您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 9i 資料庫監控新功能：觸發器與自動化通知實作

編輯：Oracle 資料庫基礎

前言：善用 Oracle 9i 監控機制，打造自動化運維體系

Oracle 9i 強化了資料庫事件觸發器功能，配合 UTL_FILE、UTL_SMTP 等內建套件，可輕鬆實作「寫入日誌」、「發送郵件通知」、「審計登入/結構變更/資料異動」等自動化監控需求。本文提供完整可執行的 PL/SQL 範例，涵蓋輔助程序與四大核心觸發器場景。

一、共用輔助程序

1. 寫入伺服器端日誌檔：`sp_Write_log`

CREATE OR REPLACE PROCEDURE sp_Write_log (p_text IN VARCHAR2) IS
  v_file   UTL_FILE.FILE_TYPE;
  v_line   VARCHAR2(1024);
  v_fname  VARCHAR2(50) := 'db108_alert.log';
  v_dir    VARCHAR2(100) := '/u01/product/admin/ora81/logs';  -- 需在 init.ora 設定 UTL_FILE_DIR
BEGIN
  v_file := UTL_FILE.FOPEN(v_dir, v_fname, 'A');
  v_line := TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || '||' || p_text;
  IF UTL_FILE.IS_OPEN(v_file) THEN
    UTL_FILE.PUT_LINE(v_file, v_line);
  END IF;
  UTL_FILE.FCLOSE(v_file);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(v_file) THEN UTL_FILE.FCLOSE(v_file); END IF;
    -- 可選：RAISE 或記錄至其他管道
END sp_Write_log;
/

注意：須於資料庫初始化參數 UTL_FILE_DIR 包含目標目錄，或使用 Directory 物件（Oracle 9i+ 建議）。

2. 發送 SMTP 郵件：`sp_Send_mail`

CREATE OR REPLACE PROCEDURE sp_Send_mail (
  p_rcpt      IN VARCHAR2,
  p_content   IN VARCHAR2
) IS
  v_conn  UTL_SMTP.CONNECTION;
  PROCEDURE write_hdr(p_name IN VARCHAR2, p_val IN VARCHAR2) IS
  BEGIN
    UTL_SMTP.WRITE_DATA(v_conn, p_name || ': ' || p_val || UTL_TCP.CRLF);
  END;
BEGIN
  v_conn := UTL_SMTP.OPEN_CONNECTION('smtp.yourdomain.com', 25);
  UTL_SMTP.HELO(v_conn, 'oracle');
  UTL_SMTP.MAIL(v_conn, 'oracle_monitor@yourdomain.com');
  UTL_SMTP.RCPT(v_conn, p_rcpt);
  UTL_SMTP.OPEN_DATA(v_conn);
  write_hdr('From', 'Oracle Database Monitor');
  write_hdr('To', p_rcpt);
  write_hdr('Subject', '【自動通知】資料庫事件警示');
  UTL_SMTP.WRITE_DATA(v_conn, UTL_TCP.CRLF || p_content);
  UTL_SMTP.CLOSE_DATA(v_conn);
  UTL_SMTP.QUIT(v_conn);
EXCEPTION
  WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
    BEGIN UTL_SMTP.QUIT(v_conn); EXCEPTION WHEN OTHERS THEN NULL; END;
    RAISE_APPLICATION_ERROR(-20000, '郵件發送失敗: ' || SQLERRM);
END sp_Send_mail;
/

二、核心監控觸發器實作

1. 資料庫關閉/啟動監控

CREATE OR REPLACE TRIGGER tr_db_shutdown
BEFORE SHUTDOWN ON DATABASE
DECLARE
  v_msg VARCHAR2(500);
BEGIN
  v_msg := 'User ' || ORA_LOGIN_USER || ' at ' || ORA_CLIENT_IP_ADDRESS ||
           ' is shutting down database ' || ORA_DATABASE_NAME || ' now.';
  sp_Send_mail('dba@yourdomain.com', v_msg);
EXCEPTION
  WHEN OTHERS THEN
    sp_Send_mail('dba@yourdomain.com', ORA_DATABASE_NAME || ' shutdown trigger error: ' || SQLERRM);
END;
/

關鍵提醒：關閉/啟動觸發器中避免呼叫 UTL_FILE，可能導致資料庫無法啟動；若必要，請確保關閉與啟動使用不同 Session。

2. 使用者登入審計

步驟 1：建立紀錄表

CREATE TABLE log$information (
  id          NUMBER(10),
  username    VARCHAR2(30),
  logintime   DATE,
  terminal    VARCHAR2(50),
  ipaddress   VARCHAR2(20),
  osuser      VARCHAR2(30),
  machine     VARCHAR2(64),
  program     VARCHAR2(64),
  sid         NUMBER,
  serial#     NUMBER,
  ausid       NUMBER
);

步驟 2：建立序列

CREATE SEQUENCE sq_login MINVALUE 1 MAXVALUE 999999999 START WITH 1 INCREMENT BY 1 CACHE 20;

步驟 3：建立登入觸發器

CREATE OR REPLACE TRIGGER tr_login_record
AFTER LOGON ON DATABASE
DECLARE
  v_sess  V$SESSION%ROWTYPE;
  CURSOR c_sess(p_audsid NUMBER) IS
    SELECT * FROM v$session WHERE audsid = p_audsid;
BEGIN
  OPEN c_sess(USERENV('SESSIONID'));
  FETCH c_sess INTO v_sess;
  IF c_sess%FOUND THEN
    INSERT INTO log$information (id, username, logintime, terminal, ipaddress, osuser, machine, program, sid, serial#, ausid)
    VALUES (sq_login.NEXTVAL, USER, SYSDATE, v_sess.TERMINAL,
            SYS_CONTEXT('USERENV','IP_ADDRESS'), v_sess.OSUSER, v_sess.MACHINE,
            v_sess.PROGRAM, v_sess.SID, v_sess.SERIAL#, USERENV('SESSIONID'));
  ELSE
    sp_Write_log('Session 查無資料: ' || SQLERRM);
    RAISE_APPLICATION_ERROR(-20099, '登入異常', FALSE);
  END IF;
  CLOSE c_sess;
EXCEPTION
  WHEN OTHERS THEN
    sp_Write_log('記錄登入資訊錯誤: ' || SQLERRM);
    RAISE;
END;
/

3. 全庫 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)
);

觸發器

CREATE OR REPLACE TRIGGER tr_trace_ddl
AFTER DDL ON DATABASE
DECLARE
  v_sql_txt  ORA_NAME_LIST_T;
  v_sql      VARCHAR2(4000);
BEGIN
  FOR i IN 1..ORA_SQL_TXT(v_sql_txt) LOOP
    v_sql := v_sql || v_sql_txt(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, v_sql);
EXCEPTION
  WHEN OTHERS THEN
    sp_Write_log('擷取 DDL 異常: ' || SQLERRM);
END;
/

限制單一 Schema：將 ON DATABASE 改為 ON your_schema.SCHEMA。

4. 特定表 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)
);

觸發器（以 mtamanager.emailbox 為例）

CREATE OR REPLACE TRIGGER tr_capt_sql
BEFORE DELETE OR INSERT OR UPDATE ON mtamanager.emailbox
DECLARE
  v_stmt    VARCHAR2(4000);
  v_sql_txt ORA_NAME_LIST_T;
BEGIN
  FOR i IN 1..ORA_SQL_TXT(v_sql_txt) LOOP
    v_stmt := v_stmt || v_sql_txt(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'), v_stmt, 'emailbox', 'mtamanager');
EXCEPTION
  WHEN OTHERS THEN
    sp_Write_log('擷取 DML 異常: ' || SQLERRM);
END;
/