CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Ryan B.
Ryan B.

Reputation: 3665

Discover oracle connection data (Host, SID/Service, Port) from an ODBC connection

I'm not an Oracle guy, but I find that I need to touch on some oracle resources to respond to a new report request. I'm working at an office where the connection to an oracle server uses LDAP and I can't get to the connection info. I do have an ODBC connection on my machine, so I can get into the database through MS Access. But I'd like to be able to connect with SQL Developer so I can do more useful profiling on the tables.

Is there any way to use my ODBC connection through Access to tease out Hostname, Port, and SID/Service name so I can connect through SQL Developer?

It seems I do not have permissions to the UTL_INADDR functions.

Upvotes: 0

Views: 1986

Answers (2)

Hauke
Hauke

Reputation: 242

You can query SID and Hostname like this:

select instance_name, host_name from v$instance;

Afaik there is no way to query the port name from the database, see also this article on Ask TOM on this subject:

You cannot get the port -- the port is not necessarily known to the database. The listener need not be running on the same machine with the database, a single listener might be servicing many databases. A database may have many listeners servicing it.

Upvotes: 1

Sentinel
Sentinel

Reputation: 6449

Since you have an ODBC connection defined already, you can open the ODBC Data Source Administrator on your computer (Goto the start menu and type ODBC in to search for it).

In the ODBC Data Source Administrator select the data source for the database in question (it may be on the User, System or File DSN tab) and click the Configure button.

From the Configuration screen you will be able to see the TNS Service Name for the connection.

The TNS Service Name should be all you'll need to supply to SQL Developer when creating the connection. Aside from your account credentials of course.

Upvotes: 1

Related Questions