[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等等(我没有测试)。 --//生产系统的数据库管理是非常复杂的问题，涉及到许多方面....