oracle10g new feature -- 1. Sqlplus

這些天一直在學習oracle10g的一些新特性，順便作了一些筆記。把它們都陸續放到這上面來吧。

雖然網上oracle10g新特性的資料很多，自己看過，測試過，自己做的筆記畢竟不一樣，回頭來看快。

按自己的順序，第一章，SQLPLUS

[@more@]

1.     Prompts for the Unmindful

set sqlprompt "_user _privilege> "

The SQL*Plus prompt shows up as

SYS AS SYSDBA>

SQL> set sqlprompt "_user _privilege 'on' _date >"

SYS AS SYSDBA on 06-JAN-04 >

 

SQL> set sqlprompt "_user 'on' _date 'at' _connect_identifier >"

ANANDA on 06-JAN-04 at SMILEY >

 

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 >

2.     Quote the Obvious? Why, No!

Oracle 9i:  $sqlplus "/ as sysdba"

Oracle 10g:  $ sqlplus / as sysdba

 

3.     Improved File Manipulations

To save scripts

In previous versions:

select something1 ....

save c:testf1.sql

select something else ....

save c:testf2.sql

select yet another thing ....

save c:testf3.sql

 

In Oracle 10g:

select something1 ....

save myscripts

select something else ....

save myscripts append

select yet another thing ....

save myscripts append

 

In spool, if u don’t want to silently overwrite the existed output Result.lst file,

APPEND:

SQL>spool result.lst append

check the existence of the file before writing:

SQL> spool result.lst create

Use another name or "SPOOL filename[.ext] REPLACE"

 

Just OVERWRITE

SQL> spool result.lst

Or

SQL>spool result.lst replace

4.     Login.sql is for Logins, Isn't It?

In oracle9i and below, login.sql file was not executed at connect time, but only at the SQL*Plus startup time. So,

SQL>set sqlprompt "_connect_identifier >"

DB1>

DB1> connect scott/tiger@db2

Connected

DB1> --“Wrong prompt”

 

In oracle10g, The file login.sql is not only executed at SQL*Plus startup time, but at connect time as well:

SCOTT at DB1> connect scott/tiger@db2

SCOTT at DB2> connect john/meow@db3

JOHN at DB3> “Correct prompt”

 

5.     Shift back to sqlplus 9i

$Sqlplus –c 9.2

 

6.     Use DUAL Freely

Suppose SQL:

SQL>select USER into from DUAL;

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.