TNSnames.org File Entry for all databases?

Question

TNSnames.org File Entry for all databases?

Steve Dell

SSCarpal Tunnel

Points: 4623
More actions
April 26, 2011 at 2:53 pm

#239211

Hi !
I have installed the oracle client tool on my machine and Tnsnames.org file was configured with one oracle database connection and was able to make a successful connection with BIDS. Now i need to make multiple connections with multiple oracle databases(around 600 and they increase day by day also)so do i need to make a database entry for all databases each time to TNSnames.org file ? Is there any way to connect to multiple oracle databases directly with out TNSnames.org file update? as we do with SQL Server with out any complications.
Thanks in advance 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Not that no one in here will be able to answer but you may have better luck finding Oracle help in one of the "Working with Oracle" forums:

SQL 2005: http://www.sqlservercentral.com/Forums/Forum1042-1.aspx

SQL 2008: http://www.sqlservercentral.com/Forums/Forum1044-1.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lowell SSC Guru Points: 323518 More actions · Answer 2

can't you connect with your default linked server to oracle, and then simply switch to 4 part naming conventions, same as SQL?

That's absolutely true in my case whenever i connect with Oracle SQLDeveloper, where i can do the equivalent of cross database queries;

i know it depends on the permissions of the user you connect with, but I'm sure you can connect to say, the Scott user's data by doing MyOracleLinkedServer.UserName..TableName, so you could SELECT * FROM the view ALL_USERS( is that MyOracleLinkedServer..ALL_USERS?)for instance discovery purposes, and then work your way through each user/schema...

MyOracleLinkedServer.Scott..Tablename

MyOracleLinkedServer.Bob..Tablename

i hate to say it, but you'd probably need some superuser credentials, whether SYS, SYTEM or something you created to hit every schema i think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 3

After Creating the link server ,how can we connect to different Oracle databases from SSIS packages .?

Thanks in advance 🙂

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

RamSteve (4/27/2011)
After Creating the link server ,how can we connect to different Oracle databases from SSIS packages .?
Thanks in advance 🙂

SQL Server 2008 comes with SSIS Connection project that lets you add and remove connections as needed, I have not used it but you can start by adding 10 Oracle connections and see what you can do with that. VS2008 adds each connection based on database.

Kind regards,
Gift Peddie

liteswitch SSCommitted Points: 1824 More actions · Answer 5

Gift Peddie (4/28/2011)
RamSteve (4/27/2011)
After Creating the link server ,how can we connect to different Oracle databases from SSIS packages .?
Thanks in advance 🙂
SQL Server 2008 comes with SSIS Connection project that lets you add and remove connections as needed, I have not used it but you can start by adding 10 Oracle connections and see what you can do with that. VS2008 adds each connection based on database.

I didn't think that would work. I could be completely wrong, but I always thought no matter how your Oracle connection was defined, you had to have a relevant entry for that database in your tsnames.ora file? I didn't think you could create a connection without this?