Turing's Man Blog

Part 11. Enabling hr user for connections with the Oracle Database 11gR2 and configuring tnsnames.ora for TNS connection type availability on the client side

Bookmark and Share
We have created and configured the "basic" type connections for sys and system users in the part 10. This is required to unlock hr user, who is locked by default in each Oracle Database 11gR2 installation, and to set a password for him (in our case: turing$man). We will now take care about hr user and – also – we will configure TNS (ang. Transparent Network Substrate) interface for connections between client and server (with the usage of hr account, of course).

First of all, we need to start SQL Developer. We should connect (with the previously created connection) as a system user. Just like this (right-click on connection name, then select "Connect" - provide password if not stored in the connection data):


Oracle SQL Developer


Now, to unlock hr user, please execute the following SQL statement (once SQL statement is typed in the editor, just press F9 – Execute Statement – or select play button from the toolbar):



We not only unlocked hr user, but also set a password for him ("turing$man" – of course, brackets are not a part of the password). This should look like this:


Oracle SQL Developer


Okay. Our hr user is now unlocked. So, we can configure a connection for him – exactly the same way like for sys and system user before ("basic" type). Of course, the role should be "default" – this is not SYSDBA user:


Oracle SQL Developer


Well, great. However, TNS interface is still not available – please, change the "Connection Type" combo-box from "basic" to "TNS". Then, try to select any "Network Alias" from the related combo-box. There is nothing to be selected?


Oracle SQL Developer


Yes, there is nothing and it's very good. We haven't configured TNS interface yet, so we cannot expect anything there.

To configure TNS interface, please go to the following location (or similar in your case – properly modified to follow your actual configuration and installation directory):



If there is tnsnames.ora file – let's edit this one. If not – create blank tnsnames.ora file in the Notepad (or any text editor you want). Then, type the following lines and save this file in the mentioned directory:




     (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))







Oracle SQL Developer


That's all. Now, please open hr connection properties once again in SQL Developer – right-click "hr@TURINGSMAN" under the "Connections" node and select "Properties".


Oracle SQL Developer


You'll see the properties window.

We can now try to change "Connection Type" combo-box from "basic" to "TNS". This time we can select "TURINGSMAN" in "Network Alias" combo-box. Do this and then click "Test" button – if connection test succeeded then click "Save" to store our modification:


Oracle SQL Developer


Then click "Connect" and try to execute simple SQL statement (F9) provided in the editor window – it has to work:


Oracle SQL Developer


Great. We have unlocked hr user and configured TNS interface on the client side. We also changed the connection type for hr user. We can do the same thing for sys and sysdba, but we can leave their connections with the previously defined "basic" type. Both methods will work and the things are more interesting when there is a difference (at least during experiments). So, let's leave everything the way it is configured right now.

We're now ready to go with part 12, in which we will integrate MS Access 2010 via ODBC with our Oracle Database 11gR2. At this stage we have all required components.


Bookmark and Share


0 #1 Rx_ 2013-09-24 20:38
Thanks - this is all new for me. It is very difficult given what the oracle people sent to me.
So I have a
PNAV.CompayName .com =
(Description =
(Address = Protocol = TCP)(Host....port=1521)
(Service Name = Pone_TAA.CompanyName.com)

This is what they sent me - is my TNS then PNAV or is it PNAV.CompanyNam e.com

Add comment

Security code