一些互联网用户删除dual表还有一个问题：

删除dual时间表hang直播，然后直接shutdown abort。话又说回来，当您启动数据库。发现open时间已经hang直播。但该数据库是真正开放的另一个窗口已经打开。

下面的模拟：

SYS@orcl11g>drop table dual;

一直hang在这不动

在另外一个窗体：

SYS@orcl11g>shutdown abort

ORACLE instance shut down.

重新启动数据库：

SYS@orcl11g>startup mount

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size                  2213936 bytes

Variable Size             327157712 bytes

Database Buffers           83886080 bytes

Redo Buffers                4288512 bytes

Database mounted.

SYS@orcl11g>alter database open;

一直hang这个不动。打开另外一个窗体：

SYS@orcl11g>select open_mode from v$database;

OPEN_MODE

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

READ WRITE

SYS@orcl11g>

发现数据库已经打开

这是由于系统触发器造成，在初始化參数中增加：

_system_trig_enabled=flase

再次启动数据库：

SYS@orcl11g>startup mount

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size                  2213936 bytes

Variable Size             327157712 bytes

Database Buffers           83886080 bytes

Redo Buffers                4288512 bytes

Database mounted.

SYS@orcl11g>show parameter _sys

NAME                                 TYPE                   VALUE

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

_system_trig_enabled                 boolean                FALSE

audit_sys_operations                 boolean                FALSE

audit_syslog_level                   string

filesystemio_options                 string                 none

ldap_directory_sysauth               string                 no

SYS@orcl11g>alter database open;

Database altered.

最開始删除dual表的时候也是由于系统触发器的存在。

如今我们禁用了系统触发器将dual删除看是什么效果：

SYS@orcl11g>drop table dual;

Table dropped.

重新启动数据库：

SYS@orcl11g>startup nomount pfile=initorcl11g.ora

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size                  2213936 bytes

Variable Size             327157712 bytes

Database Buffers           83886080 bytes

Redo Buffers                4288512 bytes

SYS@orcl11g>show parameter rep

NAME                                 TYPE                   VALUE

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

replication_dependency_tracking      boolean                TRUE

SYS@orcl11g>

SYS@orcl11g>

SYS@orcl11g>

SYS@orcl11g>

SYS@orcl11g>alter database mount;

Database altered.

SYS@orcl11g>alter database open;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01775: looping chain of synonyms

Process ID: 2485

Session ID: 1 Serial number: 3

这里我们不用trace就知道是由于dual表的缘故，所以dual也是非常重要的。不要乱玩

这时候我们须要将REPLICATION_DEPENDENCY_TRACKING參数设置为False才干打开数据库：

官方文档给出的解释：

REPLICATION_DEPENDENCY_TRACKING enables or disables dependency tracking for

read/write operations to the database. Dependency tracking is essential for

propagating changes in a replicated environment in parallel

增加參数打开数据库：

SYS@orcl11g>startup mount

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size                  2213936 bytes

Variable Size             327157712 bytes

Database Buffers           83886080 bytes

Redo Buffers                4288512 bytes

Database mounted.

SYS@orcl11g>show parameter rep

NAME                                 TYPE                   VALUE

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

replication_dependency_tracking      boolean                FALSE

SYS@orcl11g>ak^H

SP2-0042: unknown command "a" - rest of line ignored.

SYS@orcl11g>a;ter^H

SYS@orcl11g>alter database open;

Database altered.

SYS@orcl11g>select * from dual;

select * from dual

              *

ERROR at line 1:

ORA-01775: looping chain of synonyms

这时候我们呢须要重建dual表：

SYS@orcl11g>CREATE TABLE "SYS"."DUAL"

  2       (  "DUMMY" VARCHAR2(1)

  3       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  4     NOCOMPRESS LOGGING

  5      STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  6      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  7      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  8      TABLESPACE "SYSTEM" ;

Table created.

SYS@orcl11g>SYS@orcl11g>GRANT SELECT ON "SYS"."DUAL" TO PUBLIC WITH GRANT OPTION;

Grant succeeded.

SYS@orcl11g>SYS@orcl11g>@?/rdbms/admin/utlrp.sql

至此我们dual已经恢复完毕

SYS@orcl11g>select 'www.zbdba.com' from dual;

'WWW.ZBDBA.COM'

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

www.zbdba.com