Sqlplus is the command line interface client for the Oracle database. It is used by DBAs to administer a database, by developers to develop server side software components, and others. It is one of the oldest Oracle tools and one of the most famous. Once connected to a database via Sqplus, you can then execute Sqlplus specific and SQL statements, e.g. Queries, DML, DDL, DCL and TCL. Even with the advent of GUI alternatives such as SQLDeveloper, TOAD, etc Sqlplus still is the preferred method to connect to an Oracle database by many professionals. Sqlplus is the Oracle equivalent of the db2 CLI interface for IBM DB2, the mysql CLI interface for MySQL and others.
The Sqlplus utility is installed with the Oracle client software or Oracle server software. It is installed in $ORACLE_HOME/bin, where ORACLE_HOME is the location where the Oracle software is installed, i.e. /u01/app/oracle/product/12.1.0.1/dbhome_1.
I wrote this blog entry to demonstrate some useful features I have found throughout my experience as a DBA. This is not intended to be a comprehensive reference. Please refer to the Oracle documentation for that.
Note, the DB_NAME you are using in these examples is an alias which is resolved to a database service name which is listed in the file $TNS_ADMIN/tnsnames.ora. The $TNS_ADMIN variable is $ORACLE_HOME/network/admin.
To connect to an Oracle database instance and be prompted for your password:
$ sqlplus USER@DB_NAME Enter password: PASSWORD
To connect to an Oracle database instance and be prompted for both your username and password:
#$ sqlplus Enter user-name: USER@DB_NAME Enter password: PASSWORD
The less desirable way to connect to an Oracle instance is to use the batch way. This is the way to connect to Oracle in an unattended batch way, like via scripts. This is undesirable because it can display the password in clear text via ps –ef command output on UNIX or Linux systems:
$ sqlplus USER/PASSWORD@DB_NAME
If you were to invoke Sqlplus via the command above on a UNIX system and then were to open another window to the same UNIX system and execute the ps –ef command you would see the following:
ps –ef while loop: … oracle 7864626 8716500 0 10:32:43 pts/2 0:00 sqlplus USER/PASSWORD@securedb oracle 7864626 8716500 1 10:32:43 pts/2 0:00 sqlplus oracle 7864626 8716500 1 10:32:43 pts/2 0:00 sqlplus oracle 7864626 8716500 1 10:32:43 pts/2 0:00 sqlplus …
What is going on here is when you first connect to Sqlplus in UNIX and use the syntax above, for a short time the database user’s password would be visible via ps –ef output. Note how when I first execute Sqlplus I see the password via ps –ef; however when I run ps –ef again, the password goes away and just shows sqlplus. This apparently is the fix that has been implemented in recent releases of Oracle. Even though it appears that Oracle ultimately removes the password from the ps –ef output, it doesn’t appear to do it right away. There appears to be a short period of time after the invocation of Sqlplus where it is still visible. Keep in mind that in earlier Oracle releases the password was visible via ps –ef for the entire duration of the sqlplus session. The remedy is to embed the password in an UNIX environmental variable or to connect to sqlplus with the /nolog option and the subsequently connect to the database instance via the CONNECT statement.
To launch Sqlplus without being prompted for a username or password:
$ sqlplus /nolog SQL> connect USER@DB_NAME
Note, with the method above you can also jump from one database to another without exiting Sqlplus and then launching it again.
If you wanted to set the database name before you connect.
On Windows:
$ set LOCAL=DB_NAME $ sqlplus USERNAME Enter password: PASSWORD
Note on UNIX or Linux systems the variable would be TWO_TASK.
There is a file you can create locally in the same directory where you launch Sqlplus and it is called sqlnet.ora. In this file you can set many parameters to control the behavior of TNS, the network protocol for interacting with an Oracle database. TNS stands for Transparent Network Substrate and it runs on top of the industry standard TCP/IP protocol. If you want to keep this file in a separate location then you can define the variable TNS_ADMIN and put that file in that location.
There is another file you can create locally in the same directory where you launch Sqlplus and it is called login.sql. Any SQL or Sqlplus statement you put in this file will automatically be executed upon launching Sqlplus, whether you connect to a database or not. If you want to keep this file in a separate location then you can define the variable SQLPATH and put that file in that location.
To disconnect from an Oracle database but not exit Sqlplus:
SQL> disconnect;
To suppress banner information such as version, Oracle copyright, SQL prompt, etc use the –S option of Sqlplus:
This is useful when automating Oracle tasks like via the Oracle crontab, etc.
Without the –S option:
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 23 10:08:36 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL>
With the –S option:
$ sqlplus -S USER@DB_NAME Enter password: show user; USER is "USER" quit
To automate the interaction of Sqlplus in a UNIX or Linux environment:
$ sqlplus USER/PASSWORD@DB_NAME <<EOF > show user; > select name from v$database; > quit > EOF show user; select name from v$database; quit … SQL> USER is "USER" NAME --------- DB_NAME
There are levels of Sqlplus that will disable certain operating system interactive activities.
Level # 1 disables edit and host commands
$ sqlplus -R USER@DB_NAME SQL> XXX SP2-0738: Restricted command XXX not available
Level # 2 disables edit, host, save, spool and store:
$ sqlplus -R 2 USER@DB_NAME SQL > XXX SP2-0738: Restricted command XXX not available
Level # 3 Will disable EDIT, GET, HOST, SAVE, SPOOL, START,@, @@ and STORE
$ sqlplus -R 3 USER@DB_NAME SQL> XXX SP2-0738: Restricted command XXX not available
To execute SQL statements in Sqlplus from an OS file:
This is useful for when you want to store commonly used SQL queries, etc.
$ ls -l command.sql -rw-r--r-- 1 bleisj UsersGrp 29 Oct 23 10:53 command.sql $ cat command.sql select name from v$database;
Sqlplus:
SQL > @command; NAME --------- DB_NAME
Or:
SQL > start command; NAME --------- DB_NAME
If you had a wrapper SQL script which subsequently called other scripts in the same directory, you sometimes would discover that Sqlplus will not keep the path of the calling script. Consider the following example:
$ pwd /tmp/ora
In the /tmp/ora directory on a UNIX server we have four SQL scripts. Script.sql is the wrapper script calling the other scripts:
$ ls -l total 32 -rw-r--r-- 1 oracle dba 42 Oct 23 11:35 script.sql -rw-r--r-- 1 oracle dba 70 Oct 23 11:34 script1.sql -rw-r--r-- 1 oracle dba 70 Oct 23 11:34 script2.sql -rw-r--r-- 1 oracle dba 70 Oct 23 11:34 script3.sql $ cat script.sql set feedback off heading off; select ‘running script wrapper’ from dual; @script1.sql; @script2.sql; @script3.sql; $ cat script1.sql set feedback off heading off; select 'running script1.sql' from dual; $ cat script2.sql set feedback off heading off; select 'running script2.sql' from dual; $ cat script3.sql set feedback off heading off; select 'running script3.sql' from dual; …
When you launch Sqlplus from the same directory where the scripts reside and run the wrapper, the wrapper script runs the other scripts just fine:
SQL> @script.sql; running script wrapper running script1.sql running script2.sql running script3.sql …
Now move to a directory where the scripts do not reside and call the wrapper script by its full path. You will see the wrapper script fails to call the other scripts:
$ cd /tmp $ pwd /tmp … SQL> @/tmp/ora/script.sql; running script wrapper SP2-0310: unable to open file "script1.sql" SP2-0310: unable to open file "script2.sql" SP2-0310: unable to open file "script3.sql"
Now edit the script.sql file and add a @ – double before each script:
… @@script1.sql; @@script2.sql; @@script3.sql; … $ pwd /tmp … SQL> @/tmp/ora/script.sql; running script wrapper running script1.sql running script2.sql running script3.sql
That time we were in a directory that did not contain the scripts and we were able to run all of them without specifying their fully qualified pathnames from the wrapper. That is what the @@ does. It instructs Sqlplus to call this script from the same directory where the wrapper script was called from. If you don’t do that then after script.sql executes, it will simply drop back to the directory where the Sqlplus utility was invoked from and attempt to run the subsequent scripts from that directory.
