Q
Problem solve Get help with specific problems with your technologies, process and projects.

Dynamically reflecting the SID at the SQL*Plus prompt

We are trying to figure out how to change the SQL*Plus prompt to dynamically reflect the instance name (SID) every time we switch databases. On Unix, I would have set an environment variable but I am not sure how to do this in a Windows 2000 environment.

I have a script called "login.sql" that gets executed every time I logon to a database with SQL*Plus. You can create this file anywhere you want. Just make sure that its directory is in your SQLPATH environment variable. This holds true on both Unix and Windows environments. Then, in your login.sql file, put the following lines of code:

COLUMN value NEW_VALUE _SID
SELECT DECODE(INSTR(global_name,'.'),0,global_name,
             
SUBSTR(global_name,1,INSTR(global_name,'.')-1)) AS
value 
FROM global_name;
SET SQLPROMPT "&_SID SQL> "
Now, when you sign on to SQL*Plus, you will get the database SID in your prompt. You can further customize the login.sql file to set up your SQL*Plus environment before you get the prompt.

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close