Skip Headers
Oracle® Database Reference
10g Release 2 (10.2)

Part Number B14237-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

V$LOGMNR_CONTENTS

This view contains log history information. To query this view, you must have the SELECT ANY TRANSACTION 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
CSCN 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
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
THREAD# NUMBER Number of the thread that made the change to the database
LOG_ID NUMBER This column is deprecated.
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
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
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_OBJD# NUMBER Data block data object number identifying the object within the tablespace
SEG_OWNER VARCHAR2(32) Owner of the modified 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. Possible values are:
  • 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. Possible values are:
  • UNKNOWN

  • INDEX

  • TABLE

  • TABLE PARTITION

  • UNSUPPORTED

  • TABLE_SPACE

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.
SESSION# NUMBER Session number of the session that made the change
SERIAL# NUMBER Serial number of the session that made the change
USERNAME VARCHAR2(30) Name of the user who executed the transaction
SESSION_INFO VARCHAR2(4000) Information about the database session that executed the transaction. Contains process information, machine name from which the user logged in etc. 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)

TX_NAME VARCHAR2(256) Name of the transaction that made the change. This is only meaningful if the transaction is a named transaction.
ROLLBACK NUMBER 1 = if the redo record was generated because of a partial or a full rollback of the associated transaction

0 = otherwise

OPERATION VARCHAR2(32) User level SQL operation that made the change. Possible values are:
  • 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 returns 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

  • UNSUPPORTED = change was caused by operations not currently supported by LogMiner (for example, changes made to tables with ADT columns)

OPERATION_CODE NUMBER Number of the operation code. Possible values are:
  • 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

  • 36 = ROLLBACK

  • 255 = UNSUPPORTED

SQL_REDO VARCHAR2(4000) Reconstructed SQL statement that is equivalent to the original SQL statement that made the change. Please 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. Please 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 row in V$LOGMNR_CONTENTS. RS_ID uniquely identifies the redo record that generated the row.
SEQUENCE# NUMBER Sequence number of the redo log that contained the redo record corresponding to the database change
SSN NUMBER SQL sequence number. Used in conjunction with RS_ID, this uniquely identifies a row in 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(32) Informational message about the row. For instance, the string "USER DDL" in INFO column indicates that the DDL statement returned in SQL_REDO column was the top-level DDL executed by the user and the string "INTERNAL DDL" in INFO column indicates that DDL statement returned in SQL_REDO column was executed internally by the RDBMS.
STATUS NUMBER 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 due to the fact that 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.
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
SQL_COLUMN_TYPE VARCHAR2(30) This column is deprecated.
SQL_COLUMN_NAME VARCHAR2(30) This column is deprecated.
REDO_LENGTH NUMBER This column is deprecated.
REDO_OFFSET NUMBER This column is deprecated.
UNDO_LENGTH NUMBER This column is deprecated.
UNDO_OFFSET NUMBER This column is deprecated.
DATA_OBJV# NUMBER Version number of the table being modified
SAFE_RESUME_SCN NUMBER Reserved for future use
XID RAW(8) Raw representation of the transaction identifier
PXID RAW(8) Raw representation of the parent transaction identifier
AUDIT_SESSIONID NUMBER Audit session ID associated with the user session making the change