A few more runtime variables have been introduced to help you identify the instance you are connected to and as which user you have connected. All I can say to Oracle is thanks for this one. As we all switch from one database instance to the next it is very easy to get lost--especially when you are connected to production and test instance and have multiple windows up on your workspace. I know it is a bit difficult, but if Oracle could just change the background color on my windows, I would be totally happy. For now, this is as good as it gets. Keep in mind that these variables are available for use in any manner that you would normally use variables. The following new variables are as follows:

_DATE : the current date or a pre-defined string.
_PRIVILEGE : level of connection, SYSDBA, SYSOPER, or NULL for normal.
_USER : user you are connected to.

SET SQLPROMPT Example

SQL > SET SQLPROMPT "_DATE : _USER'@'_CONNECT_IDENTIFIER _PRIVILEGE > "
20-MAY-04 : SYSTEM@prd9i  >

Also, be aware the syntax has changed a bit on these. Here is an example of how it used to be in pre-10g and current 10g for the CONNECT_IDENTIFIER.

SQLPROMPT Switching Between Instances (pre-Oracle10g)

SQL > SET SQLPROMPT '&_CONNECT_IDENTIFIER > '
prd9i > 

prd9i > connect system@prd10g
Enter password:
Connected.
prd9i > 

SQLPROMPT Switching Between Instances (new-Oracle10g)

SQL >  SET SQLPROMPT "_CONNECT_IDENTIFIER > "
prd9i > 

prd9i > connect system@prd10g
Enter password:
Connected.
prd10g >

As a side note, if you are using the glogin.sql script and have the pre-10g CONNECT_IDENTIFIER since the glogin.sql script is now executed after every successful connection as previously discussed, you do not need to worry about the proper prompt being displayed.

SQLPROMPT Switching Between Instances with pre-Oracle10g & _CONNECT_IDENTIFIER in globin.sql

SQL > connect system@prd10g
Enter password:
Connected.			
prd10g > connect system@prd9i
Enter password:
Connected.
prd9i >

SPOOL Command Enhancement

The SPOOL command has now been altered to handle the ability to append to the end of a previously spooled file. There is really only one new option for this command since the CREATE and REPLACE behave just as with no option and the behavior we are all use to. The new options to the SPOOL command are as follows:

CREATE : Create a new spool file
REPLACE : Create a new spool file
APPEND : Append to the given spool file

SPOOL Example

prd9i > spool dual.lst
prd9i > select * from dual;
D
-
X
prd9i > spool off

Display the spooled file.

prd9i > ho cat dual.lst
prd9i > select * from dual;
D
-
X
prd9i > spool off

SPOOL APPEND to the previously created spooled file

prd9i > spool dual.lst append
prd9i > select * from dual;
D
-
X
prd9i > spool off

Display the appended spooled file

prd9i > ho cat dual.lst
prd9i > select * from dual;
D
-
X
prd9i > spool off
prd9i > select * from dual;
D
-
X
prd9i > spool off

Previous   Next

Back to DBAsupport.com