oracle监控DDL,oracle 监控DDL操作方法

前段时间遇见一个问题，就是一个未做任何归档备份的9i数据库老是被出现一个表被莫名drop，并在回收站不留任何痕迹，最后只得创建触发器的方法，来跟踪运行这个操作的时间和主机信息，问题予以解决，记录在此：

-- Create table

create table T_WCDMA_AUDIT_DDL

(

DDL_TIME VARCHAR2(30),

SESSION_ID NUMBER,

OS_USER VARCHAR2(200),

IP_ADDRESS VARCHAR2(200),

TERMINAL VARCHAR2(200),

HOST VARCHAR2(200),

USER_NAME VARCHAR2(30),

DDL_TYPE VARCHAR2(30),

OBJECT_TYPE VARCHAR2(18),

OWNER VARCHAR2(30),

OBJECT_NAME VARCHAR2(128),

SQL_TEXT VARCHAR2(4000)

);

-- Add comments to the columns

comment on column T_ZYK_AUDIT_DDL.DDL_TIME

is '时间';

comment on column T_ZYK_AUDIT_DDL.SESSION_ID

is '会话ID';

comment on column T_ZYK_AUDIT_DDL.OS_USER

is '终端OS用户';

comment on column T_ZYK_AUDIT_DDL.IP_ADDRESS

is '终端IP地址';

comment on column T_ZYK_AUDIT_DDL.TERMINAL

is '终端';

comment on column T_ZYK_AUDIT_DDL.HOST

is '终端主机名';

comment on column T_ZYK_AUDIT_DDL.USER_NAME

is 'ORACLE用户名';

comment on column T_ZYK_AUDIT_DDL.DDL_TYPE

is 'DDL操作的类型';

comment on column T_ZYK_AUDIT_DDL.OBJECT_TYPE

is '操作的对象类型';

comment on column T_ZYK_AUDIT_DDL.OWNER

is '对象的所有者';

comment on column T_ZYK_AUDIT_DDL.OBJECT_NAME

is '对象的名称';h

comment on column T_ZYK_AUDIT_DDL.SQL_TEXT

is 'SQL语句';

--2.建立审计DDL触发器

select to_char(sysdate,'yyyy-mm-dd hh:mm:ss') from dual;

create or replace trigger tri_WCDMA_ddl after ddl on wcdma.schema declare sql_text ora_name_list_t; i integer; state_sql varchar2(4000); begin for i in 1..ora_sql_txt(sql_text) loop state_sql := state_sql||sql_text(i); end loop; state_sql :=substrb(state_sql,1,4000); insert into t_wcdma_audit_ddl (ddl_time, session_id, os_user, ip_address, terminal, host, user_name, ddl_type, object_type, owner, object_name, sql_text) values (to_char(sysdate,'yyyy-mm-dd hh:mm:ss'), sys_context('USERENV','SESSIONID'), sys_context('USERENV','OS_USER'), sys_context('USERENV','IP_ADDRESS'), sys_context('USERENV','TERMINAL'), sys_context('USERENV','HOST'), ora_login_user, ora_sysevent, ora_dict_obj_type, ora_dict_obj_owner, ora_dict_obj_name, state_sql ); exception when others then null; end;