Oracle记录用户DDL操作脚本
--当前普通用户创建 issgzt
create table TAB_DDL
(
LOGIN_USER VARCHAR2(60),
AUDSID NUMBER,
MACHINE VARCHAR2(60),
IPADDRESS VARCHAR2(20),
SCHEMA_USER VARCHAR2(60),
SCHEMA_OBJECT VARCHAR2(60),
DDL_TIME DATE,
DDL_SQL VARCHAR2(4000),
MODULE VARCHAR2(100),
SCHEMA_OBJECT_TYPE VARCHAR2(60)
)
;
--sys用户创建,监视用户对Oracle进行DDL操作
CREATE OR REPLACE TRIGGER SYS.tr_issgzt_ddl
AFTER ddl ON database
DECLARE
sql_text ora_name_list_t;
state_sql issgzt.TAB_DDL.ddl_sql%TYPE;
v_program issgzt.TAB_DDL.module%TYPE;
error1 exception;
BEGIN
IF SUBSTR(SYS_CONTEXT('USERENV', 'SESSION_USER'), 1, 1) = 'T' AND
SYS_CONTEXT('USERENV', 'SESSION_USER') NOT IN ('username') AND
lower(state_sql) not like 'alter user t%' THEN
raise error1;
end if;
FOR i IN 1 .. nvl(ora_sql_txt(sql_text), 2) LOOP
begin
state_sql := state_sql || sql_text(i);
Exception
when others then
null;
end;
END LOOP;
select nvl(s.program, s.MODULE)
into v_program
from v$session s
where s.SID = (select distinct sid from v$mystat);
INSERT INTO issgzt.TAB_DDL
(login_user,
audsid,
machine,
ipaddress,
schema_user,
schema_object,
SCHEMA_OBJECT_TYPE,
ddl_time,
ddl_sql,
module)
VALUES
(ora_login_user,
userenv('SESSIONID'),
SYS_CONTEXT('USERENV', 'TERMINAL'),
sys_context('userenv', 'ip_address'),
ora_dict_obj_owner,
ora_dict_obj_name,
ora_dict_obj_type,
SYSDATE,
substr(state_sql, 1, 3000),
v_program);
EXCEPTION
WHEN error1 THEN
raise_application_error(num => -20000,
msg => ' ERROR');
END tr_trace_ddl;
/
相关阅读：
两台Oracle之间配置OGG-未配置同步DDL
Oracle GoldenGate 11g单向DDL配置实战
Oracle利用dbms_metadata.get_DDL查看DDL语句
Oracle使用系统级触发器禁用DDL语句
Oracle使用系统级触发器审计重要帐号的DDL语句