首页 > 代码库 > Drop dual

Drop dual

2024-08-02 11:19:39   224人阅读

有网友删除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







Drop dual


联系
我们