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

Practice safe changes to Oracle RDBMS

Two scripts to use with sqlplus or svrmgrl that allow database administrators to be a bit more at ease.

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):

   export SHOWDB=$ORACLE_ADMIN/sql/showdb2.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:

   svrmgrl> @$SHOWDB

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


Dig Deeper on Oracle and SQL

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