|
Arup
Nanda is the author of
Oracle Privacy Security Auditing by
Rampant TechPress.
Week 7
SQL*Plus Grows Up
OTN
With Oracle Database 10g, this tiny but powerful DBA tool has
undergone some noticeable changes, including useful prompts and
advanced file manipulations.
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
Where am I or who am I? No, this is a not a question for your
psychic; it's about the whereabouts of the user in the context
of the SQL*Plus environment. The default prompt in SQL*Plus, the
plain vanilla SQL>, does indicate who the user is and what the
user is connected as. In previous releases you have to do some
elaborate coding to get the variable, but not any more. In
SQL*Plus 10.1.0.2, you use:
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!
After the internal login was desupported in Oracle9i, a lot of
DBAs around the world cried foul: how were they supposed to
enter the password of SYS on the command line and maintain
security? Well, the answer was to use quotes in the operating
system prompt:
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
Let's imagine that you are working on a problem and using some
free format ad-hoc SQL statements. Obviously, they are useful
you want to store them for future use. What do you do? You save
them in individual files such as
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 <some variable> 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 <somevariable> := 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'
In 10g
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 FAST DUAL
Notice the use of the new FAST DUAL optimization plan, as
opposed to the FULL TABLE SCAN of DUAL in Oracle9i. This
improvement reduces the consistent reads significantly,
benefiting applications that use the DUAL table frequently.
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
Other SQL*Plus enhancements have been described elsewhere in
this series. For instance, I covered RECYCLEBIN concepts in the
Week 5 installment about Flashback Table.
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.
|
