Useful Prompt in SQL*Plus
By Hervé Deschamps, Oracle Corporation.
Do you use SQL*Plus to connect to various instances? Do you loose track
of which SQL*Plus window is connected to which Database? You can set the
SQL*Plus prompt to remind you.
Do you like SQL*Plus to help you time the execution time of certain
queries? Do you like the idea of being able to go get a cup of tea while
the machine does the work and come back to your screen to see the result
and how much time it took? You can set the SQL*Plus prompt to help
you with that too.
If you follow the instructions below you can get something like this
when you connect to SQL*Plus:
Oracle8 Enterprise Edition Release 18.104.22.168.0 - Production
With the Partitioning and Objects options
PL/SQL Release 22.214.171.124.0 - Production
HCIT is the SID of the database where I am currently connected. Every
prompt indicates the time. So when SQL*Plus is done with one command it
will display the new time on the prompt for next command. This tells you
exactly when the previous command terminated.
How do you set SQL*Plus to do this?
set lines 100
Go to your oracle home directory. e.g.. d:\orant.
Go to the SQL*Plus Directory. e.g.. Plus33, Plus80.
Edit or create file Glogin.sql
Add the following text at the end of the file:
set pages 100
set termout off
col dbname new_value prompt_dbname
select substr(global_name,1,instr(global_name,'.')-1) dbname
set sqlprompt "&&prompt_dbname> "
set termout on
set time on
Hervé Deschamps is a Technical Manager with Oracle Corporation.
Over the years he has developed a number of applications using Oracle Development
Tools and others. He has an extensive experience of all phases of
the development life cycle. He is also known in the technical community
for his article publications centered around best custom development practices
and for his user group presentations. You can reach him by e-mail at email@example.com.
He also maintains a web site full of articles, scripts, tips and techniques