Seven SQL and SQL*Plus tricks which work wonders
Whether you like it or not and however strongly tool providers - Oracle included - try to sell their wares, SQL*Plus remains and is likely to remain for a while, the tool of choice for DBAs. You obviously needn't be a SQL expert to be a good DBA - although it doesn't hurt but, expert or not, when you want to write usable SQL*Plus scripts there are a number of tricks worth knowing..
Here are seven of them which ,if you don't already know them, will probably save you a lot of hassle.
If in order to be a good DBA all you had to do was to type SELECT * from the ad hoc DBA_something view, check a few things and then execute a single DROP, ALTER or whatever to fix them, life would be easy. Unfortunately, most of the time you need to run a sequence of statements, which moreover changes over time. Therefore, one of the favorite tricks of experienced DBAs is to write SQL scripts which generate other SQL scripts which do the job.
Let's take an example to illustrate most of the SQL*Plus tricks you need.
Say that you need a script to execute an on-line backup of your database (we are assuming your database is running in the required ARCHIVELOG mode).
Basically, what you need to do for each database is to execute
ALTER TABLESPACE xxx BEGIN BACKUP;
then backup the file(s) associated with the tablespace, then execute
ALTER TABLESPACE xxx END BACKUP;
The brute force method would be to write a first SQL script in which the BEGIN BACKUP is hard-coded for each tablespace, some kind of operating-system script which deals with the file backup and a second SQL script for the END BACKUP part.
Very, very bad method indeed, chiefly because things evolve. It's highly likely that you will add files to your database and quite possibly you will have new tablespaces too, which means that you will have to maintain all your scripts. Are you ready to bet you (or, for that matter, the guy who looks after your database while you are on vacation) won't forget? And your initial backup script may run smoothly every day without telling you you're omitting some files. One of the irritating things with backup scripts is that it's only when you're in really dire straits and trying to recover your production database, that you realize that they have been out of date and therefore totally useless for months. This is typically a case where you MUST use a SQL script to generate what is needed; for the Oracle data dictionary contains all the information you need, and is ALWAYS up-to-date.
To generate the 'ALTER TABLESPACE' statements, no better place to look than DBA_TABLESPACES. You can easily type :
select 'alter tablespace ' || tablespace_name || chr(10) ||
'begin backup;'
from dba_tablespaces
where status <> 'INVALID';
(INVALID refers to tablespaces which were dropped).
Trick #1 : chr(10) is quite a useful thing to concatenate in strings, as it inserts a 'newline' character.
It's not really required here but it's very useful with lines which would otherwise be much too long and generally speaking improves the legibility of the scripts you generate.
To get the up-to-date list of files which constitute your database, you can refer to DBA_DATAFILES. Let's assume that our underlying system is Unix and that we want to copy files to the /backup directory, you can write something such as :
select 'cp ' || f.file_name || ' /backup'
from dba_datafiles f;
Trick #2 : WATCH THE LINE LENGTH!
SQL*Plus has the bad habit of 'wrapping' long lines (slicing them, in effect), which can generate invalid operating system commands. Don't forget that a full file name can be up to 255 (give or take one) characters in length and so can the name of the backup directory. If you really want to be safe, you will have to insert at the beginning of your script :
set linesize 600
This is the first but certainly not the last SQL*Plus command we meet.
We are beginning to see the generation of the required commands taking shape but we're certainly not done yet. For one thing, backup procedures are among the most sensitive procedures in operations. You must handle errors, either SQL errors or operating system errors - what about some change of protections preventing you from copying where you want, or a full file system ?
Trick #3 : Use WHENEVER OSERROR EXIT
and WHENEVER SQLERROR EXIT SQL.SQLCODE and end your SQL procedures with EXIT 0.
This will enable you to test return codes from SQL*Plus in an operating system script and will allow you to handle errors properly.
Now let's improve our script. We could of course generate and run BEGIN BACKUP on all the tablespaces, generate and run the copy of all the files, and then generate and run END BACKUP on all the tablespaces. This is fairly crude and (let's leave out the details) not very good in terms of database management. What should be done is for each tablespace
step 1 : execute BEGIN BACKUP
step 2 : copy the files associated with the tablespace
step 3 : execute END BACKUP
The difficulty is in ordering correctly a set of unrelated commands. In that case we need two more tricks :
Trick #4 : Use UNIONs to fetch commands generated by independent queries
and
Trick #5 : Use dummy, unprinted columns for ordering.
Here's how it works :
We want to order by tablespace, so we return the tablespace name but we use the COLUMN SQL*Plus comment to make it invisible in the output. We also want the steps to be returned in a given order, so we add a constant column, also invisible, which we shall use as a minor sorting key (note that in a UNION you need to give aliases to the columns in the first select list only):
column tablespace_name noprint
column step noprint
column text format A550
select tablespace_name,
'step_1' step,
'alter tablespace ' || tablespace_name || chr(10) ||
'begin backup;' text
from dba_tablespaces
where status <> 'INVALID'
union
select tablespace_name,
'step_2',
'cp ' || file_name || ' /backup'
from dba_datafiles
union
select tablespace_name,
'step_3',
'alter tablespace ' || tablespace_name || chr(10) ||
'end backup;'
from dba_tablespaces
where status <> 'INVALID'
order by 1, 2;
This will generate what we want... except that we can't spool it directly because of headers, feedback information on the number of rows returned, etc...
Trick #6 : Use the three required SQL*Plus commands needed to remove unwanted output!
Those three commands are :
set pagesize 0 -- Removes breaks on pages AND headings
set feedback off -- Removes the message about how many lines
-- were selected
set recsep off -- Removes unwanted blank lines.
With these settings, you can write your script in the following way :
spool do_backup.sql
spool off
set feedback on
@do_backup
to generate and immediately run a backup procedure which is always guaranteed to be up-to-date.
Well, we have what we want, so what about the seventh promised trick?
Don't forget that when you run something under SQL*Plus you may run several 'profile' files : glogin.sql which you Unix DBAs will find under $ORACLE_HOME/sqlplus/admin and possibly a login.sql file in the current directory. You don't know what is or ever will be in these scripts. So :
Trick #7 : code defensively and unset any potentially troublesome setting, even if the default is just right.
For instance :
set pause off
set echo off
set verify off -- If you are using parameters
set scan off
You now know most of the techniques you need to write truly useful scripts; then it's a matter of imagination, knowledge of the data dictionary and, of course, SQL skills. Beware that hot-backup as it is introduced here is still a prototype for tutorial purposes. If you are interested by the subject, a complete version named hotbackup.sql is freely available on this site, www.oriolecorp.com. The adventurous can also have a look at other SQL scripts, some of them are in the 'North Face' category...
The mythical rollback segment All you ever wanted to know about exp/imp
Best regards,
Rafi.
