本文介绍了Oracle Database 10g中SQL*Plus工具的重要改进，包括增强的提示信息、文件操作改进及安全性提升等。这些变化提高了DBA的工作效率并简化了日常任务。
10g: TOP 20 Features for dbas
From OTN.
lession 7: sqlplus in 10g
SQL*Plus Grows Up
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 headaches 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.
lession 7: sqlplus in 10g
SQL*Plus Grows Up
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 headaches 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.
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
