sqlplus tips

Introduction

Most Oracle Database Administrators (DBAs) rely on the Oracle SQL*Plus (sqlplus) utility provided in $ORACLE_HOME/bin for various administration tasks on the database server.  Though Oracle provides the web-based Enterprise Manager Grid Control to perform various database administration activities and the exclusive command line interfaces for ASM (asmcmd) and Dataguard administration, many experienced DBAs still rely on sqlplus alone for administration.  In this article, I will write about 3 useful tips for sqlplus. 

spool append

Many of you may be already aware of the spool command.  The spool command helps one capture the output of various commands executed in an output file that can be used for various purposes such as record keeping, auditing and uploading query output to Oracle Support. 

Until Oracle 9i, if one uses a spool file for one session, the contents of this file are erased if the same file name is specified at a different time. There was no append option until Oracle 9i version.  The append option is available from Oracle 10g.  An example is shown below. This option is available on both Windows and Unix/Linux platforms.

$ sqlplus '/ as sysdba'

SQL > spool dba_output.txt

SQL > select count(*)

        from dba_tables;            

      1057

SQL > spool off;

SQL > exit

$ sqlplus '/ as sysdba'

SQL > spool dba_output.txt append

SQL > select count(*)

        from dba_data_files;

      7

SQL > spool off;

SQL > exit

$ cat dba_output.txt   (The file has contents saved from both the sessions above due to the append option in the latter.)

SQL > select count(*)
        from dba_tables;             
      1057

SQL > spool off;

SQL > select count(*)
        from dba_data_files;
      7

SQL >  spool off;


SQLPATH environment variable

Many of you are already aware of the PATH environment variable. Oracle provides a similar environment variable for sql scripts as well.  Oracle DBAs who heavily rely on sqlplus usually have a few local directories on the server where all the commonly run sql scripts are stored.  For example, say one has two directories viz. /home/oracle/admin_scripts and /home/oracle/monitoring_scripts respectively.

SQLPATH environment variable gives DBAs the flexibility to run any scripts from the above two directories even if  sqlplus is started from a different directory *without* giving the full path of the script.  An example is shown below.

$ export SQLPATH=/home/oracle/admin_scripts:/home/oracle/monitoring_scripts

$ ls  /home/oracle/admin_scripts
   admin1.sql admin2.sql

$ ls /home/oracle/monitoring_scripts
   monitoring1.sql monitoring2.sql

One can currently be in a different working directory (say /tmp), but still execute any of the above scripts *without* specifying the full path during execution. sqlplus automatically searches the script in directories referred by $SQLPATH

$ pwd
/tmp

$ sqlplus '/ as sysdba'
SQL > @admin1.sql

System altered.


glogin.sql  (Global Login)

Similar to the operating system login profile (such as .bash_profile etc), one can enable a global sqlplus profile by customizing $ORACLE_HOME/sqlplus/admin/glogin.sql.  For example, if one has several databases running on a server and if one wants to get the database name that sqlplus is connected to, then one can edit glogin.sql and put the "select name from v$database" command. Because glogin.sql is a global profile file,  Oracle automatically executes this file during every start of sqlplus.

Conclusion

An Oracle DBA who relies heavily on sqlplus can make his/her work life much easier by knowing about advanced capabilities of sqlplus.  For more information,  one can refer to the "SQL*Plus Quick Reference" and "SQL*Plus User's Guide and Reference" PDF documents in the Oracle documentation webpage.