[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,没有报错，这样开发一般不会建立这样的对象，问题应该暂时解决。