ITPub博客

首页 > 数据库 > Oracle > [20210325]如何避免sequence意外删除.txt

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

原创 Oracle 作者:lfree 时间:2021-03-25 10:12:40 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2764778/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2907
  • 访问量
    6680936