TNS Names in SQL Developer

Published 12 March 2010

Starting in SQL Developer 2.1, you can just point to the tnsnames.ora file by navigating to Tools > Preferences > Database > Advanced and specifying the file location in the Tnsnames Directory text box.

If you use other tools (like sqlplus) that refer to a tns admin variable to find the connection information, the procedure below could still be helpful.

For versions lower than 2.1 or if you haven't specified a Tnsnames Directory on 2.1 or higher, SQL Developer will look in the following locations in order for a tnsnames.ora file

1. $HOME/.tnsnames.ora
2. $TNS_ADMIN/tnsnames.ora
3. TNS_NAMES lookup key in the registry
4. /etc/tnsnames.ora ( non-windows )
5. $ORACLE_HOME/network/admin/tnsnames.ora
6. LocalMachineSOFTWAREORACLEORACLE_HOME_KEY
7. LocalMachineSOFTWAREORACLEORACLE_HOME

If your tnsnames.ora file is not getting recognized, use the following procedure:

1. Define an environmental variable called TNS_ADMIN to point to the folder that contains your tnsnames.ora file.
   

   In Windows, this is done by navigating to Control Panel > System > Advanced Tab > Environment Variables

   In Linux, define the TNS_ADMIN variable in the .profile file in your home directory.

2. Confirm the os is recognizing this environmental variable and restart SQL Developer. From the windows command line:
   echo %TNS_ADMIN%

   From linux: echo $TNS_ADMIN

3. Now in SQL Developer right click on Connections and select new connection. Select connection type TNS in the drop down box. Your entries from tnsnames.ora should now display here.

Oracle User Administration

Published 04 January 2009

Users

To create a new user:

create user <username> 
identified by <password>;

This is the most basic statement for creating a user. There are also options for designating tablespaces, etc. [1]

Read full entry