Detecting Unrecoverable Operations

In the view V$DATAFILE, there are two columns that are updated when an unrecoverable operation is run in the database and modified the specific datafile.

The columns are:
* UNRECOVERABLE_CHANGE# – Change number at point unrecoverable change completes.
* UNRECOVERABLE_TIME – Timestamp at point unrecoverable change completes.

Monitoring these columns is useful if you are running physical standby database in your environment and can give you a heads up on any files that may need to be copied from the primary database.

This is also useful, since a number of bugs in Oracle partitioning cause indexes to be rebuilt in nologging mode, even when the system is supposed to be logging.

Code Listing 5 : Detecting Unrecoverable Operations

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release

References Used : Oracle® Database Reference 10g Release 2 (10.2)
Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)


  1. — lists all objects in tablespaces belonging to datafiles
    — that have received unrecoverable data where the object
    — has received “physical writes direct” since instance startup
    — detecting NOLOGGING objects

    select o.owner,o.object_name,dfs.tablespace_name,s.value, t.logging
    from v$segstat s, v$tablespace ts, dba_objects o, dba_tables t,
    v$datafile df, dba_data_files dfs
    where s.statistic_name = ‘physical writes direct’
    and o.object_id = s.obj#
    and ts.ts# = s.ts#
    and s.value != 0
    and df.unrecoverable_change# != 0
    and dfs.file_name =
    and = dfs.tablespace_name
    and t.owner = o.owner
    and t.table_name = o.object_name
    order by 2,3,1 ;

Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s