In SQL*Plus, how do I change the prompt to show the connected user and database?

oracle sqlplus
cagcowboy picture cagcowboy · Nov 20, 2008 · Viewed 21.2k times · Source

To show, for example....

USER@SID >

I thought this was potentially helpful to a few people so I'm going to answer it too!

Answer

cagcowboy picture cagcowboy · Nov 20, 2008

Amend your $ORACLE_HOME\sqlplus\admin\glogin.sql script - add:

set sqlprompt "_user '@' _connect_identifier > "

to the end of the file.

In Oracle 10g this will change correctly each time you issue a "conn". For clients before 10g it won't change when you do a "conn" but will remain as the username/db you first connected to.

You can also use _date for the current date and _privilege for the privilege (eg SYSDBA) of the connected user.

Reference:

http://www.acs.ilstu.edu/docs/Oracle/server.101/b12170/whatsnew.htm

Related questions

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

We have an application running locally where we're experiencing the following error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I've tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try …

Read More
How do I spool to a CSV formatted file using SQLPLUS?

I want to extract some queries to a CSV output format. Unfortunately, I can't use any fancy SQL client or any language to do it. I must use SQLPLUS. How do I do it?

Read More
How to display databases in Oracle 11g using SQL*Plus

With help of this command show databases; I can see databases in MySQL. How to show the available databases in Oracle?

Read More