SQL*Plus Grows Up for 10g DBA

Which tool is most used by DBAs on a daily basis? For many DBAs like myself who predate the GUI revolution, it has to be the SQL*Plus command line option.

Although SQL*Plus might have changed in Oracle Database 10g with the introduction of powerful and feature-rich Enterprise Manager 10g, this ubiquitous little tool has been and will continue to be part of the Oracle legacy—for novice and experienced DBAs alike.

In this installment we will explore some of the very useful enhancements made to SQL*Plus 10.1.0.2. Remember, you'll need the sqlplus executable of Oracle Database 10g software, not Oracle9i Database sqlplus running against a 10g database, to follow along.

Prompts for the Unmindful

set sqlprompt "_user _privilege> "

The SQL*Plus prompt shows up as

SYS AS SYSDBA>

provided, of course, that the user SYS is logged in as SYSDBA. Note the use of the two special predefined variables— _user and _privilege—which define the current user and the privilege it used to login.

Let's throw something else into the mix: we now want to display today's date as well. All we have to do is the following to make the prompt show the desired information.

SQL> set sqlprompt "_user _privilege 'on' _date >"
SYS AS SYSDBA on 06-JAN-04 >

How about adding the database connection identifier as well? That approach is definitely helpful in situations where you may be wondering "where" you are (in production or development).

SQL> set sqlprompt "_user 'on' _date 'at' _connect_identifier >"
ANANDA on 06-JAN-04 at SMILEY >

So far so good; but we may want to display the current date in more detailed manner-with hours and minutes—to be even more useful.

ANANDA on 06-JAN-04 at SMILEY > alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';

Session altered.

ANANDA on 01/06/2004 13:03:51 at SMILEY >

There you go: the very informative SQL prompt in a few key strokes. Save it in the glogin.sql file and you have these settings forever.

Quote the Obvious? Why, No!

sqlplus "/ as sysdba"

The usage of quotes was deplored but accepted with some grumbling. In Oracle Database 10g, that requirement is gone. Now you can login as SYSDBA with

sqlplus / as sysdba

at the OS command prompt, without the quotation marks. This enhancement not only means you have two fewer characters to type, but provides some additional benefits such as not requiring escape characters in OSs such as Unix.

Improved File Manipulations

select something1 ....
save 1
select something else ....
save 2
select yet another thing ....
save 3

and so on. After a while you have to collect all the saves files for future use. How cumbersome! SQL*Plus 10.1.0.2 allows you to save statements as appended to the files. In the previous example, you could use:

select something1 ....
save myscripts
select something else ....
save myscripts append
select yet another thing ....
save myscripts append

and so on. All the statements will be appended to the file myscripts.sql, eliminating the need to store in separate files and then concatenating them to a single one.

This approach applies to spooling as well. In prior releases, the command SPOOL RESULT.LST would have created the file result.lst, if not already present; but would have silently overwritten if it did exist. More often than not, especially under trying circumstances, this behavior may lead to undesired side effects such as an important output file being overwritten. In 10g, the spool command can append to an existing one:

spool result.lst append

What if you want to overwrite it? Simply omit the append clause or use REPLACE instead, which is the default. The following will check the existence of the file before writing.

spool result.lst create
Use another name or "SPOOL filename[.ext] REPLACE"

This approach will prevent the overwriting of the file result.lst.
Login.sql is for Logins, Isn't It?

Remember the files login.sql and glogin.sql? Essentially, the file login.sql in the current directory is executed whenever SQL*Plus is invoked. However, there was a serious limitation. In Oracle9i and below, say you have the following line in the file.

set sqlprompt "_connect_identifier >"

When you first start SQL*Plus to connect to a database DB1, the prompt shows:

DB1>

Now, if you connect to a different database DB2 from the prompt:

DB1> connect scott/tiger@db2
Connected
DB1>

Note the prompt. It's still DB1, although you are connected to DB2 now. Clearly, the prompt is incorrect. The reason is simple: login.sql file was not executed at connect time, but only at the SQL*Plus startup time. The subsequent connection did not re-execute the file, leaving the prompt unchanged.

In Oracle Database 10g, this limitation is removed. The file login.sql is not only executed at SQL*Plus startup time, but at connect time as well. So in 10g, if you are currently connected to database DB1 and subsequently change connection, the prompt changes.
SCOTT at DB1> connect scott/tiger@db2
SCOTT at DB2> connect john/meow@db3
JOHN at DB3>

Change is Bad!

What if you don't want to use these enhanced SQL*Plus for some reason? Simple, just call it with the -c option:

sqlplus -c 9.2

The SQL*Plus environment will behave like the old 9.2 one.

Use DUAL Freely

How many developers (and DBAs, too) do you think use this command often?

select USER into from DUAL

Far too many, probably. Each call to the DUAL creates logical I/Os, which the database can do without. In some cases the call to DUAL is inevitable as in the line := USER. Because Oracle code treats DUAL as a special table, some ideas for tuning tables may not apply or be relevant.

Oracle Database 10g makes all that worry simply disappear: Because DUAL is a special table, the consistent gets are considerably reduced and the optimization plan is different as seen from the event 10046 trace.

In Oracle9i

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   TABLE ACCESS (FULL) OF 'DUAL'
	  

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   FAST DUAL

Note: Technically these DUAL improvements are implemented in the SQL Optimizer, but of course for many users SQL*Plus is the primary tool for manipulating SQL.

Other Useful Tidbits

Contrary to some widespread rumors, the COPY command is still available, although it will be obsolete in a future release. (Hmm...didn't we hear that in Oracle9i?) If you have scripts written with this command, don't lose heart; it's not only available but supported as well. Actually, it has been enhanced a bit on the error message-reporting front. If the table has a LONG column, COPY is the only way you can create a copy of the table; the usual Create Table As Select will not be able to process tables with columns of long datatype.