Drop dual，dropdual

Drop dual，dropdual
有網友刪除dual表出現了問題：

刪除dual表的時候hang住，然後直接shutdown abort。再重新啟動資料庫的時候，發現open的時候一直hang住，但是開啟另外一個視窗資料庫確實已經開啟的。

下面進行類比：

SYS@orcl11g>drop table dual;

一直hang在這不動

在另外一個視窗：

SYS@orcl11g>shutdown abortORACLE instance shut down.

重啟資料庫：

SYS@orcl11g>startup mountORACLE instance started.Total System Global Area  417546240 bytesFixed Size                  2213936 bytesVariable Size             327157712 bytesDatabase Buffers           83886080 bytesRedo Buffers                4288512 bytesDatabase mounted.

SYS@orcl11g>alter database open;

一直hang這個不動，開啟另外一個視窗：

SYS@orcl11g>select open_mode from v$database;OPEN_MODE----------------------------------------READ WRITESYS@orcl11g>

探索資料庫已經開啟

這是因為系統觸發器造成，在初始化參數中加入：
_system_trig_enabled=flase

再次啟動資料庫：

SYS@orcl11g>startup mountORACLE instance started.Total System Global Area  417546240 bytesFixed Size                  2213936 bytesVariable Size             327157712 bytesDatabase Buffers           83886080 bytesRedo Buffers                4288512 bytesDatabase mounted.SYS@orcl11g>show parameter _sysNAME                                 TYPE                   VALUE------------------------------------ ---------------------- ------------------------------_system_trig_enabled                 boolean                FALSEaudit_sys_operations                 boolean                FALSEaudit_syslog_level                   stringfilesystemio_options                 string                 noneldap_directory_sysauth               string                 noSYS@orcl11g>alter database open;Database altered.

最開始刪除dual表的時候也是因為系統觸發器的存在。

現在我們禁用了系統觸發器將dual刪除看是什麼效果：

SYS@orcl11g>drop table dual;Table dropped.

重啟資料庫：

SYS@orcl11g>startup nomount pfile=initorcl11g.oraORACLE instance started.Total System Global Area  417546240 bytesFixed Size                  2213936 bytesVariable Size             327157712 bytesDatabase Buffers           83886080 bytesRedo Buffers                4288512 bytesSYS@orcl11g>show parameter repNAME                                 TYPE                   VALUE------------------------------------ ---------------------- ------------------------------replication_dependency_tracking      boolean                TRUESYS@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 forcedORA-01775: looping chain of synonymsProcess ID: 2485Session 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 mountORACLE instance started.Total System Global Area  417546240 bytesFixed Size                  2213936 bytesVariable Size             327157712 bytesDatabase Buffers           83886080 bytesRedo Buffers                4288512 bytesDatabase mounted.SYS@orcl11g>show parameter repNAME                                 TYPE                   VALUE------------------------------------ ---------------------- ------------------------------replication_dependency_tracking      boolean                FALSESYS@orcl11g>ak^HSP2-0042: unknown command "a" - rest of line ignored.SYS@orcl11g>a;ter^HSYS@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