oracle中DUAL表详解
1、DUAL表的用途
Dual是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中
--查看当前连接用户
sql>selectuserfromdual;
USER
------------------------------
SYSTEM
--查看当前日期、时间
sql>selectsysdatefromdual;
SYSDATE
-----------
2007-1-241
sql>selectto_char(sysdate,''yyyy-mm-ddhh24:mi:ss'')fromdual;
TO_CHAR(SYSDATE,''YYYY-MM-DDHH2
------------------------------
2007-01-2415:02:47
--当作计算器用
sql>select1+2fromdual;
1+2
----------
3
--查看序列值
sql>createsequenceaaaincrementby1startwith1;
sql>selectaaa.nextvalfromdual;
NEXTVAL
----------
1
sql>selectaaa.currvalfromdual;
CURRVAL
----------
1
2、关于DUAL表的测试与分析
DUAL就是个一行一列的表，如果你往里执行insert、delete、truncate操作，就会导致很多程序出问题。结果也因sql*plus、pl/sqldev等工具而异。
--查看DUAL是什么OBJECT
--DUAL是属于SYSschema的一个表,然后以PUBLICSYNONYM的方式供其他数据库USER使用.
sql>selectowner,object_name,object_typefromdba_objectswhereobject_namelike''%DUAL%'';
OWNEROBJECT_NAMEOBJECT_TYPE
---------------------------------------------
SYSDUALTABLE
PUBLICDUALSYNONYM
--查看表结构，只有一个字段DUMMY，为VARCHAR2(1)型
sql>descdual
NameTypeNullableDefaultComments
---------------------------------------
DUMMYVARCHAR2(1)Y
--DUAL表的结构：
createtableSYS.DUAL
(
DUMMYVARCHAR2(1)
)
tablespaceSYSTEM
pctfree10
pctused40
initrans1
maxtrans255
storage
(
initial16K
next16K
minextents1
maxextents505
pctincrease50
);
/*
很是困惑，ORACLE为什么要用VARCHAR(1)型，用CHAR(1)难道不好么？从这样的表结构来看，DUAL表设计的目的就是要尽可能的简单，以减少检索的开销。
还有，DUAL表是建立在SYSTEM表空间的，第一是因为DUAL表是SYS这个用户建的，本来默认的表空间就是SYSTEM；第二，把这个可能经常被查询的表和用户表分开来存放，对于系统性能的是有好处的。
有了创建了表、创建了同义词还是不够的。DUAL在SYS这个Schema下面，因此用别的用户登录是无法查询这个表的，因此还需要授权：
grantselectonSYS.DUALtoPUBLICwithgrantoption;
将Select权限授予公众。
接下来看看DUAL表中的数据，事实上，DUAL表中的数据和ORACLE数据库环境有着十分重要的关系（ORACLE不会为此瘫痪，但是不少存储过程以及一些查询将无法被正确执行）。
*/
--查询行数
--在创建数据库之后，DUAL表中便已经被插入了一条记录。个人认为：DUMMY字段的值并没有什么关系，重要的是DUAL表中的记录数
sql>selectcount(*)fromdual;
COUNT(*)
----------
1
sql>select*fromdual;
DUMMY
-----
X
--插入数据，再查询记录，只返回一行记录
sql>insertintodualvalues(''Y'');
1rowcreated.
sql>commit;
Commitcomplete.
sql>insertintodualvalues(''X'');
1rowcreated.
sql>insertintodualvalues(''Z'');
1rowcreated.
sql>commit;
Commitcomplete.
sql>selectcount(*)fromdual;
COUNT(*)
----------
4
sql>select*fromdual;
DUMMY
-----
X
/*
--假我们插入一条数据，DUAL表不是返回一行，而是多行记录，那会是什么结果呢？
sql>insertintodualvalues(''Y'');
1行已插入
sql>commit;
提交完成
sql>select*fromdual;
DUMMY
-----
X
Y
sql>selectsysdatefromdual;
SYSDATE
-----------
2004-12-15
2004-12-15
这个时候返回的是两条记录，这样同样会引起问题。在通过使用
selectsysdateintov_sysdatefromdual;
来获取时间或者其他信息的存储过程来说，ORACLE会抛出TOO_MANY_ROWS(ORA-01422)异常。
因此，需要保证在DUAL表内有且仅有一条记录。当然，也不能把DUAL表的UPDATE，INSERT，DELETE权限随意释放出去，这样对于系统是很危险的
*/
--把表截掉
sql>truncatetabledual;
Tabletruncated.
sql>selectcount(*)fromdual;
COUNT(*)
----------
0
sql>select*fromdual;
norowsselected
sql>selectsysdatefromdual;
norowsselected
--试着把DUAL表中的数据删除，看看会出现什么结果：
sql>deletefromdual;
1行已删除
sql>select*fromdual;
DUMMY
-----
sql>selectsysdatefromdual;
SYSDATE
-----------
/*
我们便取不到系统日期了。因为，sysdate是个函数，作用于每一个数据行。现在没有数据了，自然就不可能取出系统日期。
这个对于很多用
selectsysdateintov_sysdatefromdual;
这种方式取系统时间以及其他信息的存储过程来说是致命的，因为，ORACLE会马上抛出一个NO_DATA_FOUND（ORA-01403）的异常，即使异常被捕获，存储过程也将无法正确完成要求的动作。
*/
--对于DELETE操作来说，ORACLE对DUAL表的操作做了一些内部处理,尽量保证DUAL表中只返回一条记录.当然这写内部操作是不可见的
--不管表内有多少记录（没有记录除外）,ORACLE对于每次DELETE操作都只删除了一条数据。
sql>selectcount(*)fromdual;
COUNT(*)
----------
2
sql>deletefromdual;
1行已删除
sql>commit;
提交完成
sql>selectcount(*)fromdual;
COUNT(*)
----------
1
/*
附:ORACLE关于DUAL表不同寻常特性的解释
Thereisinternalizedcodethatmakesthishappen.CodechecksthatensurethatatablescanofSYS.DUALonlyreturnsonerow.SvrmgrlbehavIoUrisincorrectbutthisisnowanobsoleteproduct.
Thebaseissueyoushouldalwaysrememberandkeepis:DUALtableshouldalwayshave1ROW.Dualisanormaltablewithonedummycolumnofvarchar2(1).
Thisisbasicallyusedfromseveralapplicationsasapseudotableforgettingresultsfromaselectstatementthatusefunctionslikesysdateorother
prebuiltorapplicationfunctions.IfDUALhasnorowsatallsomeapplications(thatuseDUAL)mayfailwithNO_DATA_FOUNDexception.IfDUALhasmorethan1rowthenapplications(thatuseDUAL)mayfailwithTOO_MANY_ROWSexception.
SoDUALshouldALWAYShave1andonly1row
*/
DUAL表可以执行插入、更新、删除操作，还可以执行drop操作。但是不要去执行drop表的操作，否则会使系统不能用，数据库起不了，会报DatabasestartupcrasheswithORA-1092错误。
3、如果DUAL表被“不幸”删除后的恢复：
用sys用户登陆。
创建DUAL表。
授予公众SELECT权限（sql如上述，但不要给UPDATE，INSERT，DELETE权限）。
向DUAL表插入一条记录（仅此一条）：insertintodualvalues(''X'');
提交修改。
具体操作：
--用sys用户登陆。
sql>createpfile=’d:/pfile.bak’fromspfile
sql>shutdownimmediate
--在d:/pfile.bak文件中最后加入一条：replication_dependency_tracking=FALSE
--重新启动数据库：
sql>startuppfile=’d:/pfile.bak’
sql>createtable“sys”.”DUAL”
(“DUMMY”varchar2(1))
pctfree10pctused4;
sql>insertintodualvalues(‘X’);
sql>commit;
sql>GrantselectondualtoPublic;
授权成功。
sql>select*fromdual;
D
-
X
sql>shutdownimmediate
数据库已经关闭。
已经卸载数据库。
ORACLE例程已经关闭。
sql>startup
ORACLE例程已经启动。
TotalSystemGlobalArea135338868bytes
FixedSize453492bytes
VariableSize109051904bytes
DatabaseBuffers25165824bytes
RedoBuffers667648bytes
数据库装载完毕。
数据库已经打开。
sql>
--OK，下面就可以正常使用了。
