Drop dual

一些互联网用户删除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

版权声明:本文博客原创文章,博客,未经同意,不得转载。

上一篇:Android定位&地图&导航——基于百度地图实现的定位功能


下一篇:Linux下C编程通过宏定义打开和关闭调试信息