文章详情

  • 游戏榜单
  • 软件榜单
关闭导航
热搜榜
热门下载
热门标签
php爱好者> php文档>oracle 监控DDL操作方法

oracle 监控DDL操作方法

时间:2010-09-17  来源:marvelyu

前段时间遇见一个问题,就是一个未做任何归档备份的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;

相关阅读 更多 +
排行榜 更多 +
辰域智控app

辰域智控app

系统工具 下载
网医联盟app

网医联盟app

运动健身 下载
汇丰汇选App

汇丰汇选App

金融理财 下载