V$LOGMNR_CONTENTS

V$LOGMNR_CONTENTS contains log history information. To query this view, you must have the LOGMINING privilege.

When a SELECT statement is executed against the V$LOGMNR_CONTENTS view, the archive redo log files are read sequentially. Translated records from the redo log files are returned as rows in the V$LOGMNR_CONTENTS view. This continues until either the filter criteria specified at startup (EndTime or endScn) are met or the end of the archive log file is reached.

Column Datatype Description

SCN

NUMBER

System change number (SCN) when the database change was made

START_SCN

NUMBER

System change number (SCN) when the transaction that contains this change started; only meaningful if the COMMITTED_DATA_ONLY option was chosen in a DBMS_LOGMNR.START_LOGMNR() invocation, NULL otherwise. This column may also be NULL if the query is run over a time/SCN range that does not contain the start of the transaction.

COMMIT_SCN

NUMBER

System change number (SCN) when the transaction committed; only meaningful if the COMMITTED_DATA_ONLY option was chosen in a DBMS_LOGMNR.START_LOGMNR() invocation

TIMESTAMP

DATE

Timestamp when the database change was made

START_TIMESTAMP

DATE

Timestamp when the transaction that contains this change started; only meaningful if the COMMITTED_DATA_ONLY option was chosen in a DBMS_LOGMNR.START_LOGMNR() invocation, NULL otherwise. This column may also be NULL if the query is run over a time/SCN range that does not contain the start of the transaction.

COMMIT_TIMESTAMP

DATE

Timestamp when the transaction committed; only meaningful if the COMMITTED_DATA_ONLY option was chosen in a DBMS_LOGMNR.START_LOGMNR() invocation

XIDUSN

NUMBER

Transaction ID undo segment number of the transaction that generated the change

XIDSLT

NUMBER

Transaction ID slot number of the transaction that generated the change

XIDSQN

NUMBER

Transaction ID sequence number of the transaction that generated the change

XID

RAW(8)

Raw representation of the transaction identifier

PXIDUSN

NUMBER

Parent transaction ID undo segment number of a parallel transaction

PXIDSLT

NUMBER

Parent transaction ID slot number of a parallel transaction

PXIDSQN

NUMBER

Parent transaction ID sequence number of a parallel transaction

PXID

RAW(8)

Raw representation of the parent transaction identifier

TX_NAME

VARCHAR2(256)

Name of the transaction that made the change; only meaningful if the transaction is a named transaction

OPERATION

VARCHAR2(32)

User level SQL operation that made the change:

  • INSERT - Change was caused by an insert statement

  • UPDATE - Change was caused by an update statement

  • DELETE - Change was caused by a delete statement

  • DDL - Change was caused by a DDL statement

  • START - Change was caused by the start of a transaction

  • COMMIT - Change was caused by the commit of a transaction

  • ROLLBACK - Change was caused by a full rollback of a transaction

  • LOB_WRITE - Change was caused by an invocation of DBMS_LOB.WRITE

  • LOB_TRIM - Change was caused by an invocation of DBMS_LOB.TRIM

  • LOB_ERASE - Change was caused by an invocation of DBMS_LOB.ERASE

  • SELECT_FOR_UPDATE - Operation was a SELECT FOR UPDATE statement

  • SEL_LOB_LOCATOR - Operation was a SELECT statement that returned a LOB locator

  • MISSING_SCN - LogMiner encountered a gap in the redo records. This is most likely because not all redo logs were registered with LogMiner.

  • INTERNAL - Change was caused by internal operations initiated by the database

  • XML DOC BEGIN - Beginning of a change to an XMLType column or table

  • XML DOC WRITE - Data for an XML document

  • XML DOC END - End of the Data for an XML document

  • UNSUPPORTED - Change was caused by operations not currently supported by LogMiner (for example, changes made to nested tables)

OPERATION_CODE

NUMBER

Number of the operation code:

  • 0 - INTERNAL

  • 1 - INSERT

  • 2 - DELETE

  • 3 - UPDATE

  • 5 - DDL

  • 6 - START

  • 7 - COMMIT

  • 9 - SELECT_LOB_LOCATOR

  • 10 - LOB_WRITE

  • 11 - LOB_TRIM

  • 25 - SELECT_FOR_UPDATE

  • 28 - LOB_ERASE

  • 34 - MISSING_SCN

  • 68 - XML DOC BEGIN

  • 70 = XML DOC WRITE

  • 71 = XML DOC END

  • 36 - ROLLBACK

  • 255 - UNSUPPORTED

ROLLBACK

NUMBER

1 = if the redo record was generated because of a partial or a full rollback of the associated transaction

0 = otherwise

SEG_OWNER

VARCHAR2(32)

Owner of the modified data segment

SEG_NAME

VARCHAR2(256)

Name of the modified data segment

TABLE_NAME

VARCHAR2(32)

Name of the modified table (in case the redo pertains to a table modification)

SEG_TYPE

NUMBER

Type of the modified data segment:

  • 0 - UNKNOWN

  • 1 - INDEX

  • 2 - TABLE

  • 19 - TABLE PARTITION

  • 20 - INDEX PARTITION

  • 34 - TABLE SUBPARTITION

  • All other values - UNSUPPORTED

SEG_TYPE_NAME

VARCHAR2(32)

Segment type name:

  • UNKNOWN

  • INDEX

  • TABLE

  • TABLE PARTITION

  • INDEX PARTITION

  • TABLE SUBPARTITION

  • UNSUPPORTED

TABLE_SPACE

VARCHAR2(32)

Name of the tablespace containing the modified data segment. This column is not populated for rows where the value of the OPERATION column is DDL. This is because DDL may operate on more than one tablespace.

ROW_ID

VARCHAR2(18)

Row ID of the row modified by the change (only meaningful if the change pertains to a DML). This will be NULL if the redo record is not associated with a DML.

USERNAME

VARCHAR2(30)

Name of the user who executed the transaction

OS_USERNAME

VARCHAR2(4000)

Name of the operating system user

MACHINE_NAME

VARCHAR2(4000)

Machine from which the user connected to the database

AUDIT_SESSIONID

NUMBER

Audit session ID associated with the user session making the change

SESSION#

NUMBER

Session number of the session that made the change

SERIAL#

NUMBER

Serial number of the session that made the change

SESSION_INFO

VARCHAR2(4000)

Information about the database session that executed the transaction. Contains process information, machine name from which the user logged in, and so on. A possible SESSION_INFO column may contain the following:

  • login_username = HR

  • client_info =

  • OS_username = jkundu

  • Machine_name = nirvan

  • OS_terminal = pts/31

  • OS_program_name = sqlplus@nirvan (TNS V1-V3)

THREAD#

NUMBER

Number of the thread that made the change to the database

SEQUENCE#

NUMBER

Sequence number of the SQL statement within the transaction. If you are mining without the COMMITED_DATA_ONLY option set, then this value is 1.

RBASQN

NUMBER

Sequence# associated with the Redo Block Address (RBA) of the redo record associated with the change

RBABLK

NUMBER

RBA block number within the log file

RBABYTE

NUMBER

RBA byte offset within the block

UBAFIL

NUMBER

Undo Block Address (UBA) file number identifying the file containing the undo block

UBABLK

NUMBER

UBA block number for the undo block

UBAREC

NUMBER

UBA record index within the undo block

UBASQN

NUMBER

UBA undo block sequence number

ABS_FILE#

NUMBER

Data block absolute file number of the block changed by the transaction

REL_FILE#

NUMBER

Data block relative file number. The file number is relative to the tablespace of the object.

DATA_BLK#

NUMBER

Data block number within the file

DATA_OBJ#

NUMBER

Data block object number identifying the object

DATA_OBJV#

NUMBER

Version number of the table being modified

DATA_OBJD#

NUMBER

Data block data object number identifying the object within the tablespace

SQL_REDO

VARCHAR2(4000)

Reconstructed SQL statement that is equivalent to the original SQL statement that made the change. Refer to Oracle Database Utilities before executing SQL_REDO to your database.

LogMiner does not generate SQL redo for temporary tables. In such a case, this column will contain the string "/* No SQL_REDO for temporary tables */".

SQL_UNDO

VARCHAR2(4000)

Reconstructed SQL statement that can be used to undo the effect of the original statement that made the change. DDL statements have no corresponding SQL_UNDO. Refer to Oracle Database Utilities before executing SQL_UNDO to your database.

LogMiner does not generate SQL undo for temporary tables. In such a case, this column will contain the string "/* No SQL_UNDO for temporary tables */".

RS_ID

VARCHAR2(32)

Record set ID. The tuple (RS_ID, SSN) together uniquely identifies a logical row change. This will usually mean one row from V$LOGMNR_CONTENTS, but could be more than one row if a single SQL statement for either the Redo or Undo would be too large to fit within the respective columns SQL_UNDO or SQL_REDO. RS_ID uniquely identifies the redo record that generated the row.

SSN

NUMBER

SQL sequence number. Used in conjunction with RS_ID, this uniquely identifies a logical row change, shown as one or more rows from the V$LOGMNR_CONTENTS view.

CSF

NUMBER

Continuation SQL flag. Possible values are:

  • 0 - Indicates SQL_REDO and SQL_UNDO is contained within the same row

  • 1 - Indicates that either SQL_REDO or SQL_UNDO is greater than 4000 bytes in size and is continued in the next row returned by the view

INFO

VARCHAR2(64)

Informational message about the row. For instance, the string "USER DDL" indicates that the DDL statement returned in the SQL_REDO column was the top-level DDL executed by the user and the string "INTERNAL DDL" indicates that the DDL statement returned in the SQL_REDO column was executed internally by the RDBMS.

STATUS

NUMBER

A value of 0 indicates that the reconstructed SQL statements as shown in the SQL_REDO and SQL_UNDO columns are valid executable SQL statements. Otherwise, the reconstructed SQL statements are not executable. This may be because no data dictionary was provided to LogMiner for the analysis, or that the data dictionary provided did not have the definition of the object being mined.

A value of 5 indicates that this row is part of a change to an XMLType column or table and the XML document must be assembled before being applied.

REDO_VALUE

NUMBER

Used as input to the DBMS_LOGMNR.MINE_VALUE() and DBMS_LOGMNR.COLUMN_PRESENT() functions

UNDO_VALUE

NUMBER

Used as input to the DBMS_LOGMNR.MINE_VALUE() and DBMS_LOGMNR.COLUMN_PRESENT() functions

SAFE_RESUME_SCN

NUMBER

Reserved for future use

CSCN

NUMBER

This column is deprecated in favor of the COMMIT_SCN column

OBJECT_ID

RAW(16)

Object identifier for DMLs to XMLType tables. For changes to non-typed tables, this column is NULL.

EDITION_NAME

VARCHAR2(30)

Identifies the edition in which a DDL statement was executed

CLIENT_ID

VARCHAR2(64)

Client identifier in the session that performed the operation, if available.

SRC_CON_NAME

VARCHAR2(30)

Contains the pluggable database (PDB) name. This information will only be available when mining with a current LogMiner dictionary.

SRC_CON_ID

NUMBER

Contains the PDB ID (the PDB_ID column from the DBA_PDBS view). This information will be available only with a current LogMiner dictionary.

SRC_CON_UID

NUMBER

Contains the PDB UID (the CON_UID column from the DBA_PDBS view). This information will be available with or without a current LogMiner dictionary.

SRC_CON_DBID

NUMBER

Contains the PDB identifier (the DBID column from the DBA_PDBS view). This information will only be available when mining with a current LogMiner dictionary.

SRC_CON_GUID

RAW(16)

Contains the GUID associated with the PDB (the GUID column from the DBA_PDBS view). This information will only be available when mining with a current LogMiner dictionary.

CON_ID

NUMBER

The ID of the container to which the data pertains. Possible values include:

  • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.

  • 1: This value is used for rows containing data that pertain to only the root

See Also: