Rem
Rem NAME
Rem   CATPITR.SQL
Rem FUNCTION
Rem   Tablespace Point in Time specific views 
Rem NOTES
Rem   This script must be run while connected as SYS or INTERNAL
Rem    MODIFIED   (MM/DD/YY)
Rem     apareek   12/03/98 -  fix blanks for sqlplus
Rem     apareek   10/20/98 -  block functional indexes
Rem     apareek   09/08/98 -  capture rollback segs
Rem     apareek	  08/28/98 -  make sure iot and overflow segment are self-contained
Rem	apareek	  08/20/98 -  remove IOT,Nested tables,VARRAY restrictions
Rem     apareek   08/06/98 -  remove BFILE restriction
Rem     apareek   08/05/98 -  disallow SYS owned objects
Rem     ncramesh  08/04/98 -  change for sqlplus
Rem     apareek   06/12/98 -  partitioned lobs, subpartitions
Rem     apareek   06/11/98 -  check for subpartitions
Rem     apareek   04/10/98 -  secondary objs and functional indexes
Rem     apareek   06/23/97 -  detect snapshots,fix 484226
Rem     apareek   06/24/97 -  capture primary key constraint relationships 
Rem     apareek   06/06/97 -  capture global non-partitioned indexes
Rem     apareek   05/12/97 -  use nvl for ts2_name 
Rem     apareek   04/24/97 -  add constraint name to view
Rem     apareek   04/18/97 -  fix bug 480823
Rem     apareek   04/08/97 -  change TS_PITR_DROPPED_OBJECTS name
Rem     apareek   03/27/97 -  add tablespace column to TS_PITR_DROPPED_OBJECTS
Rem     apareek   03/17/97 -  new column for  partition name in TS_PITR_CHECK
Rem     asurpur   01/03/97 -  Granting select on views to SELECT_CATALOG_ROLE
Rem     apareek   11/12/96 -  view for reporting objects lost due to tspitr
Rem     apareek   11/12/96 -  added check for queue tables
Rem     apareek   11/10/96 -  check for queue tables using new property flag
Rem     apareek   10/08/96 -  Creation
Rem
Rem
Rem View Name   : TS_PITR_CHECK
Rem
Rem View Schema 
Rem ===========
Rem
Rem     (OBJ1_OWNER,OBJ1_NAME,OBJ1_SUBNAME,OBJ1_TYPE,TS1_NAME,
Rem      OBJ2_NAME,OBJ2_SUBNAME,OBJ2_TYPE,OBJ2_OWNER, TS2_NAME,
Rem      CONSTRAINT_NAME,REASON
Rem     )
Rem
Rem Column Name                    Description
Rem ======================================== ======== ====
Rem  OBJ1_OWNER                   Owner of object 1
Rem  OBJ1_NAME                    Object 1
Rem  OBJ1_SUBNAME                 SubObject1Name
Rem  OBJ1_TYPE                    Object Type     
Rem  TS1_NAME                     Tablespace containing Object 1 
Rem  OBJ2_NAME                    Object Name 
Rem  OBJ2_SUBNAME                 SubObject2Name
Rem  OBJ2_TYPE                    Object Type
Rem  OBJ2_OWNER                   Object owner of second object 
Rem  TS2_NAME                     Tablespace containing Object 1
Rem  CONSTRAINT_NAME		name of dependent constraint
Rem  REASON                       Reason why Tablespace Point in Recovery cannot proceed           
Rem
Rem
Rem
Rem Comments on View : TS_PITR_CHECK
Rem ================================
Rem
Rem
Rem
Rem To perform Point-Intime-Tablespace-Recovery(PITR) in Oracle Version 8.0 
Rem at the time of export there can be no references going in or out of the 
Rem the Recovery Set(RS) i.e the set of non-system tablespaces to be recovered.
Rem This view captures any such references not fully contained in the recovery
Rem set.
Rem In addition, it checks for presence of objects in the recovery set
Rem that are not supported 
Rem
Rem
Rem The view needs to return 0 rows for the export phase to proceed.

Rem The following scenarios will show up in the view:

	
Rem  	- IOT tables exist in the recovery set
Rem	- IOT overflow segments exist in the recovery set
Rem	- Replication master tables are in the recovery set
Rem	- Snapshot tables are in the recovery set
Rem	- Snapshot logs are in the recovery set
Rem	- Tables or Extent tables that have associated nested tables
Rem	- Tables or Extent tables that contain a VARRAY collection type
Rem	- Tables/Clusters and associated indexes not fully contained in the RS
Rem	- Partitioned Objects (tables and indexes) not fully contained in the RS
Rem	  includes, straddling table partitions and straddling table and index
Rem	  partition
Rem	- Tables that have a parent-child relationship via referential integrity
Rem	  constraints are not fully contained in the RS
Rem	- Lob Segments, Lob Indexes and referencing tables are not fully 
Rem	  contained in the RS
Rem       - Secondary Objects, Functional indexes exist in the recovery set




Rem ==============================================================================

create or replace view STRADDLING_RS_OBJECTS 
	(OBJECT1,TS1,OBJECT2,TS2,REASON_ID ) 
as
/* check whether base table and lob segment are in different tablespaces */
(select t.obj#, t.ts#, l.lobj#, l.ts#, 'Base table and lob object not fully contained in recovery set'
from tab$ t, lob$ l
where l.ts#!=t.ts# 
  and l.obj#=t.obj#)
union all
/* iot and overflow segment are self contained */
select t.obj#, t.ts#, t2.obj#, t2.ts#, 'IOT and Overflow segment not self contained'
from   tab$ t, tab$ t2
where  t.bobj# = t2.obj# 
  and  t.ts# !=  t2.ts#
union all
/* Are there dependencies between objects in different tablespaces that 
   are enforced through constraints, also ignore constraints that are 
   disabled
*/
select t.obj#,t.ts#,cdef$.obj#,t2.ts#,'constraint between tables not contained in recovery set'
from tab$ t2,cdef$, tab$ t
where cdef$.robj#=t.obj# 
  and cdef$.obj#=t2.obj#
  and t.ts# != t2.ts#
  and cdef$.enabled is not null
union all
/* tables whose indexes are not in the same tablespace. Ignore partitioned
   objects , they are checked for separately. Also don't check for indexes
   on any unsupported TSPITR objects, also ignore indexes enforcing primary key 
   constraints, these are checked for separately
*/
select t.obj# object1, t.ts# ts1, i.obj# object2, i.ts# ts2, 'Tables and associated indexes not fully contained in the recovery set'
from  tab$ t, ind$ i
where t.obj#=i.bo# 
  and t.ts# != i.ts#
  and bitand(t.property,32)= 0
  and bitand(i.property,2 ) = 0
  and bitand(t.property, 4096) = 0
  and bitand(t.property, 131072)=0
minus  /* indexes enforcing primary key constraints */
select t.obj# object1, t.ts# ts1, i.obj# object2, i.ts# ts2, 'Tables and associated indexes not fully contained in the recovery set'
from  tab$ t, ind$ i , cdef$ cf
where t.obj#=cf.obj#
  and i.obj#=cf.enabled
  and cf.type#=2 
  and t.ts# != i.ts#
  and i.bo#=t.obj#
union all
/* Capture indexes enforcing primary key constraints, ignore internally generated snapshot/indexes */ 
select t.obj# object1, t.ts# ts1, i.obj# object2, i.ts# ts2, 'Table and Index enforcing primary key constraint not in same tablespace'
from  tab$ t, ind$ i , cdef$ cf
where t.obj#=cf.obj#
  and i.obj#=cf.enabled
  and cf.type#=2 
  and t.ts# != i.ts#
  and i.bo#=t.obj#
minus  /* primary key constraints on internally generated snapshot tables */
select t.obj# object1, t.ts# ts1, i.obj# object2, i.ts# ts2, 'Table and Index enforcing primary key constraint not in same tablespace'
from  tab$ t, ind$ i, obj$ o, user$ u, snap$ s
where t.obj#=i.bo# 
  and t.ts# != i.ts#
  and s.tname=o.name
  and s.sowner=u.name
  and o.obj#=t.obj#
  and o.owner#=u.user#
union all
/* clusters whose indexes are not in the same tablespace 		*/
select c.obj# object1, c.ts# ts1, i.obj# object2, i.ts# ts2,'Tables/Clusters and associated indexes not fully contained in the recovery set' 
from clu$ c, ind$ i
where c.obj#=i.bo# 
  and c.ts# != i.ts#
union all
/* partitioned tables with at least two partitions in different tablespaces */
select tp1.obj#, tp1.ts#, tp.obj#, tp.ts#, ' Partitioned Objects not fully contained in the recovery set' 
from tabpart$ tp, tabpart$ tp1
where tp1.part# =1
  and tp1.bo# = tp.bo#
  and tp1.ts# != tp.ts#
union all
/* partitioned indexes that are in tablespace different than any table 
   partitions
*/
select tp1.obj#,tp1.ts#,ip.obj#,ip.ts#, '  Partitioned Objects not fully contained in the recovery set'
from indpart$ ip, ind$ i, tabpart$ tp1
where tp1.part# = 1
  and tp1.bo# = i.bo#
  and ip.bo#  = i.obj#
  and tp1.ts# != ip.ts#
union all
/* partitioned table and non-partitioned index in different tablespaces */ 
select tp.obj#, tp.ts#, i.obj#, i.ts#, ' Partitioned Objects not fully contained in the recovery set'
from tabpart$ tp, ind$ i
where tp.ts#!=i.ts#
  and bitand(i.property,2) =0
  and tp.bo#=i.bo#
union all
/*  partitioned index and non-partitioned table in different tablespaces */
select t.obj#, t.ts#, ip.obj#, ip.ts#, ' Partitioned Objects not fully contained in the recovery set' 
from indpart$ ip, tab$ t, ind$ i
where ip.ts#!=t.ts#
  and t.property=0
  and ip.bo#=i.obj#
  and i.bo#=t.obj#
union all
/* Handle Composite partitions */
/* Subpartitions that are not in the same tablespace */
/* Check the tablespace of the first subpartition of partition 1
   against all tablespaces of other subpartitions for the same object */ 
select V1.obj#, V1.ts# , V2.obj#, V2.ts#, 'Subpartitions not fully contained in recovery set'
from
      ( select tsp.obj#,ts#,tsp.pobj#,tcp.bo# 
        from   tabcompart$ tcp, tabsubpart$ tsp 
        where  part# =1 and subpart# = 1
          and  tsp.pobj# = tcp.obj#) V1 ,
      ( select tsp.obj#,ts#,tsp.pobj#,tcp.bo# 
        from   tabcompart$ tcp, tabsubpart$ tsp 
        where  tsp.pobj# = tcp.obj#) V2
where
      V1.bo# = V2.bo#
  and V1.ts# != V2.ts#
union all
/* Make sure that composite table partitions and index composite partitions 
   are in the same tablespace */
select V3.obj#,V3.ts#,V4.obj#,V4.ts#, 'Table subpartition and index subpartition not fully contained in the recovery set'
from
      ( select tsp.obj#,ts#,tsp.pobj#,tcp.bo# 
        from   tabcompart$ tcp, tabsubpart$ tsp 
        where  part# =1 and subpart# = 1
          and  tsp.pobj# = tcp.obj#) V3 ,
      ( select isp.obj#,ts#,isp.pobj#,icp.bo# 
         from  indcompart$ icp, indsubpart$ isp 
         where isp.pobj# = icp.obj#) V4, ind$ i
where
        i.bo#  =  V3.bo# 
  and   V4.bo# =  i.obj# 
  and   V4.ts# != V3.ts#
union all
/* Partitions having lob fragments */
select lf.fragobj#,lf.ts#, tp.obj#,tp.ts#,'Table partition and lob fragment not in recovery set'
from   lobfrag$ lf, tabpart$ tp
where  lf.tabfragobj# = tp.obj# 
  and  tp.ts# !=lf.ts# 
union all
/* Subpartitions having lob fragments */
select tsp.obj#,tsp.ts#,lf.fragobj#,lf.ts#,'Table Subpartition and lob fragment not fully contained in pluggable set'
from tabsubpart$ tsp, lobfrag$ lf
where tsp.obj# = lf.tabfragobj#
  and tsp.ts# != lf.ts#
union all
/* Objects that are not supported 
	tab$.property
	- 0x1000  = Primary Key based OID$ column
	- 0x20000 = AQs  to be defined
*/
select obj#, ts#, -1, -1 , 'Object not allowed in Recovery Set'
from  tab$
where bitand(property ,4096) != 0
   or bitand(property,131072)!=0
union all
/* Check for Snapshots */
select o2.obj#,t.ts#, -1,-1, 'Snapshots not allowed in recovery set' 
from obj$ o, obj$ o2, tab$ t, user$ u, snap$ s
where t.obj#=o.obj#
  and s.tname=o.name
  and s.sowner=u.name
  and u.user#=o.owner#
  and o2.owner#=o.owner#
  and o2.name=s.vname
union all
/* Master tables without snapshot logs not supported */
select distinct(t.obj#), t.ts#, -1,-1 ,'Master table used for snapshots not allowed in recovery set'
from obj$ o,tab$ t,snap$ s,user$ u
where o.obj#=t.obj#
  and o.name=s.master
  and o.owner#=u.user#
  and s.mowner=u.name
  and t.trigflag=0
union all 
/* Master tables used for replication/snapshots not allowed in recovery set */
select distinct(t.obj#),t.ts#, -1, -1 ,'Master table used for snapshots/replication not allowed in recovery set'
from tab$ t
where t.trigflag!=0
union all
/* Check for snapshot logs */
select o.obj#,t.ts#,-1,-1, 'Snapshot logs not allowed in recovery set' 
from mlog$ m, tab$ t, obj$ o
where m.log=o.name 
  and o.obj#=t.obj#
/* Secondary Objects not allowed in the recovery Set */
union all
select o.obj#, t.ts#,-1,-1, 'Secondary Objects not allowed in Recovery Set'
from tab$ t, obj$ o
where o.obj#=t.obj#
  and o.flags=16
union all
/* Domain/Functional  Indexes not supported */
select i.obj#,i.ts#,-1,-1,'Domain/Functional Indexes not supported'
from ind$ i
where i.type# = 9
   or i.property = 16
union all
/****************************************************/
/*                                                  */
/* Don't allow objects owned by SYS                 */     
/*                                                  */
/****************************************************/
/* Capture non-partitioned tables owned by SYS */
select o.obj#, t.ts#,-1,-1, 'Sys owned tables not allowed in Recovery Set'
from tab$ t, obj$ o
where t.obj# = o.obj#
  and bitand(t.property,32) = 0
  and o.owner# = 0
union all
/* Capture partitioned tables owned by SYS */
select o.obj#, tp.ts#,-1,-1, 'Sys owned partitions not allowed in Recovery Set'
from tabpart$ tp, obj$ o
where tp.obj# = o.obj#
  and o.owner# = 0
union all
/* Capture clusters owned by SYS */
select o.obj#, c.ts#,-1,-1, 'Sys owned clusters not allowed in Recovery Set'
from clu$ c, obj$ o
where c.obj# = o.obj#
  and o.owner# = 0
union all
/* Capture subpartitions owned by SYS */
select o.obj#, tsp.ts#,-1,-1, 'Sys owned subpartitions not allowed in Recovery Set'
from tabsubpart$ tsp, obj$ o
where tsp.obj# = o.obj#
  and o.owner# = 0
union all
/* Capture non-partitioned indexes owned by SYS */
select o.obj#, i.ts#,-1,-1, 'Sys owned indexes not allowed in Recovery Set'
from ind$ i, obj$ o
where i.obj# = o.obj#
  and o.owner# = 0
  and bitand(i.property,2) =0
union all
/* Capture partitioned indexes owned by SYS */
select o.obj#, ip.ts#,-1,-1, 'Sys owned partitioned indexes not allowed in Recovery Set'
from indpart$ ip, obj$ o
where ip.obj# = o.obj#
  and o.owner# = 0
union all
/* Capture subpartitioned indexes owned by SYS */
select o.obj#, isp.ts#,-1,-1, 'Sys owned subpartitioned indexes not allowed in Recovery Set'
from indsubpart$ isp, obj$ o
where isp.obj# = o.obj#
  and o.owner# = 0
union all
/* Capture SYS owned lobs */
select l.lobj#, l.ts#,-1,-1, 'Sys owned lobs not allowed in Recovery Set'
from lob$ l, obj$ o
where l.lobj# = o.obj#
  and o.owner# = 0
union all
/* Capture partitioned lobs */
select lf.fragobj#, lf.ts#,-1,-1, 'Sys owned lob fragments not allowed in Recovery Set'
from lobfrag$ lf, obj$ o
where lf.fragobj# = o.obj#
  and o.owner# = 0;

grant select on STRADDLING_RS_OBJECTS to SELECT_CATALOG_ROLE;


create or replace view TS_PITR_INFO 
	(OBJ1_OWNER,OBJ1_NAME,OBJ1_SUBNAME,OBJ1_TYPE,TS1_NAME,OBJ2_NAME,OBJ2_SUBNAME,OBJ2_TYPE,OBJ2_OWNER,TS2_NAME,CONSTRAINT_NO,REASON)
as
select u.name owner,o1.name,o1.subname,
       decode(o1.type#,0, 'NEXT OBJECT', 1, 'INDEX',
                          2, 'TABLE', 3, 'CLUSTER',
                          4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                          7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                          11, 'PACKAGE BODY', 12, 'TRIGGER',
                          13, 'TYPE', 14, 'TYPE BODY',
                          19, 'TABLE PARTITION', 20, 'INDEX PARTITION',
                          21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY',
                          28, 'JAVA SOURCE', 29, 'JAVA CLASS',
                          30, 'JAVA RESOURCE', '         '),
	ts1.name,o2.name,o2.subname, 
	decode(o2.type#, 0, 'NEXT OBJECT', 1, 'INDEX',
                          2, 'TABLE', 3, 'CLUSTER',
                          4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                          7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                          11, 'PACKAGE BODY', 12, 'TRIGGER',
                          13, 'TYPE', 14, 'TYPE BODY',
                          19, 'TABLE PARTITION', 20, 'INDEX PARTITION',
                          21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY',
                          28, 'JAVA SOURCE', 29, 'JAVA CLASS',
                          30, 'JAVA RESOURCE', '         '),
	o2.owner#,ts2.name,cf.con#,s.reason_id
from straddling_rs_objects s, obj$ o1, obj$ o2, ts$ ts1, ts$ ts2 , user$ u,cdef$ cf
where s.object1=o1.obj# 
  and s.object2=o2.obj#(+)
  and s.ts1=ts1.ts#
  and s.ts2=ts2.ts#(+)
  and o1.owner#=u.user#
  and s.object2=cf.obj#(+) 
union all
/* capture rollback segs */
select 'SYS', u.name, NULL, 'ROLLBACK SEGMENT', ts.name,NULL , NULL, NULL,-1, NULL,-1,'Rollback Segment not allowed in transportable set'
from   undo$ u, ts$ ts
where  u.ts# = ts.ts# 
  and  ts.ts# != 0;

grant select on TS_PITR_INFO to SELECT_CATALOG_ROLE;

Rem ===============================================================================
Rem	VIEW NAME	TS_PITR_CHECK                                             #
Rem       									  #
Rem ===============================================================================



create or replace view TS_PITR_CHECK
	(OBJ1_OWNER,OBJ1_NAME,OBJ1_SUBNAME,OBJ1_TYPE,TS1_NAME,OBJ2_NAME,OBJ2_SUBNAME,OBJ2_TYPE,OBJ2_OWNER,TS2_NAME,CONSTRAINT_NAME,REASON)
as
select obj1_owner,obj1_name,obj1_subname,obj1_type,ts1_name,obj2_name,obj2_subname,obj2_type,u.name,nvl(ts2_name,'-1'),c.name,reason
from ts_pitr_info t, user$ u, con$ c
where u.user#(+)=t.obj2_owner
 and c.con#(+)=t.constraint_no ;

grant select on TS_PITR_CHECK to SELECT_CATALOG_ROLE;





Rem ===============================================================================
Rem	VIEW NAME	TS_PITR_OBJECTS_TO_BE_DROPPED                             #
Rem       									  #
Rem ===============================================================================

Rem This view describes all objects created in the future of the pitr recovery 
Rem time that will be lost as a consequence of performing tablespace point 
Rem in time recovery.

create or replace view TS_PITR_OBJECTS_TO_BE_DROPPED
	(owner,name,creation_time,tablespace_name)
as
(select u.name,o.name,o.ctime,tablespace_name
from user$ u, obj$ o, dba_segments s
where u.user# = o.owner#
  and o.name  = s.segment_name
  and u.name  = s.owner);

grant select on TS_PITR_OBJECTS_TO_BE_DROPPED to SELECT_CATALOG_ROLE;

