ITPub博客

首页 > 数据库 > Oracle > oracle10g new feature -- 1. Sqlplus

oracle10g new feature -- 1. Sqlplus

原创 Oracle 作者:zhyuh 时间:2004-09-14 15:48:25 0 删除 编辑

这些天一直在学习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 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.

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-778590/,如需转载,请注明出处,否则将追究法律责任。

下一篇: CMM5
请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2009568