[20210720]修改TRI_PREVENT_DROP_TRUNCATE触发器.txt

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