合 Oracle之dual系列

前言部分

导读和注意事项

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

① DUAL遭到破坏后的重建（重点）

② 关于参数replication_dependency_tracking简介

③ DUAL简介

本文简介

DUAL系列很早之前是看杨建荣的blog了解到的，最近又看他的书又看见了这块内容，于是决定自己整理一下。在此，小麦苗还是想说的是看书和自己做实验是完全不同的2回事，不多说了。

dual官方文档说明

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Refer to "About SQL Functions" for many examples of selecting a constant value from DUAL.

Note:

Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan. If you SELECT the DUMMY column from DUAL, then this optimization does not take place and logical I/O occurs.

DUAL总结

有关Oracle的DUAL表，我们需要掌握以下几点：

1. DUAL是系统提供的一个虚表，也常称为DUMMY表，临时显示结果的表，里边的内容没有特定的意义，就是为了存在而存在
2. DUAL是属于SYS SCHEMA的一个表,然后以PUBLIC SYNONYM的方式供其他数据库USER使用，这一点可以由如下SQL验证：

SYS@lhrdb> col owner format a15

SYS@lhrdb> col object_name format a15

SYS@lhrdb> SELECT D.OWNER,D.OBJECT_NAME,D.OBJECT_ID,D.OBJECT_TYPE FROM DBA_OBJECTS D WHERE D.OBJECT_NAME='DUAL';

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE

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

SYS DUAL 91680 TABLE

PUBLIC DUAL 117 SYNONYM

1. ORACLE对DUAL表的操作做了一些内部处理,尽量保证DUAL表中只返回一条记录，当然这些内部操作是不可见的。我们尝试往DUAL表中插入多条记录，但始终显示的是一条记录。

SYS@lhrdb> INSERT INTO DUAL VALUES('X');

1 row created.

SYS@lhrdb> COMMIT;

Commit complete.

SYS@lhrdb> INSERT INTO DUAL VALUES('X');

1 row created.

SYS@lhrdb> COMMIT;

Commit complete.

SYS@lhrdb> select * from dual;

D

-

X

1. DUAL表出现问题，所有相关的基础操作都会受到影响；若是DUAL表被删除的话，数据库将不能启动，报ORA-01775的错误。
2. DUAL表在数据库启动的不同阶段，里边的字段还是会有一些不同。在数据库OPEN状态下是一行一列的表，在mount和nomount状态下是一行四列的表，验证如下：

SYS@lhrdb> startup force nomount

ORACLE instance started.

Total System Global Area 1720328192 bytes

Fixed Size 2247072 bytes

Variable Size 486540896 bytes

Database Buffers 1224736768 bytes

Redo Buffers 6803456 bytes

SYS@lhrdb> select * from dual;

ADDR INDX INST_ID DU

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

0000000110299728 0 1 X

SYS@lhrdb> alter database mount;

Database altered.

SYS@lhrdb> select * from dual;

ADDR INDX INST_ID D

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

0000000110299728 0 1 X

SYS@lhrdb> alter database open;

Database altered.

SYS@lhrdb> select * from dual;

D

-

X

1. DUAL的常用方式
2. 查看当前连接用户 SELECT USER FROM DUAL；
3. 查看当前日期、时间 SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL；
4. 当作计算器用 SELECT 8*9 FROM DUAL；
5. 查看序列值 SELECT MYSEQ.NEXTVAL FROM DUAL；

DUAL表破坏后的恢复

我们首先删除SYS用户下的DUAL表：

SYS@lhrdb> SHOW USER

USER is "SYS"

SYS@lhrdb> SELECT COUNT(*) FROM DBA_OBJECTS D WHERE D.status='INVALID';

COUNT(*)

----------

0

SYS@lhrdb> DROP TABLE DUAL;

Table dropped.

SYS@lhrdb> SELECT * FROM DUAL;

SELECT * FROM DUAL

*

ERROR at line 1:

ORA-01775: looping chain of synonyms

SYS@lhrdb> SELECT COUNT(*) FROM DBA_OBJECTS D WHERE D.status='INVALID';

COUNT(*)

----------

1042

SYS@lhrdb> SET PAGESIZE 9999

SYS@lhrdb> SELECT D.OWNER, COUNT(*) FROM DBA_OBJECTS D WHERE D.status='INVALID' GROUP BY D.OWNER;

OWNER COUNT(*)

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

PUBLIC 222

XDB 7

OLAPSYS 43

APEX_030200 157

SYS 403

MDSYS 44

SYSMAN 66

EXFSYS 23

ORACLE_OCM 1

WMSYS 42

ORDSYS 11

IX 6

CTXSYS 15

DBSNMP 1

OE 1

15 rows selected.

SYS@lhrdb>

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

验证码：

请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。