REM Author : Paresh Mehta (Modified)
REM Purpose : Change the look of sql prompt with CON_NAME.
set echo off
set numwidth 9
set linesize 80
set pagesize 240
Set head off
set time on
set timing on
define _PVMUSRNM=""
define _PVMSID=0
define _PVMCON=""
column USERNAME new_value _PVMUSRNM;
column ses1 new_value _PVMSID;
column con1 new_value _PVMCON;
-- Retrieve session information
select username from user_users;
select trim(sid) as ses1 from v$mystat where rownum < 2;
-- Retrieve container name (available in Oracle 12c and later)
select sys_context('USERENV', 'CON_NAME') as con1 from dual;
-- Set the prompt: USER-SID-CONTAINER@IDENTIFIER
set SQLPROMPT '&_PVMUSRNM-&_PVMSID-&_PVMCON@&_CONNECT_IDENTIFIER:-> '
define _editor=vi
set head on
set echo on
set feedback 1
This will provide prompt with session details.