Using the Right Tool – When You Need a Command Line

 I use four primary tools with Oracle databases depending on the task – Oracle Enterprise Manager (OEM), TOAD, Oracle SQL Developer, and Oracle SQL Command Line (SQLcl) or SQL*Plus.  Yes, that old command line interface is still useful on occasion and needs to be in your Toolbox.  I love SQL Developer for PL/SQL coding and actually querying the database, TOAD is tops for many DBA tasks, OEM is unbeatable for an Enterprise or multiple database tasks, and querying the OEM repository provides some very useful information too.  But there are still times when nothing gets the job done better than SQL*Plus, particularly when running DDL scripts to deploy database changes or generating DDL needed for many routine DBA tasks.  Anything you can do in SQL*Plus you should be able to do in SQLcl – except for some reporting functions that really are not used very often anyway.  I’m using SQLcl to run most of my sql scripts these days.  SQLcl is a simple download and extract install and adds functionality from the old SQL*Plus; however, SQLcl requires JDK to run where SQL*Plus does not so I’m not ready to completely walk away from SQL*Plus just yet.

Production Problems

Occasionally server problems create a situation where no database connection is available except via SQL*Plus.  If you cannot connect to the database, then it’s very hard to troubleshoot what’s going on in the database so every DBA needs some basic SQL and SQL*Plus skills.  The good news is that because you can run SQL script files from within a SQL*Plus session, you can prepare some “check” or basic troubleshooting SQL scripts and deploy them onto your database server before problems occur.  Because these situations are very infrequent, this area is one place where I have not had a chance to see if SQLcl will also run when there are server side issues with memory, semaphores, process load, etc.


When you deploy/promote database changes into test or production environments there are advantages to using command line SQL*Plus/SQLcl rather than GUI interfaces, chief among them being able to reliably test and duplicate your efforts.  Placing all the DDL statements into SQL script files means being able to run the same statements in the same order in each deployment environment.  Using a simple spool file generates a log file for each script that can be reviewed for errors and timing.  You can even prefix the SQL script filenames with the deployment step number.  This kind of organization works well in team environments too because once the setup is understood, anyone on the team can execute the scripts in any given deployment.  By using a shell script wrapper around the SQL script files you can easily generate logs and determine if any problems were encountered.  My favorite shell wrapper takes the script name and database SID as input parameters, sets the oracle environment for the database based on the SID, runs the SQL script, and scans the generated log and displays any error messages.  In large project situations where there are multiple development and test databases along with frequent database refreshes/clones, the deployment log quickly shows which scripts/updates have been applied to a particular environment.

Health Checks

As you support databases running different applications or using different Oracle features you may find that some databases need custom checks or sometimes a situation requires a health check.  For example, after getting burned a couple of times with performance problems after a database bounce because memory parameter values did not match spfile parameter values, I started running a script to compare in memory to spfile parameter values prior to database shutdown.  Some of the non-prod environments that were frequently refreshed were prone to some configuration issues (like running on a pfile instead of a spfile or having the spfile or control files on non-ASM storage) so a script that queries the database and quickly checks on all the known trouble helps prevent surprises like not having an spfile in the backup.

Tablespace and Partition Management

Tablespace and partition management are two areas where consistency needs to be maintained for both administrative ease and performance.  Scripting DDL generation to manage date range partitioning by adding tablespaces, splitting partitions, and making the appropriate quota grants greatly aids in getting a consistent result for annual maintenance efforts.

More Information

For SQLcl:

For SQL*Plus:



PFILE and SPFILE Management

The PFILE or “init.ora” file is a text based database parameter file that has been around as long as the Oracle database.

The SPFILE is a non-text server parameter file that replaces the PFILE from version 9i forward.  Beginning with version 10g there are significant advantages to using the SPFILE including automatic SPFILE backups by RMAN and saving of memory management pool sizes on database shutdown so those pools can be initiated to the same sizes as the database is restarted.

In 11gR2, the standard is to use a PFILE that contains one line stating that the SPFILE is in ASM.  If other parameters are listed in the PFILE, the PFILE settings will be ignored and Oracle will use the parameter values stored in the SPFILE.

Comparing PFILE/SPFILE parameter values to system memory.

The following query can be run to determine parameter values that have been changed in memory but not the database parameter file.  This is a good query to run anytime there is a planned shutdown of the database scheduled to avoid having problems with change parameter values in the restart.

select sp.sid,, m.isdefault as mem_default, 
       decode(sp.isspecified,'TRUE', 'FALSE','TRUE') as sp_default,
       sp.value             as sp_value,
       m.value              as mem_value,
       sp.display_value     as sp_display_value,
       m.display_value      as mem_display_value,
       sp.update_comment    as sp_update_comment,
       m.update_comment     as mem_update_comment,
       m.issys_modifiable, m.isinstance_modifiable, m.isadjusted
  from v$spparameter  sp,
       v$parameter    m
 where =
   and sp.value <> m.value
 order by, sid;

Creating the pfile/spfile from database parameter memory values.

You can create a PFILE or an SPFILE from memory values using the following commands.

create spfile = '+<ASMPATH>/<SID>/spfile<SID>.ora' from memory;
create pfile = '<ORACLE_HOME>/dbs/pfile<SID>.ora' from memory;

Moving from PFILE to SPFILE – Using the same parameters for each instance.

  • On the first node/instance, create the spfile from database parameter values in memory.
SQL> create spfile = '+<ASMPATH>/<SID>/spfile<SID>.ora' from memory;
  • Edit the pfile on the node to include only a single line specifying the spfile location in ASM.
  • Restart the database instance on the first node.
svrctl stop database -d <SID> -n <node_name>
svrctl start database -d <SID> -n <node_name>
  • On each additional node in the cluster, edit the PFILE located on that node as above and restart the instance on that node to use the single SPFILE stored in ASM for the database.

Moving from PFILE to SPFILE – Using the different parameters for each instance.

Repeat this process on each node in the cluster:

  • Create the spfile from database parameter values in memory.
SQL> create spfile = '+<ASMPATH>/<SID>/spfile<SID>_<instance>.ora' 
SQL> from memory;
  • Edit the pfile to include only a single line specifying the spfile location in ASM.
  • Restart the database instance.
svrctl stop database -d <SID> -n <node_name>
svrctl start database -d <SID> -n <node_name>

Note: Before moving your SPFILE file to ASM, the database controlfiles should be moved to ASM.  MOS note “468458.1 – How to Move Controlfiles to ASM” can be used to relocate your controlfiles from an RMAN backup to ASM.

SQL*Plus Spooling Wrapper

--** start_spool
--** starts a spool file named with the passed prefix and the database SID
--** and timestamp suffixes.
column database_name noprint new_value database
column database_time noprint new_value now
select sys.database_name, to_char(sysdate,'yymmddhh24mi') as database_time from dual;
spool &1._&database._&now.
set timing on
set echo on