创建oracle ddl触发器记录所有ddl操作
---- 存储DDL语句的表       
create _audit_ddl
(
opertime timestamp PRIMARY KEY,
ip varchar2(20),
hostname varchar2(30),
operation varchar2(30),
object_type varchar2(30),
object_name varchar2(30),
sql_stmt clob,
db_schema varchar2(30)
);
---- 捕获DDL语句的触发器
CREATE OR REPLACE _my_audit_ddl
after ddl ON DATABASE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
n        NUMBER;
stmt    clob := NULL;
sql_text ora_name_list_t;
BEGIN
n := ora_sql_txt(sql_text);
FOR i IN 1 .. n LOOP
stmt := stmt || sql_text(i);
END LOOP;
INSERT _audit_ddl
(opertime, ip, hostname, operation, object_type, object_name, sql_stmt,db_schema)
VALUES
(systimestamp,
sys_context('userenv', 'ip_address'),
sys_context('userenv', 'terminal'),
ora_sysevent,
sql触发器的使用
ora_dict_obj_type,
ora_dict_obj_name,
stmt,
user 
);
COMMIT;
END;
/
-- drop  procedure test_proc;
CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
dbms_output.put_line('这是一个测试中文DDL过程');
END;
/
CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
dbms_output.put_line('这是一个测试中文DDL过程');
END;
/
-----查看结果----
select * _audit_ddl;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。