oracle dual表

1、DUAL表的用途
Dual 是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中

--查看当前连接用户
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as SYS

SQL> select user from dual;
USER
------------------------------
SYSTEM

--查看当前日期、时间
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
09-3月 -1111.11.05.901000上午+08:00

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2011-03-09 11:12:03

--当作计算器用
SQL> select 1+2 from dual;
       1+2
----------
         3
--查看序列值
SQL> create sequence a increment by 1 start with 1;
Sequence created

SQL> select a.nextval from dual;
   NEXTVAL
----------
         1
SQL> select a.currval from dual;
   CURRVAL
----------
         1
SQL> drop sequence a;
Sequence dropped

2、关于DUAL表的测试与分析
DUAL就是个一行一列的表，如果你往里执行insert、delete、truncate操作，就会导致很多程序出问题。结果也因sql*plus、pl/sql dev等工具而异。
--查看DUAL是什么OBJECT

--DUAL是属于SYSschema的一个表,然后以PUBLIC SYNONYM的方式供其他数据库USER使用.
先格式化列标题和列数据的现实格式：
SQL> clear columns;
SQL> COLUMN owner HEADING FORMAT a10 WORD_WRAPPEND
SQL> COLUMN object_name HEADING FORMAT a15 WORD_WRAPPEND
SQL> COLUMN object_type HEADING FORMAT a15 WORD_WRAPPEND
SQL> select owner,object_name,object_type from dba_objects where object_namelike '%DUAL%';

OWNER      OBJECT_NAME    OBJECT_TYPE
---------- --------------- ---------------
SYS       DUAL            TABLE
PUBLIC    DUAL            SYNONYM

SQL> clear columns;

--查看表结构，只有一个字段DUMMY，为VARCHAR2(1)型
SQL> desc dual;
Name  Type        Nullable DefaultComments
----- ----------- -------- ------- --------
DUMMY VARCHAR2(1) Y

--DUAL表的结构：
create table SYS.DUAL
(
  DUMMY VARCHAR2(1)
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 16K
    next 16K
    minextents 1
    maxextents 505
    pctincrease 50
  );

/*
很是困惑，ORACLE为什么要用VARCHAR2(1)型，用CHAR(1)难道不好么？从这样的表结构来看，DUAL表设计的目的就是要尽可能的简单，以减少检索的开销。
还有，DUAL表是建立在SYSTEM表空间的，第一是因为DUAL表是SYS这个用户建的，本来默认的表空间就是SYSTEM；第二，把这个可能经常被查询的表和用户表分开来存放，对于系统性能的是有

好处的。
有了创建了表、创建了同义词还是不够的。DUAL在SYS这个Schema下面，因此用别的用户登录是无法查询这个表的，因此还需要授权：
grant select on SYS.DUAL to PUBLIC with grant option;
将Select权限授予公众。
接下来看看DUAL表中的数据，事实上，DUAL表中的数据和ORACLE数据库环境有着十分重要的关系（ORACLE不会为此瘫痪，但是不少存储过程以及一些查询将无法被正确执行）。
*/

--查询行数
--在创建数据库之后，DUAL表中便已经被插入了一条记录。个人认为：DUMMY字段的值并没有什么关系，重要的是DUAL表中的记录数
SQL> select count(*) from dual;
  COUNT(*)
----------
         1

SQL> select * from dual;
DUMMY
-----
X

--使用PL/SQL Developer插入数据，能现实所有的数据。
SQL> insert into dual values('Y');
1 row inserted

SQL> insert into dual values('X');
1 row inserted

SQL> insert into dual values('Z');
1 row inserted

SQL> commit;
Commit complete

SQL> select * from dual;
DUMMY
-----
X
Y
X
Z

但是当我们执行下面语句时只显示一行：
SQL> select sysdate from dual;
SYSDATE
-----------
2011-3-9 12

SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
09-3月 -1112.10.23.073000下午+08:00

当system用户用oracleSQL*Plus登录后，执行相同的操作，显示插入数据成功，但是查询不了添加的数据，统计也只有一条数据，查看表中的所有数据，也只能看到原表中的数据'X'.

--把表截掉
SQL> truncate table dual;
Table truncated

SQL> commit;
Commit complete

然而，数据表里还有一条数据没有被删除，这是表里至少要有一条数据的原因。
SQL> select count(*) from dual;
  COUNT(*)
----------
         1

SQL> select * from dual;
no rows selected

但是下面这个语句还能成功：
SQL> select sysdate from dual;
SYSDATE
-----------
2011-3-9 12

--试着把DUAL表中的数据删除，看看会出现什么结果：
SQL> delete from dual;
1 row deleted

SQL> select * from dual;
no rows selected

SQL> select sysdate from dual;
SYSDATE
-----------
09-3月 -11

我们能取到系统日期。在以前的版本中是取不到系统日期的，据说原因是：sysdate是个函数，作用于每一个数据行。现在没有数据了，自然就不可能取出系统日期。但是10g版本中对此做了

修改，如果你对表中的数据进行全部删除，oracle也会保留一条空数据在表中的，这样便于你用这个dual表进行其他操作。

--对于DELETE操作来说，ORACLE对DUAL表的操作做了一些内部处理,尽量保证DUAL表中只返回一条记录.当然这写内部操作是不可见的
--不管表内有多少记录（没有记录除外）,ORACLE对于每次DELETE操作都只删除了一条数据。
SQL> select count(*) from dual;
COUNT(*)
----------
4

SQL> delete from dual;
4 rows deleted

SQL> commit;
Commit complete

SQL> select count(*) from dual;
COUNT(*)
----------
1

附:ORACLE关于DUAL表不同寻常特性的解释
There is internalized code that makes this happen. Code checks that ensurethata table scan of SYS.DUAL only returns one row. Svrmgrl behaviour is incorrectbut this

is now an obsolete product.
The base issue you should always remember and keep is: DUAL table should alwayshave 1 ROW. Dual is a normal table with one dummy column of varchar2(1).
This is basically used from several applications as a pseudo table for gettingresults 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 1row then

applications (that use DUAL) may fail with TOO_MANY_ROWS exception.
So DUAL should ALWAYS have 1 and only 1 row

DUAL表可以执行插入、更新、删除操作，还可以执行drop操作。但是不要去执行drop表的操作，否则会使系统不能用，数据库起不了，会报Database startup crashes with ORA-1092错误。

3、如果DUAL表被“不幸”删除后的恢复：
用sys用户登陆。
创建DUAL表。
授予公众SELECT权限（SQL如上述，但不要给UPDATE，INSERT，DELETE权限）。
向DUAL表插入一条记录（仅此一条）： insert into dual values('X');
提交修改。
--用sys用户登陆。
SQL> create pfile=’d:/pfile.bak’ from spfile
SQL> shutdown immediate
--在d:/pfile.bak文件中最后加入一条：replication_dependency_tracking= FALSE
--重新启动数据库：
SQL> startup pfile=’d:/pfile.bak’
SQL> create table “sys”.”DUAL”
     ( “DUMMY” varchar2(1) )
     pctfree 10 pctused 4;
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 135338868 bytes
FixedSize                  453492 bytes
VariableSize            109051904 bytes
Database Buffers          25165824 bytes
Redo Buffers                667648 bytes
数据库装载完毕。
数据库已经打开。