如何监控数据库的登陆登出、DDL语句等内容？
♣ 答案部分
系统中一些常用的监控都可以使用DDL触发器和系统触发器来实现。可以先创建一张记录DDL语句的表XB_AUDIT_DDL_LHR（由于该表记录数会很大，所以，需创建成按月自动分区的分区表），并创建合适的索引，然后创建存储过程用于插入DDL信息到该日志表中。最后再创建系统触发器就可以将DDL语句或系统事件的信息插入日志表中。下面详细说明DDL触发器和系统触发器的使用。
首先创建一张记录DDL语句的表XB_AUDIT_DDL_LHR，由于该表记录数很大，所以，创建成按月自动分区的分区表，代码如下所示：
CREATE TABLE XB_AUDIT_DDL_LHR(
ID NUMBER PRIMARY KEY,
INST_ID NUMBER,
OPER_DATE DATE,
OPERATION VARCHAR2(30),
OBJECT_OWNER VARCHAR2(255),
OBJECT_TYPE VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
SQL_TEXT VARCHAR2(4000),
SQL_FULLTEXT CLOB,
OS_USER VARCHAR2(255),
CLIENT_IP VARCHAR2(20),
CLIENT_HOSTNAME VARCHAR2(30),
DB_SCHEMA VARCHAR2(30),
SID NUMBER,
SERIAL# NUMBER,
SPID NUMBER,
SESSION_TYPE VARCHAR2(1000),
DATABASE_NAME VARCHAR2(255),
ERRORS_INFOR VARCHAR2(4000),
SQL_ID VARCHAR2(13),
PREV_SQL_ID VARCHAR2(13)
) NOLOGGING
PARTITION BY RANGE(OPER_DATE) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) SUBPARTITION BY HASH(OPERATION)
SUBPARTITION TEMPLATE (
SUBPARTITION SP1 ,
SUBPARTITION SP2 ,
SUBPARTITION SP3)
(PARTITION P201610 VALUES LESS THAN(TO_DATE('201610','YYYYMM')));
------创建主键列的用到的序列及其它常用列的索引
CREATE SEQUENCE S_XB_AUDIT_DDL_LHR START WITH 1 INCREMENT BY 1 CACHE 2000;
SELECT S_XB_AUDIT_DDL_LHR.NEXTVAL FROM DUAL;
CREATE INDEX IND_AUDIT_DDL_OBJECT_NAME ON XB_AUDIT_DDL_LHR(OBJECT_NAME,OPERATION) LOCAL NOLOGGING ;
CREATE INDEX IND_AUDIT_DDL_OS_USER ON XB_AUDIT_DDL_LHR(OS_USER) LOCAL NOLOGGING;
CREATE INDEX IND_AUDIT_DDL_SID ON XB_AUDIT_DDL_LHR(SID,SERIAL#) LOCAL NOLOGGING;
CREATE INDEX IND_AUDIT_DDL_OBJECT_DATE ON XB_AUDIT_DDL_LHR(OPER_DATE,OPERATION,OS_USER) LOCAL NOLOGGING ;
GRANT SELECT ON XB_AUDIT_DDL_LHR TO PUBLIC;
创建存储过程，用于插入DDL信息到日志表中，如下所示：
CREATE OR REPLACE PROCEDURE PRO_TRI_DDL_INSET_LHR(P_SQL_FULLTEXT VARCHAR2)
AUTHID CURRENT_USER AS
SP_XB_AUDIT_DDL_LHR XB_AUDIT_DDL_LHR%ROWTYPE;
V_TMP VARCHAR2(255);
BEGIN
SELECT A.SID,
A.SERIAL#,
(SELECT B.SPID
FROM GV$PROCESS B
WHERE B.ADDR = A.PADDR
AND B.INST_ID = USERENV('INSTANCE')) SPID,
UPPER(A.OSUSER) OSUSER,
A.MACHINE || '--' || A.PROGRAM || '--' || A.MODULE || '--' ||
A.ACTION SESSION_TYPE,
SUBSTR(P_SQL_FULLTEXT, 1, 3900),
A.SQL_ID,
A.PREV_SQL_ID,
A.USERNAME,
A.INST_ID
INTO SP_XB_AUDIT_DDL_LHR.SID,
SP_XB_AUDIT_DDL_LHR.SERIAL#,
SP_XB_AUDIT_DDL_LHR.SPID,
SP_XB_AUDIT_DDL_LHR.OS_USER,
SP_XB_AUDIT_DDL_LHR.SESSION_TYPE,
SP_XB_AUDIT_DDL_LHR.SQL_TEXT,
SP_XB_AUDIT_DDL_LHR.SQL_ID,
SP_XB_AUDIT_DDL_LHR.PREV_SQL_ID,
SP_XB_AUDIT_DDL_LHR.DB_SCHEMA,
SP_XB_AUDIT_DDL_LHR.INST_ID
FROM GV$SESSION A
WHERE A.AUDSID = USERENV('SESSIONID')
AND A.INST_ID = USERENV('INSTANCE');
INSERT INTO XB_AUDIT_DDL_LHR
(ID,
INST_ID,
OPER_DATE,
OPERATION,
OBJECT_TYPE,
OBJECT_NAME,
SQL_TEXT,
SQL_FULLTEXT,
OS_USER,
CLIENT_IP,
CLIENT_HOSTNAME,
DB_SCHEMA,
SID,
SERIAL#,
SPID,
SESSION_TYPE,
DATABASE_NAME,
OBJECT_OWNER,
ERRORS_INFOR,
SQL_ID,
PREV_SQL_ID)
VALUES
(S_XB_AUDIT_DDL_LHR.NEXTVAL,
USERENV('INSTANCE'), -- sp_xb_audit_ddl_lhr.INST_ID ora_instance_num
SYSDATE,
ORA_SYSEVENT, --sys.sysevent
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME,
SP_XB_AUDIT_DDL_LHR.SQL_TEXT,
P_SQL_FULLTEXT,
UPPER(SYS_CONTEXT('USERENV', 'OS_USER')), -- sp_xb_audit_ddl_lhr.os_user
SYS_CONTEXT('userenv', 'ip_address'), --ora_client_ip_address
SYS_CONTEXT('userenv', 'terminal'), --sys_context('userenv', 'host')
NVL2(ORA_LOGIN_USER,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SP_XB_AUDIT_DDL_LHR.DB_SCHEMA), -- SYS_CONTEXT('USERENV', 'SESSION_USER') sys.login_user
SP_XB_AUDIT_DDL_LHR.SID, ---- SYS_CONTEXT('USERENV', 'SID'),
SP_XB_AUDIT_DDL_LHR.SERIAL#,
SP_XB_AUDIT_DDL_LHR.SPID,
SP_XB_AUDIT_DDL_LHR.SESSION_TYPE || V_TMP,
ORA_DATABASE_NAME, --sys_context('USERENV', 'DB_NAME')
ORA_DICT_OBJ_OWNER,
DBMS_UTILITY.FORMAT_ERROR_STACK,
SP_XB_AUDIT_DDL_LHR.SQL_ID,
SP_XB_AUDIT_DDL_LHR.PREV_SQL_ID);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END PRO_TRI_DDL_INSET_LHR;
下面的触发器将会话退出和数据库关闭的信息加入日志表XB_AUDIT_DDL_LHR中：
CREATE OR REPLACE TRIGGER TRI_AUDIT_LOGOFF_LHR
BEFORE LOGOFF OR SHUTDOWN ON DATABASE --database 、 SCHEMA
--退出、数据库关闭
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- 调用存过
PRO_TRI_DDL_INSET_LHR('');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
此时若有会话退出的话，查询日志表XB_AUDIT_DDL_LHR可以看到如下的信息：
下面再创建登录和DDL触发器，脚本如下所示：
CREATE OR REPLACE TRIGGER TRI_AUDIT_DDL_LHR
-- AFTER DDL OR servererror OR logon OR STARTUP ON DATABASE --database 、 SCHEMA
--ddl语句、服务器报错、用户登陆、用户退出、数据库启动、数据库关闭
AFTER DDL OR SERVERERROR OR LOGON OR STARTUP ON DATABASE
/* WHEN (ora_dict_obj_name NOT IN
('XB_AUDIT_DDL_LHR', 'PRO_TRI_DDL_INSET_LHR') OR
ora_dict_obj_name IS NULL)*/
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
N NUMBER;
V_SQL_FULLTEXT VARCHAR2(32767) := NULL;
SQL_TEXT ORA_NAME_LIST_T;
BEGIN
IF ORA_SYSEVENT = 'LOGON' THEN
----------- 填充 v$session 的 CLIENT_INFO 和 CLIENT_IDENTIFIER 列
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('userenv',
'ip_address'));
DBMS_SESSION.SET_IDENTIFIER(SYS_CONTEXT('userenv', 'HOST'));
ELSE
--得到执行的DDL语句
BEGIN
N := ORA_SQL_TXT(SQL_TEXT);
FOR I IN 1 .. N LOOP
V_SQL_FULLTEXT := V_SQL_FULLTEXT || SQL_TEXT(I);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
END IF;
--插入日志
PRO_TRI_DDL_INSET_LHR(V_SQL_FULLTEXT);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END TRI_AUDIT_DDL_LHR;
测试DDL触发器，创建表、TRUNCATE表、删除表，如下所示：
CREATE TABLE TEST_DDL AS SELECT * FROM DUAL;
TRUNCATE TABLE TEST_DDL;
DROP TABLE TEST_DDL;
SELECT * FROM XB_AUDIT_DDL_LHR T WHERE T.OBJECT_NAME='TEST_DDL';
若有服务器的错误，也可以被记录下来，如下所示：
SELECT * FROM XB_AUDIT_DDL_LHR T WHERE T.OPERATION='SERVERERROR';
本小节的监控内容也解决了前面提出的一个问题，“如何监控会话的登录登出情况？”，可以通过查询日志表XB_AUDIT_DDL_LHR来解决，如下所示：
SELECT T.OS_USER, T.CLIENT_IP, COUNT(1)
FROM XB_AUDIT_DDL_LHR T
GROUP BY T.OS_USER, T.CLIENT_IP;
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。