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.

Deployments

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:

 

Advertisements

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