A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

How to Change SQL*Plus Prompt to Reflect Username & Instance Name ??

1 min read
photo of people engaged on their phones

Photo by cottonbro studio on Pexels.com

1.   Verify that client networking connections are functional.  Clients must be able to login to sql*plus with username/password/host string (where host string is the alias created for the specific sid).

i.e.:  username:     <dbuser>
       password:     <dbpassword>
       host string:   <connectstring>

-or-   username:     <dbuser>/<password>/<connectstring>
2. Login as oracle user and add scripts at the end of the glogin.sql script . It should be located in the $ORACLE_HOME/sqlplus/admin
[oracle@localhost admin]$ vi glogin.sql

--Add below line and save this file.
set sqlprompt "_user '@' _connect_identifier:SQL > "
[oracle@localhost admin]$ cat glogin.sql

-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--   glogin.sql
--   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.
--   This script is automatically run

set sqlprompt "_user '@' _connect_identifier > "
[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release Production 

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

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production