How to change SQL prompt to show connected user and database name in 12c +

In order to get the Database schema & Database name with include Time on the prompt need to change on the below Original file.

 

pwd

/u01/app/oracle/TEST/12.1.0/sqlplus/admin

[oracle@server1 admin]$ ls -lrt glogin.sql
-rw-r--r--. 1 oracle dba 342 Jan 12  2006 glogin.sql

[oracle@us04dbqa01 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@server1 admin]$

 

 

 

Now Connect as oracle Database OS user and add the following line at the end of the  $ORACLE_HOME/sqlplus/admin/glogin.sql

 

 

[oracle@server1 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
--
set editfile "/tmp/a447553_1.buf"
SET FEEDBACK OFF
set head off
set linesize 600
SET TERMOUT OFF
define _editor = vi

VARIABLE v_database  VARCHAR2(10)

BEGIN
  SELECT upper(INSTANCE_NAME)
  INTO   :v_database
  FROM   v$instance;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    :v_database := 'SQL';
END;
/

SPOOL uid_temp.sql

SELECT 'SET SQLPROMPT "' || '['||upper(User) || ']' ||'['|| :v_database || ']>> "' FROM   v$database;
SELECT 'SET numw 15' from DUAL;
SELECT 'SET time on ' from DUAL;
SELECT 'SET timing on trimspool on ' from DUAL;
SELECT 'SET pages 1000' from DUAL;
SELECT 'col OWNER format a16
SELECT 'col program format a30' from DUAL;
SELECT 'col machine format a10' from DUAL;
SELECT 'col partition_name format a20' from DUAL;
SELECT 'col table_name format a33' from DUAL;
SELECT 'col tablespace_name format a20' from DUAL;
SELECT 'col TBSP_NAME format a35' from DUAL;
SPOOL OFF

@uid_temp.sql
SET TERMOUT ON
--set sqlprompt " [ _DATE _USER _CONNECT_IDENTIFIER ] >> "
SET FEEDBACK ON
set head on

 

 

 

[oracle@server1 admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 9 09:22:24 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

 

09:22:24 [SYS][TEST1]>> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST   READ WRITE

1 row selected.

Elapsed: 00:00:00.03

09:22:55 [SYS][TEST1]>> show user;
USER is "SYS"
09:22:56 [SYS][TEST1]>>