Enable SQLPROMPT to show current user/db in SQL Plus

To answer to your question of:

“How do you know what user you are logged on as and on what database?”

In SQL Plus if you type show user this will tell you what user you are logged on as

And if you type:

SELECT NAME FROM V$DATABASE;

This will return the database your currently logged onto, however this will depend on if you have permission to the v$ views which you wouldn’t have.

Alternatively you can:

SELECT PROPERTY_VALUE

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME=’GLOBAL_DB_NAME’;

However this is not reliable as the GLOBAL NAME can be different to DB_NAME.

I would best advise you add the following line to the end of your glogin.sql file located in usually C:\oracle\product\10.2.0\client_1\sqlplus\admin (depending on where your oracle client is installed):

set sqlprompt "_user’@’_connect_identifier SQL>"

This will tell you on your SQL Prompt, what user you are logged in as and on what database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: