【原創】drop操作與dual還是有很大關係的（ORA-01031錯誤的解決）

今天做實驗的時候發現機器中的一個資料庫執行drop tablespace會報下面的錯誤（很奇怪的錯誤，sys使用者操作楞說許可權不足）：

SQL>drop tablespace test1;
drop tablespace test1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges

使用sql trace跟蹤了一下，發現是在執行下面的SQL語句時出錯了

The following statement encountered a error during parse:

select dummy from dual where  ora_dict_obj_type = 'TABLE'

Error encountered: ORA-01031

突然想到前幾天做實驗的時候折騰dual表來著，檢視一下現在dual表的情況：

sys@TEST>select * from dual;

D          A
- ----------
X

估計找到原因了，上次做實驗的時候給dual表新增了一列，看來drop操作看來是要用到dual表的。嘗試刪除A列，但由於dual表是屬於sys使用者的，不能刪除

sys@TEST>alter table dual drop column a;
alter table dual drop column a
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS

最後把dual表重建了，問題解決。看樣在dual表在Oracle資料庫中還是很重要的，一定不能對生產庫的dual做除select外的其他操作。解決的步驟如下：

1.先建立了一個自定義的dual表暫時頂替
sys@TEST>create table my_dual as select dummy from dual;

Table created.

sys@TEST>select * from my_dual;

D
-
X

2.建立臨時dual表的同義詞，並賦給所有使用者select的許可權
sys@TEST>create public synonym dual for my_dual;

Synonym created.

sys@TEST>grant select on my_dual to public;

Grant succeeded.

3.刪除原有dual表
sys@TEST>drop table sys.dual;

Table dropped.

4.重建dual表（第一個dual就新建的sys.dual表，第二個dual是my_dual的同義詞）
sys@TEST>create table dual as select * from dual;

Table created.

5.刪除在臨時dual表上建立的同義詞
sys@TEST>drop public synonym  dual;

Synonym dropped.

sys@TEST>select * from dual;

D
-
X

6.建立dual表的同義詞，並賦給所有使用者select的許可權
sys@TEST>create public synonym dual for sys.dual;

Synonym created.

sys@TEST>grant select on dual to public;

Grant succeeded.

7.重建dual表後，刪除表空間沒有問題了
sys@TEST>drop tablespace temp01 including contents and datafiles;

Tablespace dropped.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/498744/viewspace-258714/，如需轉載，請註明出處，否則將追究法律責任。