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

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;