[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,沒有報錯，這樣開發一般不會建立這樣的物件，問題應該暫時解決。