Square DBA – Oracle DBA Talk

Customize your SQL*Plus Environment

The setup for SQL*Plus is amazingly easy. In fact, it should already be done. Every client software installation has it, and every server installation has it too. On windows, there are two versions of SQL*Plus: a GUI one (the sqlplusw.exe program) and a character based one (the sqlplus.exe program). The character-mode SQL*Plus is 100% compatible with SQL*Plus on every other platform on which Oracle is delivered. The GUI SQL*Plus, which offers no real functional benefit over the character mode – after all, it is a character-mode tool running in a window – is different enough to be confusing and isn’t flexible as the command-line version. Additionally, it is already officially deprecated in the next release of Oracle, so it won’t be around for long.

Store Settings for SQL*PLUS (login.sql and glogin.sql)

glogin.sql

Whenever SQL*PLUS starts up, it looks for a file named glogin.sql under the directory $ORACLE_HOME/sqlplus/admin. If such a file is found, it is read and the containing statements executed. This allows to store settings (such as linesize) accross SQL*PLUS sessions. New in Oracle 10g: Oracle also reads glogin.sql and login.sql at a connect in sql*plus.

login.sql

Additionally, after reading glogin.sql, sql*plus also looks for a file named login.sql in the directory from where SQL*PLUS was started and in the directory that the environment variable SQLPATH points to and reads it and executes it. Settings from the login.sql take precedence over settings from glogin.sql.

In addition to the standard items in my glogin.sql file I added the following:

-- Define a custom prompt

define gname=idle
column global_name new_value gname
select lower(user) || '@' ||
       substr( global_name, 1, decode( dot,
                                       0, length(global_name),
                                          dot-1) ) global_name
  from (select global_name, instr(global_name,'.') dot
          from global_name );
set sqlprompt '&gname> ' 

This give me a SQL*Plus prompt that show me the database name I am connected to as well as what user I am connected as. This helps eliminate the confusion of dealing with multiple users on multiple instances. The other piece of code I have added is:

-- Start logging all scripts

define logname=date
column clogname new_value logname
select substr(global_name,1,
 decode(dot,0,length(global_name),dot-1))||'-'||
 to_char(sysdate, 'yyyymmddhh24miss')||'-'||
round(dbms_random.value(10,99),0) clogname
from (select global_name, instr(global_name, '.')
dot from global_name);
spool c:\sqlpluslogs\&logname..txt

This starts a log file for my entire session. The spool filename is generated randomly. The log file name is generated with the date, database name, and for uniqueness a random number. The random number is not really needed since I am using the date down to the seconds, but I added it just to be safe.

Dynamically set the name of a spool file in SQL*Plus

Do you need to dynamically create a spool filename in SQL*Plus? You may have a need to set the name of a spool file based on the current time or possibly the database you are connected to. It’s often needed when scripts is run as scheduled job and there is a need to generate files with different names. Much of the time I do this in a shell script, but I do run across an occasion to do it SQL*Plus. Here is a solution for using date:

define logname=date
column clogname new_value logname
select 'prefix_'||to_char(sysdate, 'yyyymmdd') clogname from dual;
spool '&logname'

What about including the database name? Here is one I used today the uses the current date, database name, and a random number.

define logname=date
column clogname new_value logname
select substr(global_name,1,
 decode(dot,0,length(global_name),dot-1))||'-'||
 to_char(sysdate, 'yyyymmddhh24miss')||'-'||
round(dbms_random.value(10,99),0) clogname
from (select global_name, instr(global_name, '.')
dot from global_name);
spool c:\sqlpluslogs\&logname..txt 

Oracle Alert Log via SQL with External Tables

Because I love to do things in the database I write little script and functions to keep me in SQLplus. Access the Alert Log is no different. In reality I access the Alert Log inside and outside the database. It’s too important to monitor in one place.

Starting in Oracle9i you can map external flat files to Oracle tables.
Mapping the Oracle alert log is easy and once defined, all you have to do is query it with standard SQL syntax:

create directory BDUMP as '/u01/app/oracle/admin/mysid/bdump';

create table
   alert_log ( msg varchar2(80) )
organization external (
   type oracle_loader
   default directory BDUMP
   access parameters (
      records delimited by newline
   )
   location('alrt_mysid.log')
)
reject limit 1000;

Now we can easily extract important Oracle alert log information without leaving SQL*Plus:

select msg from alert_log where msg like '%ORA-00600%';