How to change SQL prompt to show connected user and database name
Ans: Connect as oracle user and add the following line at the end of th glogin.sql script which is located under $ORACLE_HOME/sqlplus/admin
[oracle@rac1 ~]$ cd $ORACLE_HOME/sqlplus/admin [oracle@rac1 admin]$ pwd /u01/app/oracle/product/12.2.0.1/sqlplus/admin [oracle@rac1 admin]$ ls -ltr total 20 -rw-r--r--. 1 oracle dba 813 Mar 7 2006 plustrce.sql -rw-r--r--. 1 oracle dba 226 Apr 29 2015 libsqlplus.def -rw-r--r--. 1 oracle dba 2683 Jan 6 2016 pupbld.sql drwxr-xr-x. 2 oracle dba 4096 Jan 13 2017 help -rw-r--r--. 1 oracle dba 422 Aug 29 09:27 glogin.sql [oracle@rac1 admin]$ [oracle@rac1 admin]$ cat glogin.sql -- -- Copyright (c) 1988, 2005, Oracle. All Rights Reserved. -- -- NAME -- glogin.sql -- -- DESCRIPTION -- SQL*Plus global login "site profile" file -- -- Add any SQL*Plus commands here that are to be executed when a -- user starts SQL*Plus, or uses the SQL*Plus CONNECT command. -- -- USAGE -- This script is automatically run -- [oracle@rac1 admin]$
-- Connect as oracle user and add the following line at the end of the glogin.sql script. -- Added by Rajasekhar Amudala set sqlprompt "_user '@' _connect_identifier > "
[oracle@rac1 admin]$ cat glogin.sql -- -- Copyright (c) 1988, 2005, Oracle. All Rights Reserved. -- -- NAME -- glogin.sql -- -- DESCRIPTION -- SQL*Plus global login "site profile" file -- -- Add any SQL*Plus commands here that are to be executed when a -- user starts SQL*Plus, or uses the SQL*Plus CONNECT command. -- -- USAGE -- This script is automatically run -- -- Added by Rajasekhar Amudala set sqlprompt "_user '@' _connect_identifier > "
[oracle@rac1 admin]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 29 09:28:14 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SYS @ CDB2 > select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- CDB2 READ WRITE SYS @ CDB2 > show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO 5 PDB3 READ WRITE NO 6 PDB4 READ WRITE NO SYS @ CDB2 > SYS @ CDB2 > connect sys@PDB1 AS SYSDBA Enter password: Connected. SYS @ PDB1 > SYS @ PDB1 > show con_name CON_NAME ------------------------------ PDB1 SYS @ PDB1 >
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com