这些天一直在学习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
SQL> set sqlprompt "_user 'on' _date 'at' _connect_identifier >"
ANANDA on
ANANDA on
Session altered.
ANANDA on
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
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.
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
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.
Suppose SQL:
SQL>select USER into
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.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/207/viewspace-778590/ ，如需转载，请注明出处，否则将追究法律责任。
