Oracle记录用户DDL操作脚本

Oracle记录用户DDL操作脚本 --当前普通用户创建 issgzt create table TAB_DDL ( LOGIN_USER VARCHAR2(60), AUDS

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语句