Changing SQLPlus Prompt 1

[Michael42]

Hello,

In Oracle 9i on Solaris 8 how can I change the SQLPLus prompt to show both the current ORACLE_SID and hostname of the UNIX system?

Thanks,

Michael42

 

[SantaMufasa]

Michael,

Here is my rather informative SQL*Plus prompt:

SQL> @p
12:03:28 ServerName/SIDName:DHUNT SQL>

The prompt shows the current time, the server name, the database instance name, and the currently logged-in Oracle username.

Here is a set of code (which I run from a script named "p.sql") that produces that prompt:

SET echo off
set serveroutput on
set feedback off
set linesize 200
set trimspool on
spool TempPrompt.sql
Declare
   r               v$instance%rowtype;
Begin
   dbms_output.enable(1000000);
   select * into r from v$instance;
   dbms_output.put_line('set time on');
   dbms_output.put_line('set sqlprompt "'||r.host_name||'/'||r.instance_name||
           ':'||user||' SQL> "');
end;
/
spool off
@TempPrompt

...but before you cast "p.sql" in bronze, I'll post another set of code to do the same thing, but it is a bit tighter (and doesn't rely on a block of PL/SQL code).

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

Michael,

Here is slightly tighter, non-PL/SQL code (for the "p.sql" script) that produces the same results as above:

col my_prompt new_value my_prompt
SET echo off
set feedback off
set pagesize 0
set linesize 200
set time on
set termout off
select '"'||host_name||'/'||instance_name||
           ':'||user||' SQL> "' my_prompt
  from v$instance;
set termout on
set sqlprompt &my_prompt
set pagesize 35

Here is proof of concept for the code revision:

SQL> @p
12:18:45 ServerName/SIDName:DHUNT SQL>

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Michael42]

SantaMufasa,

Very nicely done.

Thanks,

Michael42

 

[dbtoo2001]

one word of caution, if you are in the habit of opening one sqlplus session, then using 'connect user@' to get to your instances, then the display may show you the wrong instance/server unless you re-execute the prompt sql. To rectify that, create yourself a connect.sql. Then use '@connect user@' instead of 'connect user@'....

contents of my connect.sql:

connect &1
col db_name new_value dbname
col db_machine new_value _dbmachine
set time on
set trimspool on
select distinct(machine) db_machine from v$session where type='BACKGROUND'
and program like '%PMON%';
select name db_name from v$database;
set sqlprompt "&dbname@&_dbmachine:SQL> "


output would look like (your results will vary):

> sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jan 10 08:08:34 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> @connect system@dba9i.gddsi.com
Enter password:
Connected.

DB_MACHINE
----------------------------------------------------------------
oriongd05


DB_NAME
---------
DBA9I

08:08:50 DBA9I@oriongd05:SQL> @connect system@uiddev.gddsi.com
Enter password:
Connected.

DB_MACHINE
----------------------------------------------------------------
oriongd05


DB_NAME
---------
UIDDEV

08:09:01 UIDDEV@oriongd05:SQL>
08:09:28 UIDDEV@oriongd05:SQL> @connect system@orioncop.geg.mot.com
Enter password:
Connected.

DB_MACHINE
----------------------------------------------------------------
orionssg02


DB_NAME
---------
ORIONCOP

08:09:43 ORIONCOP@orionssg02:SQL>

Notice the server name changed!

 

[SantaMufasa]

dbtoo's suggestion is very well taken...and I use such a "connect method"...I run "p.sql" as part of my canned login routines so that I always obtain the correct prompt.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.