ORACLE中dual的详解及其故障恢复

什么是dual呢？

首先，dual是属于sys用户的；

它只有一个X varchar2(1)的列；

查询这个虚拟的列不会产生逻辑的IO；

dual是oracle中的一个实际存在的表，任何用户都可读取，常用在没有目标表的select语句块中

以下是oracle manual中对dual的说明：

The table named DUAL is a small table in the data dictionary that Oracle and user-written programs can reference to guarantee a known result. This table has one column called DUMMY and one row containing the value X.

这个名称为dual的表是位了保证oracle和用户程序能够引用一个已知的值。这个表只有名称为DUMMy的伪列，而且只有一行其值为X。

下面我们通过一些例子来说明dual的结构和功能

测试环境：

rhel5.4 32bit

oracle 10.2.0.1.0

DUAL表的用途

1、查看当前连接的用户

SQL> select user from dual;

2、查看当前的日期

SQL> select sysdate from dual;

SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

3、当做简单的计算器使用

SQL> select 1+2 from dual;

4、查看序列的值

SQL> create sequence test_seq increment by 1 start with 1;

SQL> select test_seq.nextval from dual;

SQL> select test_seq.currval from dual;

关于dual表的深入测试和分析

dual表就是一个一行一列的表，如果向这个表中执行insert、delete、truncate操作，就会导致许多程序出现问题。结果也会因为sqlplus、pl/sql等工具的不同而不同

1、dual的结构

SQL> select owner,object_name,object_type from dba_objects where object_name like '%DUAL%';

OWNER      OBJECT_NAM OBJECT_TYP

---------- ---------- ----------

SYS        DUAL       TABLE

PUBLIC     DUAL       SYNONYM

在查看一下DUAL表的结构

SQL> set long 1000

SQL> select dbms_metadata.get_ddl('TABLE','DUAL','SYS') from dual;

CREATE TABLE "SYS"."DUAL"

(

"DUMMY" VARCHAR2(1)

)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(

INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT

)

TABLESPACE "SYSTEM"

SQL> select dbms_metadata.get_ddl('SYNONYM','DUAL','PUBLIC') FROM DUAL;

CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR "SYS"."DUAL"

很是困惑，ORACLE为什么要用VARCHAR(1)型，用CHAR(1)难道不好么？从这样的表结构来看，DUAL表设计的目的就是要尽可能的简单，以减少检索的开销。

dual表是建立在system表空间的，一个是因为dual表时sys这个用户建立的，默认的表空间也就是system；另外一个原因是经常被查询的表和用户表分开来存放，对系统的性能不会造成过多的影响

有了创建表、同义词这些还是不够的，dual是在sys这个方案下面的，因此别的用户是无法查看这个表的，还是需要授权的：

SQL> grant select on sys.dual to public with grant option;

事实上，dual表中的数据和oracle的数据库环境有着十分重要的关系（oracle不会为此而瘫痪，但是有不少的存储过程以及一些查询将会无法完成

2、dual的内容

SQL> select count(*) from dual;

D

-

X

插入一条数据，再查询，只返回一行记录：

SQL> select count(*) from dual;

  COUNT(*)

----------

         1

SQL> insert into dual values('Y');

SQL> insert into dual values('Z');

SQL> insert into dual values('M');

SQL> commit;

SQL> select count(*) from dual;

  COUNT(*)

----------

         1

SQL> select * from dual;

D

-

X

网上的一些资料表示这个count(*)在进行insert的时候会发生一些变化：在insert之后，再count(*)的时候会显示累加的数据，在执行一次select *之后，这个值会再次的变为1。

不过在我的测试中，（不论windows平台，还是在linux平台）。这个count(*)和select *的数据数目，无论如果insert都不会发生变化。不晓得是不是和数据库的版本有关系？

在这里我们假设说，网上的例子中的情况时可以有的，即dual表中可以有多条记录。这样通常会引起问题，如果使用类似于select sysdaba into v_sysdate from dual;的pl/sql语句来获取时间或者其他信息，oracle就会抛出TOO_MANY_ROWS(ORA-01422)异常

3、truncate dual表

SQL> truncate table dual;

SQL> select count(*) from dual;

  COUNT(*)

----------

         1

SQL> select * from dual;

no rows selected

SQL> select sysdate from dual;

SYSDATE

---------

23-MAY-12

在这里我们可以看到当我们截断表之后，还是可以根据dual来查询到sysdate，这个，，，和网络上的相关资料也不大一样！

4、drop table

dual表可以执行插入、更新、删除操作，还可以执行drop操作，但是在执行drop之后会导致系统不能用，数据库起不起来，会报Database startup crashes with ORA-01092错误

如果dual不幸被删除了，，，

SQL> drop table dual;

这个时候如果执行对dual的select操作，会警告说同义词dual找不到目标对象，因为我们只是删除了dual表，还有一个dual的同义词呢

SQL> shutdown immediate;

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              83887696 bytes

Database Buffers          197132288 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> startup mount

SQL> create pfile from spfiel;

SQL> shutdown immediate;

在刚才新建的pfile中新增加参数：replication_dependency_tracking=FALSE，将spfile重命名下，然后startup

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              83887696 bytes

Database Buffers          197132288 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

启动成功！然后我们需要重建dual表

SQL>CREATE TABLE "SYS"."DUAL"

(

"DUMMY" VARCHAR2(1)

)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(

INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT

)

TABLESPACE "SYSTEM"

SQL> insert into dual values('X');

SQL> commit;

SQL> shutdown immediate;

将spfile换回原来的名字

SQL> startup

SQL> grant select on dual to public;

SQL> select * from dual;

D

-

X

SQL> select sysdate form dual;

SYSDATE

---------

23-MAY-12

SQL> conn scott/tiger

SQL> select sysdate from dual;

SYSDATE

转载地址：http://blog.sina.com.cn/s/blog_67be3b4501015mkz.html