【DB笔试面试449】如何监控数据库的登陆登出、DDL语句等内容？

本文主要是介绍【DB笔试面试449】如何监控数据库的登陆登出、DDL语句等内容？，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

如何监控数据库的登陆登出、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源创计划”，欢迎正在阅读的你也加入，一起分享。

这篇关于【DB笔试面试449】如何监控数据库的登陆登出、DDL语句等内容？的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！