One of the hardest parts about being an administrator is that you sometimes have too much power. When you have the keys to the database, one of the biggest problems that I have found is executing a command on the wrong database. When you're managing 20 databases, on several machines, it's real easy to lose track of the current ORACLE_SID. Whether you are dropping a table or adding space to a datafile, you want to make sure you are making the change to the correct database.
There are two scripts that I always execute anytime I use sqlplus or svrmgrl. In your ORACLE_PATH (or SQLPATH) directory, create two sql scripts, showdb.sql and showdb2.sql. Here is showdb.sql:
SELECT NAME, CREATED FROM V$DATABASE;
Here is showdb2.sql:
SELECT NAME, CREATED FROM V$DATABASE; SELECT S.SID, S.SERIAL#, S.OSUSER, S.SCHEMANAME, S.TYPE, P.SPID FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.TYPE != 'BACKGROUND' AND S.OSUSER IS NOT NULL ORDER BY S.SID, S.OSUSER, S.PROCESS ;
Add to your.profile (korn shell) (I'm assuming that you put the previous two sql scripts in $ORACLE_ADMIN/sql):
Everytime you connect to sqlplus, use the syntax:
sqlplus username @showdb
Some DBAs would say you should add this to your glogin.sql (which gets executed when you start sqlplus), but I like getting into the habit of using @showdb. Using glogin.sql will not change your prompt if you connect to a different database during a sqlplus session.
Everytime you connect to svrmgrl, execute the command:
Using these two sql scripts should help to guarantee you are administering the correct database.
About the Author
James Giordano is an Oracle database administrator. He has been working with Oracle for about seven years, and also has experience with UNIX and PeopleSoft/Oracle financials.
For More Information
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Do you have any technical questions about Oracle administration or development? Post them--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature! Our Oracle gurus are waiting to answer your toughest Oracle questions.