oracle 查询ddl记录,Oracle记录用户DDL操作脚本

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;

/

相关阅读：