Automatically display SID and connected User in Sql*Plus
Posted by Kamran Agayev A. on December 10th, 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>
December 11th, 2008 at 11:03 am
kamran blog cok guzel olmus. hayirli olsun. ben istanbuldan oracle universityden bilginc IT Academyden Zekeriya.
gorusmek uzere
December 11th, 2008 at 11:17 am
Merhaba Zekeriye abi. Nasilsiniz?
Onu da bildirmek lazim ki, Bilginc Academy-deki Oracle University-de katildiqim ve hepsi sizin tarafinizdan verilen 3 eyitim bana buyuk etkisi oldu, bu eyitimlerinize gore size cok tesekkur ediyorum ve sizin eyitimlerinize gelecekde yine katilmaqi dusunuyorum inshallah
Siteni ziyaret edib hayir-dua verdiyiniz icin cok tesekkur ediyorum. Saqolun
December 12th, 2008 at 6:13 am
10g ve sonrasinda kullanilabilir;
SQL> set sqlprompt “_user _privilege> ”
HR > set sqlprompt “_user ‘on’ _date ‘at’ _connect_identifier >”
HR on 21/02/2006 at XE >alter session set nls_date_format = ‘mm/dd/yyyy hh24:mi:ss’;
Session altered.
HR on 02/21/2006 13:02:44 at XE >
Ayrica Login.sql artik her yeni baglanti ile tekrar calisitirldigindan prompt farkli bir db ‘ye gecince tekrar duzeltiliyor, cok kullanisli oluyor cidden, yanlis db’de bir komut calistirmamak adina.
December 12th, 2008 at 6:22 am
Cok ilginc Tonguc bey
O zaman bu kayitlari da login.sql faylinin-a insert etmek olar
Evet, yanlis db-de komut calisdirmaqin karsisini aliyor
Hatta ishlemlerimizi spool ederken, hangi ishlemin hangi dakika ve saniyede calisdiqini da kontrol ede biliriz
May 7th, 2009 at 12:37 pm
Salam Kamran.
Maraqli bloqdur, tebrik edirem.
Bu arada kodu sadenin sadesi kimi bele de yazmaq olar. Eletde burda nezere alinmayib db name de domain adi gosterilmesi, sen parse edirsen, amma eger kimse local db varsa ve domain istifade etmirse, parse etmeden asagidakini da istifade ede biler:)
select lower(user)||’@’||(select global_name from global_name)||’>’ “user@db_name” from global_name
May 7th, 2009 at 12:51 pm
Salam Ulfet
Bloguma xos gelmisen
Duzdur, kodu ele de yazmaq olar. Elbette ilk aqla gelen, bir basa global_name-i tam olaraq goturmekdir. Ancaq eger domain adi varsa, ve gorsenmemesi teleb olunursa, onu men gosterdiyim kimi kesmek olar
Vaxt tapib meqaleme fikir bildirdiyin ucun chox saqol, minnettaram
May 19th, 2009 at 2:40 pm
Hi Kamran,
I feel this is very good blog.
i gone through above script,
can you please tell me how to write script,
to disply the username@dbname>,
the above script always displaying sys@dbname,but whenever we connect to other user..it should show like ‘newuser@dbname>’ like this.
thank u…
May 19th, 2009 at 2:50 pm
continuation to above
i mean
it is showing like this
sys@DEMO1>conn scott
Enter password:
Connected.
sys@DEMO1>sho user
USER is “SCOTT”
but is it possible to sho like SCOTT@DEMO1>
May 19th, 2009 at 2:51 pm
Hi Vamshi. Welcome to my Blog
Whenever you connect with another user, it automatically displays the new connected user’s name as its shown below:
[oracle@localhost admin]$ sqlplus /nolog
SQL*Plus: Release 10.1.0.5.0 – Production on Tue May 19 14:43:02 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
.idle>.conn / as sysdba
Connected.
.sys@MAS>.conn kamran
Enter password:
Connected.
.kamran@MAS>.
May 31st, 2011 at 10:10 pm
Hi Kamran , thank you , your blog is very good.
April 18th, 2012 at 11:53 am
just set the following line in glogin to get user@oracle sid
set sqlprompt “_user’@’_connect_identifier>”
the output will be
SYS@ORCL>