Tuesday, April 26, 2011

How to make sqlprompt bold and blinking

In a large database environment, when DBA's use multiple windows to connect to databases, it is quite possible to type command meant for standby database on a live database window and vice versa.

If all live database sqlplus windows have bold and blinking prompt, then it may reduce the chance of making mistakes like above.

This can be achieved by using external table.

Run this one time shell command on all database boxes:

echo "`tput blink; tput smso`LIVE`tput rmso;tput sgr0`" > /oracle/custom_sqlprompt.log

Add following table to all live databases:

create table custom_sqlprompt (

custom_sqlprompt varchar2(30)

)

organization external

(type oracle_loader

default directory custom_sqlprompt_dir

access parameters

(

records delimited by newline

nobadfile

nologfile

nodiscardfile

fields terminated by '\n'

missing field values are null

(CUSTOM_SQLPROMPT)

)

location ('custom_sqlprompt.log')

)

reject limit unlimited;

create public synonym custom_sqlprompt for custom_sqlprompt;

grant select on custom_sqlprompt to public;

Add following entry in glogin.sql or login.sql:

column sql_prompt new_value sqlprompt

set termout off

select ctype sql_prompt from (select case when controlfile_type = 'STANDBY' then 'STBY'

else

(select custom_sqlprompt from customl_sqlprompt) end as ctype

from v$database);

set termout on

set sqlprompt "&sqlprompt>"


Keep in mind that when you try to open spool files using vi editor, you'll see bunch of control characters, this will not be the case if you use "cat or more" command. You can temporarily disable custom sqlprompt if you don't want to see control characters in the spool file.

posted by Saibabu Devabhaktuni @ 11:21 AM

0 Comments:

Post a Comment

<< Home