PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
The DBMS_LOGMNR_D
package contains two procedures:
BUILD
procedure 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.SET_TABLESPACE
procedure re-creates all LogMiner tables in an alternate tablespace.
See Also:
Oracle Database Utilities for information about using LogMiner and DBMS_LOGMNR for information on the package subprograms used in running a LogMiner session. |
This chapter contains the following topic:
You must have the role, EXECUTE_CATALOG_ROLE
to use the DBMS_LOGMNR_D
package.
Procedure | Description |
---|---|
Extracts the LogMiner dictionary to either a flat file or one or more redo log files | |
Re-creates all LogMiner tables in an alternate tablespace |
This procedure extracts the LogMiner data dictionary to either the redo log files or to a flat file.
DBMS_LOGMNR_D.BUILD ( dictionary_filename IN VARCHAR2, dictionary_location IN VARCHAR2, options IN NUMBER);
Table 48-2 describes the parameters for the BUILD
procedure.
To extract the LogMiner dictionary to a flat file, you must supply a filename and location.
To extract the LogMiner dictionary to the redo log files, specify only the STORE_IN_REDO_LOGS
option. The size of the LogMiner dictionary may cause it to be contained in multiple redo log files.
The combinations of parameters used result in the following behavior:
STORE_IN_FLAT_FILE
option, the LogMiner dictionary is extracted to a flat file with the specified name.STORE_IN_REDO_LOGS
option, the LogMiner dictionary is extracted to the redo log files.STORE_IN_REDO_LOGS
option, an error is returned.STORE_IN_FLAT_FILE
option, an error is returned.ORA-01302
: dictionary build options missing or incorrect.
This error is returned under the following conditions:
OPTIONS
parameter is not one of the supported values (STORE_IN_REDO_LOGS
, STORE_IN_FLAT_FILE)
or is not specifiedSTORE_IN_REDO_LOGS
option is not specified and neither the dictionary_filename
nor the dictionary_location
parameter is specifiedSTORE_IN_REDO_LOGS
option is specified and either the dictionary_filename
or the dictionary_location
parameter is specifiedORA-01308
: initialization parameter UTL_FILE_DIR
is not set.ORA-01336
: specified dictionary file cannot be opened.
This error is returned under the following conditions:
DBMS_LOGMNR_D.BUILD
procedure if there are any ongoing DDL operations.DBMS_LOGMNR_D.BUILD
procedure.
UTL_FILE_DIR
in the initialization parameter file. For example:
UTL_FILE_DIR = /oracle/dictionary
After setting the parameter, you must shut down and restart the database for this parameter to take effect. If you do not set this parameter, the procedure will fail.
Be aware that the DDL_DICT_TRACKING
option to the DBMS_LOGMNR.START_LOGMNR
procedure is not supported for flat file dictionaries created prior to Oracle9i. If you attempt to use the DDL_DICT_TRACKING
option with a LogMiner database extracted to a flat file prior to Oracle9i, the ORA-01330 error (problem loading a required build table) is returned.
DBMS_LOGMNR_D.BUILD
procedure must be run on a system that is running Oracle9i or later.COMPATIBLE
parameter in the initialization parameter file must be set to 9.2.0 or higher.In addition, supplemental logging (at least the minimum level) should be enabled to ensure that you can take advantage of all the features that LogMiner offers. See Oracle Database Utilities for information about using supplemental logging with LogMiner.
The following example extracts the LogMiner dictionary file to a flat file named dictionary.ora
in a specified path (/oracle/database
).
SQL> EXECUTE dbms_logmnr_d.build('dictionary.ora', - '/oracle/database/', - options => dbms_logmnr_d.store_in_flat_file);
The following example extracts the LogMiner dictionary to the redo log files.
SQL> EXECUTE dbms_logmnr_d.build( - options => dbms_logmnr_d.store_in_redo_logs);
By default, all LogMiner tables are created to use the SYSAUX
tablespace. However, it may be desirable to have LogMiner tables use an alternate tablespace. Use this procedure to move LogMiner tables to an alternate tablespace.
DBMS_LOGMNR_D.SET_TABLESPACE( new_tablespace IN VARCHAR2);
Parameter | Description |
---|---|
|
A string naming a preexisting tablespace. To move all LogMiner tables to employ this tablespace, supply this parameter. |
SYSTEM
tablespace. Oracle encourages such users to consider using the SET_TABLESPACE
procedure to move the tables to the SYSAUX
tablespace once they are confident that they will not be downgrading to an earlier version of Oracle Database.See Also:
Oracle Database Concepts and Oracle Database SQL Reference for information about tablespaces and how to create them |
The following example shows the creation of an alternate tablespace and execution of the DBMS_LOGMNR_D
.SET_TABLESPACE
procedure.
SQL> CREATE TABLESPACE logmnrts$ datafile '/usr/oracle/dbs/logmnrts.f' SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; SQL> EXECUTE dbms_logmnr_d.set_tablespace('logmnrts$');