Oracle9i新特性之数据库监控系列详解 （3）

    来源：赛迪网技术社区    作者：yuanye

5、监控所有DDL的触发器

当然，在此之前我们需要建立一张表，用来记录所有的DDL操作的信息。

　　
　　create table DDL$TRACE
　　(
　　LOGIN_USER　　VARCHAR2(30),
　　AUDSID　　　　NUMBER,
　　IPADDRESS　　 VARCHAR2(20),
　　SCHEMA_USER　 VARCHAR2(30),
　　SCHEMA_OBJECT VARCHAR2(30),
　　DDL_TIME　　　DATE,
　　DDL_SQL　　　 VARCHAR2(4000)
　　)

　　

下面就是触发器的主体，用来记录审计所有的DDL操作。

　　
　　CREATE OR REPLACE TRIGGER tr_trace_ddl
　　AFTER ddl
　　ON database
　　DECLARE
　　sql_text ora_name_list_t;
　　state_sql ddl$trace.ddl_sql%TYPE;
　　BEGIN
　　FOR i IN 1..ora_sql_txt(sql_text) LOOP
　　state_sql := state_sql||sql_text(i);
　　END LOOP;
　　
　　INSERT INTO ddl$trace(login_user,audsid,ipaddress,
　　schema_user,schema_object,ddl_time,ddl_sql)
　　VALUES(ora_login_user,userenv('SESSIONID'),sys_context('userenv','ip_address'),
　　ora_dict_obj_owner,ora_dict_obj_name,SYSDATE,state_sql);
　　EXCEPTION
　　WHEN OTHERS THEN
　　sp_write_log('捕获DDL语句异常错误：'||SQLERRM);
　　END tr_trace_ddl;

　　

说明：以上语句是监控整个数据库的DDL语句，如果只想监控一个用户的话，需要修改。

　　
　　ON database
　　为
　　ON uruser.schema

　　

6、捕获有需要的DML语句

对于某些特殊的表，可能需要记载DML语句，我们也需要创建一张表来记载这个信息：

　　create table CAPT$SQL
　　(
　　CAPT_TIME　DATE,
　　USERNAME　 VARCHAR2(30),
　　AUDSID　　　　NUMBER,
　　CLIENT_IP　VARCHAR2(20),
　　SQL_TEXT　 VARCHAR2(4000),
　　TABLE_NAME VARCHAR2(30),
　　OWNER　　　VARCHAR2(30)
　　)

　　

以下就是捕获特定表的DML语句的触发器:

　　
　　CREATE OR REPLACE TRIGGER tr_capt_sql
　　BEFORE DELETE OR INSERT OR UPDATE
　　ON mtamanager.emailbox
　　DECLARE
　　stmt VARCHAR2(4000);
　　sql_text ora_name_list_t;
　　BEGIN
　　FOR i IN 1..ora_sql_txt(sql_text) LOOP
　　stmt := stmt || sql_text(i);
　　END LOOP;
　　
　　INSERT INTO
　　capt$sql(CAPT_TIME,USERNAME,AUDSID,CLIENT_IP,SQL_TEXT,
　　TABLE_NAME,OWNER)
　　VALUES(sysdate,ora_login_user,userenv('SESSIONID'),
　　sys_context('userenv','ip_address'),stmt,'emailbox','mtamanager');
　　EXCEPTION
　　WHEN OTHERS THEN
　　pkgsys_manage.sp_write_log