Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Monday, June 20, 2011

How to set Oracle SQL*Plus sqlprompt on Unix

Sometimes it is very confusing to realize in which database sqlprompt we are, writing and executing the SQL commands. If we write an SQL command in a production database instead of development or test database, the results can be catastrophic. In the below example, I will try to demonstrate my way of setting the sqlprompt in Oracle SQl*Plus. My example Unix version is IBM AIX.

Oracle SQL*Plus, always, first runs an sql script called "glogin.sql" when you try to connect to an Oracle database.
First take the backup of this script, if you need it later you can use the original version.
[oracle@srvdb01]:/oracle/orahome1/sqlplus/admin > ls -l
total 40
-rw-r-----    1 oracle   dba            1525 Aug 30 2004  glogin.sql
drwxr-x---    2 oracle   dba             256 May 23 2008  help
drwxr-x---    3 oracle   dba             256 May 23 2008  iplus
-rw-r-----    1 oracle   dba            5363 Mar 02 2008  libsqlplus.exp
-rw-r-----    1 oracle   dba             813 Mar 26 2006  plustrce.sql
-rw-r-----    1 oracle   dba            2118 Feb 16 2003  pupbld.sql
[oracle@srvdb01]:/oracle/orahome1/sqlplus/admin > cp glogin.sql glogin.sql-201106141153

Add the following line to the end of "glogin.sql" script.
set sqlprompt "&_USER'@'&_CONNECT_IDENTIFIER &_PRIVILEGE> "

Now try to login to database by using SQL*Plus in a telnet session.
[oracle@srvdb01]:/oracle/orahome1/sqlplus/admin > sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 14 12:53:42 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORCL AS SYSDBA>

As you can see, we set the Oracle SQL*Plus sqlprompt to the username@instance_name the privilege like " as sysdba".

If you apply this in your Unix Oracle database servers, when you have multiple databases in the same server or when you have multiple telnet sessions connected to different database servers and you are always switching between them, you realize in which database you are connected to and it will help a lot not to write the wrong SQL in especially in production databases instead of development or test databases.

No comments: