Use tnsnames.ora in Oracle SQL Developer

sql

tnsnames.ora

oracle-sql-developer

environment-variables

byNikita Barsukov·Oct 30, 2024

Integrate tnsnames.ora into Oracle SQL Developer by setting the Oracle Client path under Preferences. Do this by going to Tools > Preferences > Database > Advanced and input the path of tnsnames.ora. With the correct path, SQL Developer can locate TNS names for database connections:

tnsnames.ora should be in $ORACLE_HOME/network/admin or $TNS_ADMIN.
In SQL Developer, go to Tools > Preferences > Database > Advanced.
Add the path in "Use Oracle Client".
Choose the TNS name when creating a new database connection.

-- How to: New connection with TNS
1. Click on the New connection ('+' icon).
2. Provide ‘Connection Name’, ‘Username’, ‘Password’.
3. For Connection Type, choose TNS.
4. From 'Network Alias', pick your TNS entry.
5. Test and Save.

-- If everything goes as planned, you're a click away from the data wonderland!

These changes are immediate - No need to restart SQL Developer.

Setting environment variables

The TNS_ADMIN environment variable is pivotal for SQL Developer to locate your tnsnames.ora. This path should be set before launching SQL Developer:

For the Windows folks

Go to Advanced System Settings > Environment Variables.
Add a new System Variable named TNS_ADMIN. Set its value to the tnsnames.ora directory.

For Linux and Unix-like system users

In Linux, add this line to your ~/.profile or equivalent profile file:

export TNS_ADMIN=/path/to/your/tnsnames/folder

With the above steps, SQL Developer, and any other Oracle connectivity-reliant tools, will know the TNS names location. Remember to restart SQL Developer or log out and back in for any environment variable changes to take effect.

Troubleshooting connection issues

If issues arise, check the following:

File accessibility: Ensure your tnsnames.ora file is readable - your software needs to be able to access it!
Syntax check: Look into your tnsnames.ora for syntax errors. Remember, a misplaced comma can create chaos!
Perform a tnsping: Let's confirm your network can resolve your TNS entry.

If all else fails, don't fret. The Oracle community on community.oracle.com and various forums have got your back.

Config for Instant client

If you're using Oracle Instant Client, then ensure the directory path is included in the -Djava.library.path parameter within the sqldeveloper.conf file:

AddVMOption -Djava.library.path=/path/to/instant/client

This ensures the Instant Client and SQL Developer sync well for TNS name resolution.

Best practices

Backups and pitfalls

While it's good to have backups of tnsnames.ora, ensure they don't confuse SQL Developer. Archive old or backup versions to a separate directory.

Naming matters

Ensure the tnsnames.ora filename is standard. Any diversion can lead to SQL Developer not recognizing the file.

Network changes

If your network changes, make sure to re-validate your TNS entries.

Custom aliases and centralized network configurations in tnsnames.ora can optimize interactivity with databases across teams for streamlined workflows.

explain-codes / Sql / Use tnsnames.ora in Oracle SQL Developer

Linked

Sqlplus statement from the command line



How can I confirm a database is Oracle & what version it is using SQL?



How to connect to Oracle using Service Name instead of SID



How to run .sql file in Oracle SQL developer tool to import database?



How to get Database Name from Connection String using SqlConnectionStringBuilder

