Logo F2FInterview

Oracle Database Interview Questions

Q   |   QA

If you want to connect your SQL*Plus session to an Oracle server, you need to know the following information about this server:

* The network hostname, or IP address, of the Oracle server.
* The network port number where the Oracle server is listening for incoming connections.
* The name of the target database instance managed by the Oracle server.
* The name of your user account predefined on in the target database instance.
* The password of your user account predefined on in the target database instance.

A "connect identifier" is an identification string of a single set of connection information to a specific target database instance on a specific Oracle server.

Connect identifiers are defined and stored in a file called tnsnames.ora located in $ORACLE_HOME/network/admin/ directory. Here is one example of a "connect identifier" definition:

ggl_XE =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = www.globalguideline.com)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

The above "connect identifier" defines "TNS_XE" with the following connection information:

* The network hostname: www.globalguideline.com.
* The network port number: 1521.
* The name of the target database instance: XE. 

In order to connect a SQL*Plus session to an Oracle server, you need to:

1. Obtain the connection information from the Oracle server DBA.

2. Define a new "connect identifier" called "ggl_XE" in your tnsnames.org file with the given connection information.

3. Run the CONNECT command in SQL*Plus as shown in the tutorial exercise below:

>cd c:oraclexeapporacleproduct10.2.0server
>.insqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...
Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> CONNECT ggl/retneclgg@ggl_XE;
Connected.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
05-MAR-12

Of course, you will get an error, if you use a wrong connect identifier. Here is an example of how SQL*Plus react to a wrong connect identifier:

SQL> CONNECT ggl/retneclgg@WRONG;
ERROR:
ORA-12154: TNS:could not resolve the connect identifier
specified

Warning: You are no longer connected to ORACLE.

What you need to do in this case:

* Check the CONNECT command to make sure that the connect identifier is entered correctly.
* Check the tnsnames.ora file to make sure that the connect identifier is defined correctly.
* Check the tnsnames.ora file to make sure that there is no multiple definitions of the same connect identifier.
* Check your files system to see if you have multiple copies of tnsnames.ora in different Oracle home directories, because you installed multiple versions of Oracle. If you do have multiple copies, make sure your SQL*Plus session is picking up the correct copy of tnsnames.ora. 

If the DBA lost the password of the SYSTEM user account, he/she can go to the Oracle server machine, and run SQL*Plus on server locally with the operating system authentication method to gain access to the database. The tutorial exercise below shows you how:

(Terminal server to the Oracle server machine)
(Start SQL*Plus)
SQL>CONNECT / AS SYSDBA
Connected.

SQL> ALTER USER SYSTEM IDENTIFIED BY ssap_lgg;
User altered.

Notice that the (/) in the CONNECT command tells SQL*Plus to use the current user on local operating system as the connection authentication method. 

In order to link this F2FInterview's page as Reference on your website or Blog, click on below text area and pres (CTRL-C) to copy the code in clipboard or right click then copy the following lines after that paste into your website or Blog.

Get Reference Link To This Page: (copy below code by (CTRL-C) and paste into your website or Blog)
HTML Rendering of above code: