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
USER is “SYS”
SQL>
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 10.1.0.3.0 – 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 10.1.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options
sys@KAM>show user
USER is “SYS”
sys@KAM>
Posted in DBA scripts | 11 Comments »