通过LogMiner包监控正在运行的sql

一、安装Oracle LogMiner包：
$sqlplus '/as sysdba'
SQL> @?/rdbms/admin/dbmslm.sql     --用来创建DBMS_LOGMNR包，该包用来分析日志文件。
SQL> @?/rdbms/admin/dbmslmd.sql     --用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

二、安装脚本包：（sql脚本见下）
$sqlplus '/as sysdba'
SQL> @tab_trace.sql
SQL> @sp_trace.sql

三、使用前提：(sp_trace_begin过程里会检查)
1. 数据库是归档模式
2. 设置了参数 utl_file_dir

四、使用步骤：
$sqlplus '/as sysdba'
SQL> set serveroutput on;
SQL> set timing on;
SQL> set lines 1000;
1. exec sp_trace_begin;
2. 操作DB，或前台受理业务,下面可以查出业务期间发生的sql操作
3. exec sp_trace_end;  --新生成字典文件，速度较慢，首次使用
or
  exec sp_trace_end(false); --用原先的字典文件，速度加快
4. select * from mv_trace; --查看跟踪的sql

五、相关SQL：
show parameter utl_file_dir;
archive log list;
alter system set utl_file_dir = '/opt/oracle/db04' scope = spfile;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
select * from tab_scn;

六、附脚本

tab_trace.sql:
drop table sys.tab_scn;
CREATE TABLE sys.tab_scn
(
startscn NUMBER,
endscn NUMBER
);
grant select on sys.tab_scn to public;
drop public synonym tab_scn;
create public synonym tab_scn for sys.tab_scn;
drop table sys.logmnr_contents;
create table sys.logmnr_contents as select * from sys.v$logmnr_contents where 1=2;
drop public synonym logmnr_contents;
create public synonym logmnr_contents for sys.logmnr_contents;
drop materialized view sys.mv_trace;
--where过滤条件可以根据实际情况更改

create materialized view sys.mv_trace
Build deferred
refresh complete with rowid
as
SELECT TIMESTAMP,REPLACE (sql_redo, '"') sql_redo,session_info
FROM SYS.logmnr_contents
WHERE seg_owner NOT IN ('SYS')
AND operation NOT IN ('INTERNAL', 'UNSUPPORTED')
ORDER BY TIMESTAMP;
grant select on sys.mv_trace to public;
drop public synonym mv_trace;
create public synonym mv_trace for sys.mv_trace;


sp_trace.sql:
CREATE OR REPLACE PROCEDURE sp_trace_begin
/*
SQLPLUS:
set serveroutput on;
set timing on;
exec sp_trace_begin;
*/
AS
  v_sql VARCHAR2 (1000);
  v_temp VARCHAR2 (1000);
  e_not_utl_file_dir EXCEPTION;
  e_not_archivelog EXCEPTION;
BEGIN
  -- check parameter utl_file_dir

  SELECT VALUE
   INTO v_temp
   FROM SYS.v$parameter
  WHERE NAME = 'utl_file_dir';
  IF v_temp IS NULL
  THEN
    RAISE e_not_utl_file_dir;
  END IF;
  -- check archive mode

  SELECT log_mode
   INTO v_temp
   FROM v$database;
  IF v_temp = 'NOARCHIVELOG'
  THEN
    RAISE e_not_archivelog;
  END IF;
  IF v_temp IS NULL
  THEN
    RAISE e_not_utl_file_dir;
  END IF;
  -- manual archive

  v_sql := 'alter system archive log current';
  EXECUTE IMMEDIATE v_sql;
  -- insert start scn

  DELETE tab_scn;
  INSERT INTO tab_scn
        (startscn, endscn
        )
    VALUES (DBMS_FLASHBACK.get_system_change_number, NULL
        );
  COMMIT;
EXCEPTION
  WHEN e_not_utl_file_dir
  THEN
    raise_application_error (-20000, 'parameter utl_file_dir not setup.');
  WHEN e_not_archivelog
  THEN
    raise_application_error (-20001, 'database not archive mode.');
END;
/
CREATE OR REPLACE PROCEDURE sp_trace_end
/*
SQLPLUS:
set serveroutput on;
set timing on;
set lines 1000;
exec sp_trace_end;
or
exec sp_trace_end(false);
*/
(i_gen_dict BOOLEAN DEFAULT TRUE)
AS
  v_sql VARCHAR2 (1000);
  v_utl_file_dir VARCHAR2 (1000);
  v_dict_file VARCHAR2 (1000) := 'trace.dat';
  v_dict_pathfile VARCHAR2 (1000);
  -- cursor which get archive file

  CURSOR cur_archive
  IS
   SELECT *
     FROM SYS.v$archived_log
     WHERE next_change# >= (SELECT startscn
                 FROM tab_scn
                 WHERE ROWNUM = 1)
      AND next_change# <= (SELECT endscn
                 FROM tab_scn
                 WHERE ROWNUM = 1)
   ORDER BY completion_time DESC;
  rec_archive cur_archive%ROWTYPE;
  -- print data

  PROCEDURE sp_print (i_string IN VARCHAR2)
  IS
    p_string LONG DEFAULT i_string;
  BEGIN
    LOOP
     EXIT WHEN p_string IS NULL;
     DBMS_OUTPUT.put_line (RPAD ('-', 80, '-'));
     DBMS_OUTPUT.put_line (SUBSTRB (p_string, 1, 255));
     p_string := SUBSTR (p_string, 256);
   END LOOP;
  END;
BEGIN
  DBMS_OUTPUT.ENABLE (1000000);
  -- manual archive

  v_sql := 'alter system archive log current';
  EXECUTE IMMEDIATE v_sql;
  -- update endScn

  UPDATE SYS.tab_scn
   SET endscn = DBMS_FLASHBACK.get_system_change_number;
  COMMIT;
  -- get utl_file_dir parameter value

  SELECT VALUE
   INTO v_utl_file_dir
   FROM SYS.v$parameter
  WHERE NAME = 'utl_file_dir' AND ROWNUM = 1;
  -- generate dictionary file (long long time)

  IF i_gen_dict = TRUE
  THEN
    DBMS_LOGMNR_D.BUILD (dictionary_filename => v_dict_file,
              dictionary_location => v_utl_file_dir
             );
  END IF;
  -- add archive logfile

  OPEN cur_archive;
  LOOP
    FETCH cur_archive
    INTO rec_archive;
   EXIT WHEN cur_archive%NOTFOUND;
   IF cur_archive%ROWCOUNT = 1
   THEN
     DBMS_LOGMNR.add_logfile (logfilename => rec_archive.NAME,
                  options => DBMS_LOGMNR.NEW
                 );
   ELSE
     DBMS_LOGMNR.add_logfile (logfilename => rec_archive.NAME,
                  options => DBMS_LOGMNR.addfile
                 );
   END IF;
    sp_print ( 'Archive File Name '
        || TO_CHAR (cur_archive%ROWCOUNT)
        || ': '
        || rec_archive.NAME
        );
  END LOOP;
  CLOSE cur_archive;
  IF SUBSTRB (v_utl_file_dir, -1) != '/'
  THEN
    v_dict_pathfile := v_utl_file_dir || '/' || v_dict_file;
  ELSE
    v_dict_pathfile := v_utl_file_dir || v_dict_file;
  END IF;
  -- analyze archive logfile

  DBMS_LOGMNR.start_logmnr (dictfilename => v_dict_pathfile);
  -- save data to logmnr_contents,

  -- because other sessions donnt have select privilege on v$logmnr_contents

  DELETE FROM SYS.logmnr_contents;
  INSERT INTO SYS.logmnr_contents
   SELECT *
    FROM SYS.v$logmnr_contents;
  COMMIT;
  --filter data

  dbms_mview.REFRESH ('sys.mv_trace');
END;
/