ITPub博客

首页 > 数据库 > Oracle > [20210720]修改TRI_PREVENT_DROP_TRUNCATE触发器.txt

[20210720]修改TRI_PREVENT_DROP_TRUNCATE触发器.txt

原创 Oracle 作者:lfree 时间:2021-07-21 20:29:36 0 删除 编辑

[20210720]修改TRI_PREVENT_DROP_TRUNCATE触发器.txt

--//另外的生产系统出现如下错误,可以参考我以前的链接::

ORA-20000: YOU CAN NOT TRUNCATE or DROP BIN$Bo/SlefRJ3fgU2ljqMCCgg==$0 TABLE!
ORA-06512: 在 line 6
ORA-06512: 在 "SYS.INSERT_DOCTOR_LOGS", line 17
ORA-06512: 在 line 1

--//为了避免以后再次出现错误,修改TRI_PREVENT_DROP_TRUNCATE触发器在测试环境测试看看::

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
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')
           OR (    ORA_DICT_OBJ_NAME NOT LIKE 'BIN$%'
               AND LENGTHB (ORA_DICT_OBJ_NAME) = 30))
   THEN
      raise_application_error
      (
         -20000
        ,'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!'
      );
   END IF;
END;
/
--//以上写错了,加入长度判断的情况后,感觉有点晕,瞬间感觉自己不会写代码了。改写如下:

CREATE OR REPLACE TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE
   BEFORE TRUNCATE OR DROP
   ON DATABASE
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')
      AND NOT ( ORA_DICT_OBJ_NAME LIKE 'BIN$%==$0' AND LENGTHB (ORA_DICT_OBJ_NAME) = 30)
   THEN
      raise_application_error ( -20000 ,'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!');
   END IF;
END;
/

--//增加排除ORA_DICT_OBJ_NAME LIKE 'BIN$%'的情况。我不知道drop后这些对象的命名规则,似乎都是BIN$开头,结尾==$0,并且长度等
--//于30。

2.测试:
CREATE TABLESPACE SUGAR DATAFILE
  '/mnt/ramdisk/book/sugar01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

2.测试:
create table tx tablespace sugar as select * from all_objects;
create table ty tablespace sugar as select * from all_objects where rownum<=10000;
drop table ty ;

alter TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE enable ;

SCOTT@book> update tx  set owner=lpad('a',30,'a');
SCOTT@book> select owner c30 ,OBJECT_NAME from tx where rownum=1;
C30                            OBJECT_NAME
------------------------------ --------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ICOL$

SCOTT@book> rollback ;
Rollback complete.

--//OK,没有报错,这样开发一般不会建立这样的对象,问题应该暂时解决。

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

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

注册时间:2008-01-03

  • 博文量
    3005
  • 访问量
    6760192