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
-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]$
--
-- 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
--
-- 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;
/
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
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
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]>>
09:22:55 [SYS][TEST1]>> show user;
USER is "SYS"
09:22:56 [SYS][TEST1]>>
