[20210325]如何避免sequence意外刪除.txt

[20210325]如何避免sequence意外刪除.txt

--//連結：http://www.itpub.net/thread-2141227-1-1.html=> [體系架構] oracle中的序列，如何監控.
--//ZALBB提示建立資料庫觸發器，自己嘗試看看。

1.環境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE OR REPLACE TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE
   BEFORE TRUNCATE OR DROP ON DATABASE
DISABLE
BEGIN
   dbms_output.put_line( ora_dict_obj_type);
   IF ora_dict_obj_type in ( 'TABLE','SEQUENCE') AND ora_dict_obj_owner = 'SCOTT'
   THEN
      raise_application_error (-20000, 'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!');
   END IF;
END;
/

SYS@book> alter TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE enable ;
Trigger altered.
--//噢，前面建立的是DISABLE的觸發器，這樣操作安全一點點，必須enable才生效。

2.測試：
SCOTT@book> set serveroutput on
SCOTT@book> create sequence s1;
Sequence created.

SCOTT@book> drop sequence s1;
SEQUENCE
drop sequence s1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: YOU CAN NOT TRUNCATE or DROP S1 TABLE!
ORA-06512: at line 5
--//實際上上面的寫法會存在一些問題，比如無法執行索引的 REBUILD online;以及使用expdp匯出時建立的表無法刪除。

SCOTT@book> create index pk_empx on empx(empno);
Index created.

SCOTT@book> alter index pk_empx rebuild online;
TABLE
TABLE
alter index pk_empx rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20000: YOU CAN NOT TRUNCATE or DROP SYS_JOURNAL_91079 TABLE!
ORA-06512: at line 5
ORA-00604: error occurred at recursive SQL level 2
ORA-20000: YOU CAN NOT TRUNCATE or DROP SYS_JOURNAL_91079 TABLE!
ORA-06512: at line 5

SCOTT@book> alter index pk_empx rebuild ;
alter index pk_empx rebuild
*
ERROR at line 1:
ORA-08104: this index object 91079 is being online built or rebuilt
--//參照ora-08104的解決方法。

$ expdp scott/book
Export: Release 11.2.0.4.0 - Production on Thu Mar 25 09:05:23 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_02":  scott/a*****
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.062 MB
Processing object type SCHEMA_EXPORT/USER
....
Processing object type SCHEMA_EXPORT/JOB
. . exported "SCOTT"."SESSION_WAIT_RECORD"               386.7 KB    8122 rows
. . exported "SCOTT"."LOCK_OBJECT_RECORD"                308.4 KB    8122 rows
. . exported "SCOTT"."TEST_RID_TAB"                      171.2 KB   10000 rows
. . exported "SCOTT"."TEST_TAB"                          7.156 KB     100 rows
. . exported "SCOTT"."SYS_EXPORT_SCHEMA_01"              44.77 KB       9 rows
. . exported "SCOTT"."TY"                                97.67 KB    1000 rows
. . exported "SCOTT"."DEMO"                              5.007 KB       1 rows
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
. . exported "SCOTT"."EMPX"                              8.078 KB      14 rows
. . exported "SCOTT"."READ_STATS"                        11.64 KB       8 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."TZ"                                5.820 KB       1 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."T"                                     0 KB       0 rows
>>> ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_SCHED_EXPORT_CALLOUTS".SCHEMA_CALLOUT(:1,1,1,'11.02.00.04.00'); END;
ORA-20000: YOU CAN NOT TRUNCATE or DROP SCHEDULER$_PROGRAM_ARG TABLE!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Master table "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_02 is:
  /u01/app/oracle/admin/book/dpdump/expdat.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu Mar 25 09:05:47 2021 elapsed 0 00:00:24
--//奇怪什麼是叫 SCHEDULER$_PROGRAM_ARG。

SCOTT@book> select * from dba_objects where object_name='SCHEDULER$_PROGRAM_ARG';
no rows selected

SCOTT@book> select * from tab where tname like 'SYS_%';
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SYS_EXPORT_SCHEMA_02           TABLE
SYS_EXPORT_SCHEMA_01           TABLE

--//此外還有一些物化檢視重新整理也存在類似問題，參考連結：
--//http://blog.itpub.net/267265/viewspace-1802941/=>[20150918]禁止使用者truncate以及drop表.txt

3.做一個修改：

/* Formatted on 2021/3/25 9:15:54 (QP5 v5.269.14213.34769) */
CREATE OR REPLACE TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE
   BEFORE TRUNCATE OR DROP
   ON DATABASE
   DISABLE
BEGIN
   --//dbms_output.put_line( ora_dict_obj_type);
   IF     ora_dict_obj_type IN ('TABLE', 'SEQUENCE')
      AND ora_dict_obj_owner = 'SCOTT'
      AND ORA_DICT_OBJ_NAME NOT LIKE 'SYS\_JOURNAL\_%' ESCAPE '\'
      AND ORA_DICT_OBJ_NAME NOT LIKE 'SYS\_EXPORT\_SCHEMA_%' ESCAPE '\'
   THEN
      raise_application_error
      (
         -20000
        ,'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!'
      );
   END IF;
END;
/

SYS@book> alter TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE enable ;
Trigger altered.

4.測試：
DECLARE
   ret   BOOLEAN;
BEGIN
   ret := DBMS_REPAIR.ONLINE_INDEX_CLEAN (91079);
END;
/
--//清除ora-08104的錯誤。以sys使用者執行。

SCOTT@book> alter index pk_empx rebuild online;
Index altered.

SCOTT@book> drop table SYS_EXPORT_SCHEMA_01 purge ;
Table dropped.

SCOTT@book> drop table SYS_EXPORT_SCHEMA_02 purge ;
Table dropped.

$ rm "/u01/app/oracle/admin/book/dpdump/expdat.dmp"
$ expdp scott/book
Export: Release 11.2.0.4.0 - Production on Thu Mar 25 09:20:34 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_03":  scott/a*****
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.625 MB
Processing object type SCHEMA_EXPORT/USER
..
Processing object type SCHEMA_EXPORT/JOB
. . exported "SCOTT"."SYS_EXPORT_SCHEMA_02"              413.4 KB    1364 rows
...
. . exported "SCOTT"."T"                                     0 KB       0 rows
>>> ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_SCHED_EXPORT_CALLOUTS".SCHEMA_CALLOUT(:1,1,1,'11.02.00.04.00'); END;
ORA-20000: YOU CAN NOT TRUNCATE or DROP SCHEDULER$_PROGRAM_ARG TABLE!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Master table "SCOTT"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_03 is:
  /u01/app/oracle/admin/book/dpdump/expdat.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_03" successfully completed at Thu Mar 25 09:20:53 2021 elapsed 0 00:00:18

SCOTT@book> select * from tab where tname like 'SYS_%';
no rows selected
--//SCOTT.SYS_EXPORT_SCHEMA_03表是清除了。但是還是提示SCHEDULER$_PROGRAM_ARG.

SCOTT@book> select * from dba_objects where object_name='SCHEDULER$_PROGRAM_ARG';
no rows selected
--//也沒有這個物件...臨時表嗎。

--//暫時修改如下，問題解決,另外寫一篇blog分析這個問題。
CREATE OR REPLACE TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE
   BEFORE TRUNCATE OR DROP
   ON DATABASE
   DISABLE
BEGIN
   --//dbms_output.put_line( ora_dict_obj_type);
   IF     ora_dict_obj_type IN ('TABLE', 'SEQUENCE')
      AND ora_dict_obj_owner = 'SCOTT'
      AND ORA_DICT_OBJ_NAME NOT LIKE 'SYS\_JOURNAL\_%' ESCAPE '\'
      AND ORA_DICT_OBJ_NAME NOT LIKE 'SYS\_EXPORT\_SCHEMA_%' ESCAPE '\'
      AND ORA_DICT_OBJ_NAME not in ('SCHEDULER$_PROGRAM_ARG','SCHEDULER$_JOB_ARG')
   THEN
      raise_application_error
      (
         -20000
        ,'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!'
      );
   END IF;
END;
/

5.總結:
--//在工作中還能遇到什麼情況目前我不知道，你可以根據工作需要增加ora_dict_obj_type類，比如view等等(我沒有測試)。
--//生產系統的資料庫管理是非常複雜的問題，涉及到許多方面....