Oracle | DBMS_LOGMNR

Oracle DBMS_LOGMNR_D Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

Purpose Logminer Dictionary related procedures used to create a logminer dictionary

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
MARK_SAFE_MINE_POINT	INTEGER	8
STORE_IN_FLAT_FILE	INTEGER	1
STORE_IN_REDO_LOGS	INTEGER	2

Dependencies

DBMS_CAPTURE_ADM_INTERNAL	GV$LOGMNR_SESSION	UTL_FILE
DBMS_LOGMNR_INTERNAL

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-01302	Dictionary build options are missing or incorrect
ORA-01308	Initialization parameter UTL_FILE_DIR is not set
ORA-01336	Specified dictionary file cannot be opened

First Available 8i

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role

Source {ORACLE_HOME}/rdbms/admin/dbmslmd.sql

Subprograms

BUILD

SET_TABLESPACE

BUILD

Extracts the LogMiner data dictionary to either the redo log files or to a flat file. This information is saved in preparation for future analysis of redo log files using the LogMiner tool. dbms_logmnr_d.build( dictionary_filename IN VARCHAR2 DEFAULT '', dictionary_location IN VARCHAR2 DEFAULT '', -- path or directory name options IN NUMBER DEFAULT 0);

-- the database must be in archivelog mode with supplemental logging enabled

      

      SELECT log_mode, supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all, supplemental_log_data_pl

      FROM  v$database;

      

      -- store in flat file

      exec dbms_logmnr_d.build('mined_log_data.log', 'CTEMP', dbms_logmnr_d.store_in_flat_file);

      

      -- store in redo log stream

      exec dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs);

Code from /rdbms/admin/utllmup.sql

Reformatted for clarity only DECLARE rowcnt NUMBER; BEGIN SELECT COUNT(1) INTO rowcnt FROM sys.v$database v WHERE v.log_mode = 'ARCHIVELOG' AND v.supplemental_log_data_min != 'NO'; IF 0 != rowcnt THEN dbms_logmnr_d.build(options=>4); END IF; END; /

SET_TABLESPACE

Relocates LogMiner tables from SYSAUX to an alternate tablespace dbms_logmnr_d.set_tablespace(new_tablespace IN VARCHAR2);

CREATE BIGFILE TABLESPACE logmnrtbsp

      DATAFILE '/app/oracle/oradata/logmnr01.dbf' SIZE 250M

      AUTOEXTEND ON

      FORCE LOGGING

      ONLINE

      PERMANENT

      BLOCKSIZE 8K

      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K

      SEGMENT SPACE MANAGEMENT AUTO;

      

      exec dbms_logmnr_d.set_tablespace('LOGMNRTBSP');

      

      SELECT owner, segment_type, segment_name

      FROM dba_segments

      WHERE tablespace_name = 'LOGMNRTBSP'

      ORDER BY 1,2,3;

Morgan's Library Page Footer