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
- Last Updated on Tuesday, 12 March 2013 01:53
- Published on Tuesday, 12 March 2013 01:53
- Written by Pawel Wawrzyniak
- Hits: 19959
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):
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):
ALTER USER HR IDENTIFIED BY "turing$man" ACCOUNT UNLOCK;
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:
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:
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?
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 = 192.168.0.18)(PORT = 1521))
(SERVICE_NAME = TURINGSMAN)
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".
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:
Then click "Connect" and try to execute simple SQL statement (F9) provided in the editor window – it has to work:
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.