Oracle DBA FAQ - Introduction to Command-Line SQL*Plus Client Tool

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7 8 9

(Continued from previous part...)

How To Connect a SQL*Plus Session to an Oracle Server?

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 "FYI_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:\oraclexe\app\oracle\product\10.2.0\server\
>.\bin\sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> CONNECT fyi/retneciyf@FYI_XE;
Connected.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
05-MAR-06

What Happens If You Use a Wrong Connect Identifier?

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 fyi/retneciyf@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.

What To Do If DBA Lost the SYSTEM Password?

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_iyf;
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.

What Types of Commands Can Be Executed in SQL*Plus?

There are 4 types of commands you can run at the SQL*Plus command line prompt:

1. SQL commands - Standard SQL statements to be executed on target database on the Oracle server. For example: "SELECT * FROM fyi_faq;" is a SQL command.

2. PL/SQL commands - PL/SQL statements to be executed by the Oracle server. For example: "EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to dba.fyicenter.com')" runs a PL/SQL command.

SQL*Plus commands - Commands to be executed by the local SQL*Plus program itself. For example: "SET NULL 'NULL'" is a SQL*Plus command.

OS commands - Commands to be executed by the local operating system. For example: "HOST dir" runs an operating system command on the local machine.

(Continued on next part...)

Part: 1 2 3 4 5 6 7 8 9

More...