建立一个日志表
CREATE TABLE ddl_log (
operation VARCHAR2(30),
obj_owner VARCHAR2(30),
object_name VARCHAR2(30),
sql_text VARCHAR2(64),
attempt_by VARCHAR2(30),
attempt_dt DATE);
在truncate 操作前触发,写日志到ddl_log表CREATE OR REPLACE TRIGGER bf_truncate
BEFORE TRUNCATE
ON SCHEMA
DECLARE
oper ddl_log.operation%TYPE;
BEGIN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, null, USER, SYSDATE
FROM DUAL;
COMMIT;
END bf_truncate;
禁止drop 或者truncate 操作
CREATE OR REPLACE PROCEDURE log_proc (
ose ddl_log.operation%TYPE,
odoo ddl_log.obj_owner%TYPE,
odon ddl_log.object_name%TYPE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, 'Not Allowed', USER, SYSDATE
FROM DUAL;
COMMIT;
END log_proc;
CREATE OR REPLACE TRIGGER save_our_db
BEFORE DROP OR TRUNCATE
ON SCHEMA
DECLARE
oper ddl_log.operation%TYPE;
BEGIN
SELECT ora_sysevent
INTO oper
FROM DUAL;
log_proc(ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name);
IF oper = 'DROP' THEN
RAISE_APPLICATION_ERROR(-20998, 'Attempt To Drop
In Production Has Been Logged');
ELSIF oper = 'TRUNCATE' THEN
RAISE_APPLICATION_ERROR(-20999, 'Attempt To Truncate A
Production Table Has Been Logged');
END IF;
END save_our_db;
更多可以捕捉的DLL
BEFORE / AFTER ALTER
BEFORE / AFTER ANALYZE
BEFORE / AFTER ASSOCIATE STATISTICS
BEFORE / AFTER AUDIT
BEFORE / AFTER COMMENT
BEFORE / AFTER CREATE
BEFORE / AFTER DDL
BEFORE / AFTER DISASSOCIATE STATISTICS
BEFORE / AFTER DROP
BEFORE / AFTER GRANT
BEFORE / AFTER NOAUDIT
BEFORE / AFTER RENAME
BEFORE / AFTER REVOKE
BEFORE / AFTER TRUNCATE
AFTER SUSPEND