oracle 19c 创建物化视图并测试logminer进行日志挖掘

-- 创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON mlog$ WITH ROWID, SEQUENCE (obj$, dbid$, ts#, txid$, ss#, na#, sqlredo$, sqlundo$, rowdata$, table_name)
INCLUDING NEW VALUES;
 
-- 创建物化视图
CREATE MATERIALIZED VIEW mv_mlog_test
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/24
WITH PRIMARY KEY
AS
SELECT obj$, dbid$, ts#, txid$, ss#, na#, sqlredo$, sqlundo$, rowdata$, 'EMPLOYEES' AS table_name
FROM mlog$ WHERE obj$ = (SELECT obj# FROM obj$ WHERE name = 'EMPLOYEES');
 
-- 测试logminer对物化视图日志的挖掘
DECLARE
  v_start_scn NUMBER;
  v_start_time TIMESTAMP;
  v_end_time TIMESTAMP;
BEGIN
  -- 设置开始和结束时间
  v_start_time := TO_TIMESTAMP('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
  v_end_time := TO_TIMESTAMP('2023-01-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
 
  -- 启动LogMiner
  DBMS_LOGMNR.START_LOGMNR(
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG,
    STARTTIME => v_start_time,
    ENDTIME => v_end_time
  );
 
  -- 查询LogMiner得到的变更
  FOR rec IN (SELECT scn, sql_redo FROM v$logmnr_contents WHERE table_name = 'EMPLOYEES') LOOP
    v_start_scn := rec.scn;
    -- 处理查询到的记录
    DBMS_OUTPUT.PUT_LINE('SCN: ' || v_start_scn || ', SQL: ' || rec.sql_redo);
  END LOOP;
 
  -- 停止LogMiner
  DBMS_LOGMNR.END_LOGMNR();
END;

这段代码首先创建了一个物化视图日志用于追踪'EMPLOYEES'表的变更，然后创建了一个物化视图以定期刷新并捕捉特定表的变更。接着，代码使用PL/SQL块声明了相关的时间变量，并利用LogMiner来挖掘在特定时间段内与'EMPLOYEES'表相关的日志内容。