ddl删除控制

CREATE OR REPLACE TRIGGER db_drop_trigger
  BEFORE truncate or drop ON ylfwjk.schema
DECLARE
  n           NUMBER;
  str_stmt    VARCHAR2(4000);
  sql_text    ora_name_list_t;
  n_trace     NUMBER;
  str_session v$session%ROWTYPE;
BEGIN
  SELECT COUNT(*)
    INTO n_trace
    FROM dual
   WHERE utl_inaddr.get_host_address IS NOT NULL
     AND sys_context('userenv', 'ip_address') IS NOT NULL
     AND sys_context('userenv', 'ip_address') <>
         utl_inaddr.get_host_address;

  IF n_trace > 0 THEN
 
    n := ora_sql_txt(sql_text);
 
    FOR i IN 1 .. n LOOP
      str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
    END LOOP;
 
    SELECT *
      INTO str_session
      FROM v$session
     WHERE audsid = userenv('sessionid');
 
    sys.dbms_system.ksdwrt(2,
                           to_char(SYSDATE, 'yyyymmdd hh24:mi:ss') ||
                           ' ORA-20000 user: ' || USER || ' program: ' ||
                           str_session.program || ' IP: ' ||
                           sys_context('userenv', 'ip_address') ||
                           ' object: ' || ora_dict_obj_name || ' DDL: ' ||
                           str_stmt);
 
    raise_application_error(-20000, '不许删除，有事请打110。');
 
  END IF;

END;