TSPITR Recovery Set Dependencies

Before performing TSPITR you must confirm whether the recovery set is fully contained. That means whether the objects present in the tablespaces that are included in the recovery set have any references to the objects that are present in the tablespaces that are not included in the recovery set.

To find out the objects that overlap the recovery set boundaries use the TS_PITR_CHECK view. For eg, if the recovery set contains two tablespaces (TS_RAIMS and TS_CMSCRM) then use the following query,

SQL> Select *from sys.ts_pitr_check
where ( ts1_name in ('TS_RAIMS', 'TS_CMSCRM')
and ts2_name not in ('TS_RAIMS', 'TS_CMSCRM') )
or ( ts1_name not in ('TS_RAIMS', 'TS_CMSCRM')
and ts2_name in ('TS_RAIMS', 'TS_CMSCRM') );

Proceed with TSPITR only when this query returns no rows.

Use the following query to check on all the tablespaces in the database,

SQL> Select * From SYS.TS_PITR_CHECK
Where ( 'SYSTEM' IN (TS1_NAME, TS2_NAME)
and TS1_NAME <> TS2_NAME
and TS2_NAME <> '-1')
Or ( TS1_NAME <> 'SYSTEM' and TS2_NAME = '-1') ;

Thanks