詳解Oracle數據庫中DUAL表的使用

　　DUAL表的用途

　　Dual 是 Oracle中的一個實際存在的表任何用戶均可讀取常用在沒有目標表的Select語句塊中

　　查看當前連接用戶
SQL> select user from dual;
USER

SYSTEM
查看當前日期時間
SQL> select sysdate from dual;
SYSDATE


SQL> select to_char(sysdateyyyymmdd hh:mi:ss) from dual;
TO_CHAR(SYSDATEYYYYMMDDHH

::
當作計算器用
SQL> select + from dual;
+


查看序列值
SQL> create sequence aaa increment by start with ;
SQL> select aaanextval from dual;
NEXTVAL


SQL> select aaacurrval from dual;
CURRVAL

　　關於DUAL表的測試與分析

　　DUAL就是個一行一列的表如果你往裡執行insertdeletetruncate操作就會導致很多程序出問題結果也因sql*pluspl/sql dev等工具而異

　　查看DUAL是什麼OBJECT
DUAL是屬於SYS schema的一個表然後以PUBLIC SYNONYM的方式供其他數據庫USER使用
SQL> select owner object_name object_type from dba_objects where object_name like %DUAL%;
OWNER      OBJECT_NAME       OBJECT_TYPE

SYS        DUAL              TABLE
PUBLIC     DUAL              SYNONYM
查看表結構只有一個字段DUMMY為VARCHAR()型
SQL> desc dual
Name Type        Nullable Default Comments

DUMMY VARCHAR() Y
DUAL表的結構
create table SYSDUAL
(
DUMMY VARCHAR()
)
tablespace SYSTEM
pctfree
pctused
initrans
maxtrans
storage
(
initial K
next K
minextents
maxextents
pctincrease
);
/*

　　很是困惑ORACLE為什麼要用VARCHAR()型用CHAR()難道不好麼？從這樣的表結構來看DUAL表設計的目的就是要盡可能的簡單以減少檢索的開銷

　　還有DUAL表是建立在SYSTEM表空間的第一是因為DUAL表是SYS這個用戶建的本來默認的表空間就是SYSTEM第二把這個可能經常被查詢的表和用戶表分開來存放對於系統性能的是有好處的

　　有了創建了表創建了同義詞還是不夠的DUAL在SYS這個Schema下面因此用別的用戶登錄是無法查詢這個表的因此還需要授權

　　grant select on SYSDUAL to PUBLIC with grant option;

　　將Select 權限授予公眾接下來看看DUAL表中的數據事實上DUAL表中的數據和ORACLE數據庫環境有著十分重要的關系（ORACLE不會為此癱瘓但是不少存儲過程以及一些查詢將無法被正確執行）

　　*/
查詢行數
在創建數據庫之後DUAL表中便已經被插入了一條記錄個人認為DUMMY字段的值並沒有什麼關系重要的是DUAL表中的記錄數
SQL> select count(*) from dual;
COUNT(*)



SQL> select * from dual;
DUMMY

X
插入數據再查詢記錄只返回一行記錄
SQL> insert into dual values (Y);
row created
SQL> commit;
Commit complete
SQL> insert into dual values (X);
row created
SQL> insert into dual values (Z);
row created
SQL> commit;
Commit complete
SQL> select count(*) from dual;
COUNT(*)


SQL> select * from dual;
DUMMY

X
/*
假我們插入一條數據DUAL表不是返回一行而是多行記錄那會是什麼結果呢？
SQL> insert into dual values(Y);
行 已插入
SQL> commit;
提交完成
SQL> select * from dual;
DUMMY

X
Y
SQL> select sysdate from dual;
SYSDATE

　　這個時候返回的是兩條記錄這樣同樣會引起問題在通過使用

　　select sysdate into v_sysdate from dual;

　　來獲取時間或者其他信息的存儲過程來說ORACLE會拋出TOO_MANY_ROWS(ORA)異常
因此需要保證在DUAL表內有且僅有一條記錄當然也不能把DUAL表的UPDATEINSERTDELETE權限隨意釋放出去這樣對於系統是很危險的

　　*/
把表截掉
SQL> truncate table dual;
Table truncated
SQL> select count(*) from dual;
COUNT(*)


SQL> select * from dual;
no rows selected
SQL> select sysdate from dual;
no rows selected
試著把DUAL表中的數據刪除看看會出現什麼結果
SQL> delete from dual;
行 已刪除
SQL> select * from dual;
DUMMY

SQL> select sysdate from dual;
SYSDATE

/*

　　我們便取不到系統日期了因為sysdate是個函數作用於每一個數據行現在沒有數據了自然就不可能取出系統日期這個對於很多用

　　select sysdate into v_sysdate from dual;

　　這種方式取系統時間以及其他信息的存儲過程來說是致命的因為Oracle會馬上拋出一個NO_DATA_FOUND（ORA）的異常即使異常被捕獲存儲過程也將無法正確完成要求的動作

　　*/
對於DELETE操作來說ORACLE對DUAL表的操作做了一些內部處理盡量保證DUAL表中只返回一條記錄當然這寫內部操作是不可見的
不管表內有多少記錄（沒有記錄除外）ORACLE對於每次DELETE操作都只刪除了一條數據
SQL> select count(*) from dual;
COUNT(*)


SQL> delete from dual;
行 已刪除
SQL> commit;
提交完成
SQL> select count(*) from dual;
COUNT(*)


/*
附: ORACLE關於DUAL表不同尋常特性的解釋
There is internalized code that makes this happen Code checks that ensurethat a table scan of SYSDUAL only returns one row Svrmgrl behaviour is incorrect but this is now an obsolete product
The base issue you should always remember and keep is: DUAL table should always have ROW Dual is a normal table with one dummy column of varchar()
This is basically used from several applications as a pseudo table for getting results from a select statement that use functions like sysdate or other
prebuilt or application functions If DUAL has no rows at all some applications (that use DUAL) may fail with NO_DATA_FOUND exception If DUAL has more than row then applications (that use DUAL) may fail with TOO_MANY_ROWS exception
So DUAL should ALWAYS have and only row
*/

　　DUAL表可以執行插入更新刪除操作還可以執行drop操作但是不要去執行drop表的操作否則會使系統不能用數據庫起不了會報Database startup crashes with ORA錯誤

　　如果DUAL表被不幸刪除後的恢復用sys用戶登陸創建DUAL表授予公眾SELECT權限（SQL如上述但不要給UPDATEINSERTDELETE權限）

　　向DUAL表插入一條記錄（僅此一條） insert into dual values(X);提交修改

　　用sys用戶登陸
SQL> create pfile=d:\pfilebak from spfile
SQL> shutdown immediate
在d:\pfilebak文件中最後加入一條replication_dependency_tracking = FALSE
重新啟動數據庫
SQL> startup pfile=d:\pfilebak
SQL> create table sysDUAL
( DUMMY varchar() )
pctfree pctused ;
SQL> insert into dual values(X);
SQL> commit;
SQL> Grant select on dual to Public;

　　授權成功

　　SQL> select * from dual;
D

X
SQL> shutdown immediate
數據庫已經關閉
已經卸載數據庫
ORACLE 例程已經關閉
SQL> startup
ORACLE 例程已經啟動
Total System Global Area bytes
Fixed Size                   bytes
Variable Size             bytes
Database Buffers           bytes
Redo Buffers                 bytes
數據庫裝載完畢
數據庫已經打開
SQL>
OK 下面就可以正常使用了
From:http://tw.wingwit.com/Article/program/Oracle/201311/16665.html