How to change SQL prompt? Customization on sql prompt!!!
Connect to database by exporting environmental variables
. oraenv
>>> DEVDB
sqlplus / as sysdba
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:35:48 2024
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL>
As oracle owner add the following line at the glogin.sql script which is located under $ORACLE_HOME/sqlplus/admin
Display the connected instance name in sql prompt?
set sqlprompt "_connect_identifier > "
[oracle@oraclelab1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:37:04 2024
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
DEVDB >
Display the connected username and instance name in sql prompt?
set sqlprompt "_user '@' _connect_identifier > "
[oracle@oraclelab1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:37:44 2024
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SYS @ DEVDB >
Logs:
=====
[root@oraclelab1 ~]# ps -ef|grep smon
oracle 29528 1 0 12:31 ? 00:00:00 ora_smon_DEVDB
root 30073 29314 0 12:35 pts/0 00:00:00 grep --color=auto smon
[root@oraclelab1 ~]# su - oracle
Last login: Thu Dec 5 12:31:01 IST 2024 on pts/0
[oracle@oraclelab1 ~]$ . oraenv
ORACLE_SID = [DEVDB] ? DEVDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 ~]$ env |grep ORA
ORACLE_SID=DEVDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:35:48 2024
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@oraclelab1 ~]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@oraclelab1 admin]$ ll glogin.sql
-rw-r--r--. 1 oracle oinstall 342 Jan 13 2006 glogin.sql
[oracle@oraclelab1 admin]$ vi glogin.sql
[oracle@oraclelab1 admin]$
[oracle@oraclelab1 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 sqlprompt "_connect_identifier > "
[oracle@oraclelab1 admin]$
[oracle@oraclelab1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:37:04 2024
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
DEVDB > exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@oraclelab1 admin]$ vi glogin.sql
[oracle@oraclelab1 admin]$
[oracle@oraclelab1 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 sqlprompt "_connect_identifier > "
set sqlprompt "_user '@' _connect_identifier > "
[oracle@oraclelab1 admin]$
[oracle@oraclelab1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:37:44 2024
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SYS @ DEVDB > exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@oraclelab1 admin]$
No comments:
Post a Comment