Kamran Agayev's Oracle Blog

Oracle Certified Master

Archive for December 10th, 2008

Automatically display SID and connected User in Sql*Plus

Posted by Kamran Agayev A. on 10th December 2008

In this example, we’ll change Sql*Plus view and display our database name and conneted user name instead of word “SQL ”

If you’re using Sql*Plus , you know that every time you type a command, you’re typing it after “SQL>”

SQL>show user

If you want to display your SID, your connected USER name instead of “SQL>” you’ve to change “glogin.sql” in your $ORACLE_SID/sqlplus/admin directory as below

set termout off
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>’
set termout on

And after that adding an alias to your /home/oracle/.bashrc file:

alias sqlplus=’export ORACLE_SID=kam;sqlplus “/ as sysdba”‘
every time from Linux terminal when you type sqlplus, you’ll connect automatically to database kam as sysdba and your SQL> prompt will be changed to SYS@KAM>
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release – Production on Mon Dec 8 06:29:29 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release – Production
With the Partitioning, OLAP and Data Mining options

sys@KAM>show user

Posted in DBA scripts | 11 Comments »