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

Question

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 1

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