SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SQL Server to collect information from your Oracle server


Using SQL Server to collect information from your Oracle server

Author
Message
Rudy Panigas
Rudy Panigas
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4332 Visits: 1325
daveriya (6/6/2011)
if you installed oracle client on sql server ,then what is the use of if.u need to create linked server to oracle client and then oracle client make connection to oracle server


If you do not install the Oracle client on your SQL server then the link connection will not work or not work properly.

When you create a "linked" connection in SQL server; the server itself is connecting to Oracle (or other SQL servers) and not your desktop.

To understand how linked server connection works I recommend Book On Line (BOL). In this article I am assuming that you know how linked servers work. It's not hard just takes some time to read through.

The only thing that the Oracle client is going to provide you is the correct drivers in order for SQL server to connect to Oracle.

I hope his answers your question.

Thanks,

Rudy



Damian Widera-396333
Damian Widera-396333
Say Hey Kid
Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)

Group: General Forum Members
Points: 676 Visits: 321
IMO this should be the service name taken from tnsnames.ora

Kindest Regards,

Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET
aleksey2001
aleksey2001
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 138
JunkMail Victim (6/6/2011)
Great article. It's so much easier to gather Oracle information from SQL Server than Oracle itself.


Oh yeah, it's ease and PAINFULLY SLOW.
I used this configuration many times in different environments and was happy, but in our current environment we have a need to move ~ 1 million rows from SQL Server to Oracle and this job takes several hours to complete. To unload this data to plain text it takes 20 seconds. To load this data from plain text to Oracle it takes several minutes, so simple INSERT ... SELECT ... is a killer.
Good luck with that configuration!
And what's wrong with Oracle-to-Oracle? DBLINK works just fine.
JunkMail Victim
JunkMail Victim
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 33
Hello aleksey2001.

I do internal audit work. My ability to work with information through SQL Server is much greater than in Oracle. Collecting Oracle environmental data through SQL Server is a blessing. So far, I get the results every bit as fast through this method as I do when checking the SQL Server environment alone. Even if it was slow, I could wait.

Everything has its purpose and benefit. This occurs irrespective of your personal wants and desires.

I'm still very glad for the information.
aleksey2001
aleksey2001
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 138
JunkMail Victim (8/10/2011)
Hello aleksey2001.

I do internal audit work. My ability to work with information through SQL Server is much greater than in Oracle. Collecting Oracle environmental data through SQL Server is a blessing. So far, I get the results every bit as fast through this method as I do when checking the SQL Server environment alone. Even if it was slow, I could wait.

Everything has its purpose and benefit. This occurs irrespective of your personal wants and desires.

I'm still very glad for the information.


Hi JunkMail Victim,

Unfortunately not all users are as patient as you are. They are not willing to wait for several hours to transfer data. I'm not trying to say that it wouldn't work for anybody. It's just I really frustrated with slowness of that configuration at this moment because I need to deal with flat files transfer via SSIS job which need to be run from .Net application. It sucks big time.

BTW I love audit options of ORACLE and kind of confused with SQL Server . Check out the fine grained auditing in Oracle. It's really neat.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search