eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: SYS.STRADDLING_RS_OBJECTS

Source

View Text - Preformatted


(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#
  and bitand(t.property,64)=0)
union all
/* check iots having lobs */
select t.obj#,i.ts#,l.lobj#, l.ts#,'Base table and lob object not fully contained in pluggable set'
from  tab$ t, lob$ l, ind$ i
where bitand(t.property,64)!=0
  and l.ts#!=i.ts#
  and l.obj#=t.obj#
  and i.bo# = t.obj#
union all
/* iot and overflow segment are self contained */
select t.obj#, t.ts#, i.obj#, i.ts#, 'IOT and Overflow segment not self contained'
from   tab$ t, ind$ i
where  t.bobj# = i.bo#
  and  t.ts# !=  i.ts#
  and  bitand(t.property,512) != 0
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 the following:
    partitioned object - checked separately
    indexes on unsupported TSPITR objects
    indexes enforcing primary key constraints - checked separately
    join indexes - checked separately
    IOT's with indexes of type LOB - see "check iots having lobs" above
*/
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
  and bitand(i.property, 1024) = 0			/* skip join indexes */
minus  /* indexes enforcing primary key constraints */
      /* fix bug 860417 - exclude partitioned objects */
      /* fix bug 6620517 - exclude IOT's with indexes of type LOB */
      /* Fix bug 14083382 - Exclude IOT's with secondary index */
(
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#
  and bitand(t.property,32)= 0
  and bitand(t.property, 4096) = 0
union all
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,64)!=0   /* IOT base table */
  and i.type# = 8                 /* index type is LOB */
union all
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,64)!=0   /* IOT base table */
  and bitand(i.flags,128)!=0
)
union all
/* Capture indexes enforcing primary key constraints, ignore internally generated snapshot/indexes */
/* Exclude iots , ALso exclude partitioned tables since they have no storage */
/* The tablespace for partitioned tables defaults to 0 and thus there will   */
/* always be a violation */
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#
  and bitand(t.property,64)=0
  and bitand(t.property,32)= 0
  and bitand(t.property, 4096) = 0
minus  /* primary key constraints on internally generated snapshot tables */
/* exclude partitioned objects and unsupported objects */
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#
  and bitand(t.property,32)= 0
  and bitand(t.property, 4096) = 0
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 */
/* Exclude logical partitions - they have no storage . eg partitioned iots */
select tp1.obj#, tp1.ts#, tp.obj#, tp.ts#, ' Partitioned Objects not fully contained in the recovery set'
from tabpart$ tp,
     (select  bo#,
              min(ts#) keep (dense_rank first order by part#) ts#,
              min(file#) keep (dense_rank first order by part#) file#,
              min(block#) keep (dense_rank first order by part#) block#,
              min(obj#) keep (dense_rank first order by part#) obj#
      from     tabpart$
      where file# != 0 and block# != 0
      group by bo#) tp1
where tp1.bo# = tp.bo#
  and tp1.ts# != tp.ts#
  and tp.file# != 0
  and tp.block# != 0
union all
/* partitioned indexes that are in tablespace different than any table
   partitions. Exclude partitioned iots - no storage (check for null header)
*/
select tp1.obj#,tp1.ts#,ip.obj#,ip.ts#, '  Partitioned Objects not fully contained in the recovery set'
from indpart$ ip, ind$ i,
     (select   bo#,
               min(ts#) keep (dense_rank first order by part#) ts#,
               min(file#) keep (dense_rank first order by part#) file#,
               min(block#) keep (dense_rank first order by part#) block#,
               min(obj#) keep (dense_rank first order by part#) obj#
      from     tabpart$
      where    file# != 0 and block# != 0
      group by bo#) tp1
where 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#
  and bitand(i.property, 1024) = 0			/* skip join indexes */
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#
  and bitand(i.property, 1024) = 0			/* skip join indexes */
union all
/* join index and parent table (or table (sub)partition) in different ts */
select v1.obj#, v1.ts#, v2.obj#, v2.ts#, 'Join Index related objects not fully contained in the recovery set'
from
 (select obj# to#, obj#, ts# from tab$
  union all
  select bo# to#, obj#, ts# from tabpart$
  union all
  select tcp.bo# to#, tsp.obj#, tsp.ts# from tabsubpart$ tsp, tabcompart$ tcp
   where tsp.pobj# = tcp.obj#
 ) v1,
 (select obj# io#, obj#, ts# from ind$
   where bitand(property, 1024) = 1024
  union all
  select ip.bo# io#, ip.obj#, ip.ts# from indpart$ ip, ind$ i
   where ip.bo# = i.obj# and bitand(i.property, 1024) = 1024
  union all
  select icp.bo# io#, isp.obj#, isp.ts#
   from indsubpart$ isp, indcompart$ icp, ind$ i
   where isp.pobj# = icp.obj# and icp.bo# = i.obj#
   and bitand(i.property, 1024) = 1024
 ) v2,
 jijoin$ j
where v2.io# = j.obj#
and (v1.to# = j.tab1obj# or v1.to# = j.tab2obj#)
and v1.ts# != v2.ts#
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   min(tsp.obj#) keep (dense_rank first
                   order by tcp.part#, tsp.subpart#) obj#,
                 min(tsp.ts#) keep (dense_rank first
                   order by tcp.part#, tsp.subpart#) ts#,
                 tcp.bo# bo#
        from     tabcompart$ tcp, tabsubpart$ tsp
        where    tsp.pobj# = tcp.obj#
        group by tcp.bo#) V1,
      ( select tsp.obj#,ts#,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   min(tsp.obj#) keep (dense_rank first
                   order by tcp.part#, tsp.subpart#) obj#,
                 min(tsp.ts#) keep (dense_rank first
                   order by tcp.part#, tsp.subpart#) ts#,
                 tcp.bo# bo#
        from     tabcompart$ tcp, tabsubpart$ tsp
        where    tsp.pobj# = tcp.obj#
        group by tcp.bo#) V3,
      ( select isp.obj#,ts#,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
   Block REF columns with ref constraints - bug 895775
*/
/* get all non partitioned, non iot unsupported objects */
select obj#, ts#, -1, -1 , ' Object not allowed in Recovery Set'
from  tab$
where (bitand(property ,4096) != 0
   or bitand(property,131072)!=0)
  and bitand(property,64)=0
  and bitand(property,32)=0
union all
/* get iot objects that aren't supported */
select i.bo#, i.ts# , -1, -1,' Object not allowed in Pluggable Set'
from tab$ t, ind$ i
where t.obj# = i.bo#
  and bitand(t.property, 64)!=0
  and (bitand(t.property, 4096)!=0
        or bitand(t.property,131072)!=0)
union all
/* partitioned objects that aren't supported */
select tp.bo#,tp.ts#,-1,-1, ' Object not allowed in Pluggable Set'
from tabpart$ tp, tab$ t
where t.obj# = tp.bo#
  and (bitand(t.property, 4096)!=0
       or bitand(t.property,131072)!=0)
union all
/* Capture subpartitioned tables that have REFs having ref constraints */
select tcp.bo#,tsp.ts#, -1, -1, '104 Object not allowed in Pluggable Set'
from tab$ t, tabcompart$ tcp, tabsubpart$ tsp
where tsp.pobj# = tcp.obj#
  and tcp.bo# = t.obj#
  and (bitand(t.property, 4096)!=0
       or bitand(t.property,131072)!=0)
union all
/* Bug 895775 Begin  */
/*  Once this bug gets fixed we should rip this code out */
/* Capture tables having referential constraints on REF column */
/* Ignore tables that are stored as IOTs or are partitioned/subpartitioned */
select t.obj#, t.ts#, -1, -1, 'Table has REF column as OID column'
from tab$ t, col$ c
where c.obj# = t.obj#
  and bitand(c.property, 524288)!=0
  and bitand(t.property,64)=0
  and bitand(t.property,32)=0
union all
/* Capture IOTs having referential constraints on REF column */
select i.bo#, i.ts# , -1, -1, 'Table has REF column as OID column'
from ind$ i, col$ c, tab$ t
where c.obj# = i.bo#
and bitand(c.property, 524288)!=0
and bitand(t.property,64)!=0
and t.obj# = i.bo#
union all
/* Capture partitioned tables that have REFs having ref constraints */
select tp.bo#,tp.ts#,-1,-1, 'Table has REF column as OID column'
from tabpart$ tp, col$ c
where c.obj# = tp.bo#
  and bitand(c.property, 524288)!=0
union all
/* Capture subpartitioned tables that have REFs having ref constraints */
select tcp.bo#,tsp.ts#, -1, -1, 'Table has REF column as OID column'
from col$ c, tabcompart$ tcp, tabsubpart$ tsp
where tsp.pobj# = tcp.obj#
  and tcp.bo# = c.obj#
  and bitand(c.property, 524288)!=0
/* End Bug 895775 */
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 */
/* Bug 5192382: The lower 2 bytes of trigflag are used for various
   combinations of snapshot/replication. The upper flags are used
   by other layers. So check for a table being master table for
   snapshot/replication is bitand(t.trigflag, 65535) != 0.
   See KQLDTVTF_MSK in kqld.h.
*/
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 bitand(t.trigflag, 65535) = 0
union all
/* Master tables used for replication/snapshots not allowed in recovery set
   Exclude partitioned tables, they will be checked saperately.
*/
select distinct t.obj#,t.ts#, -1, -1 ,'Master table used for snapshots/replication not allowed in recovery set'
from tab$ t
where bitand(t.trigflag, 65535) != 0
  and bitand(t.property,32) = 0
union all
/* Master tables used for replication/snapshots not allowed in pluggable set
   Tablespace for partitioned tables defaults to 0, hence we need to
   do the check against tabpart$
*/
select distinct t.obj#,tp.ts#, -1, -1 ,'Master table used for snapshots/replication not allowed in pluggable set'
from tab$ t, tabpart$ tp
where bitand(t.trigflag, 65535) != 0
  and t.obj#      = tp.bo#
  and bitand(t.property,32) != 0
union all
/* Capture snapshots on subpartitioned tables */
select distinct t.obj#, tsp.ts#, -1, -1, 'Master table used for snapshots/replication not allowed in pluggable set'
from tab$ t, tabcompart$ tcp, tabsubpart$ tsp
where tsp.pobj#     = tcp.obj#
  and tcp.bo#       = t.obj#
  and bitand(t.trigflag, 65535) != 0
  and bitand(t.property,32) != 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
union all
/* Make sure that for IOTs the index partitions are all self contained */
select ip1.obj#, ip1.ts#, ip2.obj#, ip2.ts# , ' IOT partitions not self contained'
from (select   bo#,
               min(ts#) keep (dense_rank first order by part#) ts#,
               min(obj#) keep (dense_rank first order by part#) obj#
      from     indpart$
      group by bo#) ip1, indpart$ ip2, ind$ i, tab$ t
where ip1.bo#= i.obj#
and ip1.ts# != ip2.ts#
and ip2.bo# = i.obj#
and i.bo# = t.obj#
and bitand(t.property,64)!=0
union all
/* Make sure that for IOTs, overflow segments and index partitions are self
contained. We can take the first overflow segment partition and run it against
all the index partitions.  This guarantees completeness since all index
partitions are checked for seperately for self containment */
select tp.obj#, tp.ts#,ip.obj#,ip.ts#, ' Overflow segment and index partition not self contained'
from   indpart$ ip, ind$ i, tab$ t,
       (select  bo#,
                min(ts#) keep (dense_rank first order by part#) ts#,
                min(obj#) keep (dense_rank first order by part#) obj#
        from     tabpart$
        group by bo#) tp
where  tp.bo# = t.obj#
  and  bitand(t.property,512)!=0
  and  t.bobj# = i.bo#
  and  ip.bo#= i.obj#
  and  ip.ts# != tp.ts#

View Text - HTML Formatted

(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#
AND BITAND(T.PROPERTY
, 64)=0) UNION ALL /* CHECK IOTS HAVING LOBS */ SELECT T.OBJ#
, I.TS#
, L.LOBJ#
, L.TS#
, 'BASE TABLE
AND LOB OBJECT NOT FULLY CONTAINED IN PLUGGABLE SET' FROM TAB$ T
, LOB$ L
, IND$ I WHERE BITAND(T.PROPERTY
, 64)!=0
AND L.TS#!=I.TS#
AND L.OBJ#=T.OBJ#
AND I.BO# = T.OBJ# UNION ALL /* IOT
AND OVERFLOW SEGMENT ARE SELF CONTAINED */ SELECT T.OBJ#
, T.TS#
, I.OBJ#
, I.TS#
, 'IOT
AND OVERFLOW SEGMENT NOT SELF CONTAINED' FROM TAB$ T
, IND$ I WHERE T.BOBJ# = I.BO#
AND T.TS# != I.TS#
AND BITAND(T.PROPERTY
, 512) != 0 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 THE FOLLOWING: PARTITIONED OBJECT - CHECKED SEPARATELY INDEXES ON UNSUPPORTED TSPITR OBJECTS INDEXES ENFORCING PRIMARY KEY CONSTRAINTS - CHECKED SEPARATELY JOIN INDEXES - CHECKED SEPARATELY IOT'S WITH INDEXES OF TYPE LOB - SEE "CHECK IOTS HAVING LOBS" ABOVE */ 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
AND BITAND(I.PROPERTY
, 1024) = 0 /* SKIP JOIN INDEXES */ MINUS /* INDEXES ENFORCING PRIMARY KEY CONSTRAINTS */ /* FIX BUG 860417 - EXCLUDE PARTITIONED OBJECTS */ /* FIX BUG 6620517 - EXCLUDE IOT'S WITH INDEXES OF TYPE LOB */ /* FIX BUG 14083382 - EXCLUDE IOT'S WITH SECONDARY INDEX */ ( 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#
AND BITAND(T.PROPERTY
, 32)= 0
AND BITAND(T.PROPERTY
, 4096) = 0 UNION ALL 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
, 64)!=0 /* IOT BASE TABLE */
AND I.TYPE# = 8 /* INDEX TYPE IS LOB */ UNION ALL 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
, 64)!=0 /* IOT BASE TABLE */
AND BITAND(I.FLAGS
, 128)!=0 ) UNION ALL /* CAPTURE INDEXES ENFORCING PRIMARY KEY CONSTRAINTS
, IGNORE INTERNALLY GENERATED SNAPSHOT/INDEXES */ /* EXCLUDE IOTS
, ALSO EXCLUDE PARTITIONED TABLES SINCE THEY HAVE NO STORAGE */ /* THE TABLESPACE FOR PARTITIONED TABLES DEFAULTS TO 0
AND THUS THERE WILL */ /* ALWAYS BE A VIOLATION */ 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#
AND BITAND(T.PROPERTY
, 64)=0
AND BITAND(T.PROPERTY
, 32)= 0
AND BITAND(T.PROPERTY
, 4096) = 0 MINUS /* PRIMARY KEY CONSTRAINTS ON INTERNALLY GENERATED SNAPSHOT TABLES */ /* EXCLUDE PARTITIONED OBJECTS
AND UNSUPPORTED OBJECTS */ 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#
AND BITAND(T.PROPERTY
, 32)= 0
AND BITAND(T.PROPERTY
, 4096) = 0 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 */ /* EXCLUDE LOGICAL PARTITIONS - THEY HAVE NO STORAGE . EG PARTITIONED IOTS */ SELECT TP1.OBJ#
, TP1.TS#
, TP.OBJ#
, TP.TS#
, ' PARTITIONED OBJECTS NOT FULLY CONTAINED IN THE RECOVERY SET' FROM TABPART$ TP
, (SELECT BO#
, MIN(TS#) KEEP (DENSE_RANK FIRST ORDER BY PART#) TS#
, MIN(FILE#) KEEP (DENSE_RANK FIRST ORDER BY PART#) FILE#
, MIN(BLOCK#) KEEP (DENSE_RANK FIRST ORDER BY PART#) BLOCK#
, MIN(OBJ#) KEEP (DENSE_RANK FIRST ORDER BY PART#) OBJ#
FROM TABPART$
WHERE FILE# != 0
AND BLOCK# != 0 GROUP BY BO#) TP1 WHERE TP1.BO# = TP.BO#
AND TP1.TS# != TP.TS#
AND TP.FILE# != 0
AND TP.BLOCK# != 0 UNION ALL /* PARTITIONED INDEXES THAT ARE IN TABLESPACE DIFFERENT THAN ANY TABLE PARTITIONS. EXCLUDE PARTITIONED IOTS - NO STORAGE (CHECK FOR NULL HEADER) */ SELECT TP1.OBJ#
, TP1.TS#
, IP.OBJ#
, IP.TS#
, ' PARTITIONED OBJECTS NOT FULLY CONTAINED IN THE RECOVERY SET' FROM INDPART$ IP
, IND$ I
, (SELECT BO#
, MIN(TS#) KEEP (DENSE_RANK FIRST ORDER BY PART#) TS#
, MIN(FILE#) KEEP (DENSE_RANK FIRST ORDER BY PART#) FILE#
, MIN(BLOCK#) KEEP (DENSE_RANK FIRST ORDER BY PART#) BLOCK#
, MIN(OBJ#) KEEP (DENSE_RANK FIRST ORDER BY PART#) OBJ#
FROM TABPART$
WHERE FILE# != 0
AND BLOCK# != 0 GROUP BY BO#) TP1 WHERE 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#
AND BITAND(I.PROPERTY
, 1024) = 0 /* SKIP JOIN INDEXES */ 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#
AND BITAND(I.PROPERTY
, 1024) = 0 /* SKIP JOIN INDEXES */ UNION ALL /* JOIN INDEX
AND PARENT TABLE (OR TABLE (SUB)PARTITION) IN DIFFERENT TS */ SELECT V1.OBJ#
, V1.TS#
, V2.OBJ#
, V2.TS#
, 'JOIN INDEX RELATED OBJECTS NOT FULLY CONTAINED IN THE RECOVERY SET' FROM (SELECT OBJ# TO#
, OBJ#
, TS#
FROM TAB$ UNION ALL SELECT BO# TO#
, OBJ#
, TS#
FROM TABPART$ UNION ALL SELECT TCP.BO# TO#
, TSP.OBJ#
, TSP.TS#
FROM TABSUBPART$ TSP
, TABCOMPART$ TCP
WHERE TSP.POBJ# = TCP.OBJ# ) V1
, (SELECT OBJ# IO#
, OBJ#
, TS#
FROM IND$
WHERE BITAND(PROPERTY
, 1024) = 1024 UNION ALL SELECT IP.BO# IO#
, IP.OBJ#
, IP.TS#
FROM INDPART$ IP
, IND$ I
WHERE IP.BO# = I.OBJ#
AND BITAND(I.PROPERTY
, 1024) = 1024 UNION ALL SELECT ICP.BO# IO#
, ISP.OBJ#
, ISP.TS#
FROM INDSUBPART$ ISP
, INDCOMPART$ ICP
, IND$ I
WHERE ISP.POBJ# = ICP.OBJ#
AND ICP.BO# = I.OBJ#
AND BITAND(I.PROPERTY
, 1024) = 1024 ) V2
, JIJOIN$ J WHERE V2.IO# = J.OBJ# AND (V1.TO# = J.TAB1OBJ# OR V1.TO# = J.TAB2OBJ#) AND V1.TS# != V2.TS# 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 MIN(TSP.OBJ#) KEEP (DENSE_RANK FIRST ORDER BY TCP.PART#
, TSP.SUBPART#) OBJ#
, MIN(TSP.TS#) KEEP (DENSE_RANK FIRST ORDER BY TCP.PART#
, TSP.SUBPART#) TS#
, TCP.BO# BO#
FROM TABCOMPART$ TCP
, TABSUBPART$ TSP
WHERE TSP.POBJ# = TCP.OBJ# GROUP BY TCP.BO#) V1
, ( SELECT TSP.OBJ#
, TS#
, 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 MIN(TSP.OBJ#) KEEP (DENSE_RANK FIRST ORDER BY TCP.PART#
, TSP.SUBPART#) OBJ#
, MIN(TSP.TS#) KEEP (DENSE_RANK FIRST ORDER BY TCP.PART#
, TSP.SUBPART#) TS#
, TCP.BO# BO#
FROM TABCOMPART$ TCP
, TABSUBPART$ TSP
WHERE TSP.POBJ# = TCP.OBJ# GROUP BY TCP.BO#) V3
, ( SELECT ISP.OBJ#
, TS#
, 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 BLOCK REF COLUMNS WITH REF CONSTRAINTS - BUG 895775 */ /* GET ALL NON PARTITIONED
, NON IOT UNSUPPORTED OBJECTS */ SELECT OBJ#
, TS#
, -1
, -1
, ' OBJECT NOT ALLOWED IN RECOVERY SET' FROM TAB$ WHERE (BITAND(PROPERTY
, 4096) != 0 OR BITAND(PROPERTY
, 131072)!=0)
AND BITAND(PROPERTY
, 64)=0
AND BITAND(PROPERTY
, 32)=0 UNION ALL /* GET IOT OBJECTS THAT AREN'T SUPPORTED */ SELECT I.BO#
, I.TS#
, -1
, -1
, ' OBJECT NOT ALLOWED IN PLUGGABLE SET' FROM TAB$ T
, IND$ I WHERE T.OBJ# = I.BO#
AND BITAND(T.PROPERTY
, 64)!=0
AND (BITAND(T.PROPERTY
, 4096)!=0 OR BITAND(T.PROPERTY
, 131072)!=0) UNION ALL /* PARTITIONED OBJECTS THAT AREN'T SUPPORTED */ SELECT TP.BO#
, TP.TS#
, -1
, -1
, ' OBJECT NOT ALLOWED IN PLUGGABLE SET' FROM TABPART$ TP
, TAB$ T WHERE T.OBJ# = TP.BO#
AND (BITAND(T.PROPERTY
, 4096)!=0 OR BITAND(T.PROPERTY
, 131072)!=0) UNION ALL /* CAPTURE SUBPARTITIONED TABLES THAT HAVE REFS HAVING REF CONSTRAINTS */ SELECT TCP.BO#
, TSP.TS#
, -1
, -1
, '104 OBJECT NOT ALLOWED IN PLUGGABLE SET' FROM TAB$ T
, TABCOMPART$ TCP
, TABSUBPART$ TSP WHERE TSP.POBJ# = TCP.OBJ#
AND TCP.BO# = T.OBJ#
AND (BITAND(T.PROPERTY
, 4096)!=0 OR BITAND(T.PROPERTY
, 131072)!=0) UNION ALL /* BUG 895775 BEGIN */ /* ONCE THIS BUG GETS FIXED WE SHOULD RIP THIS CODE OUT */ /* CAPTURE TABLES HAVING REFERENTIAL CONSTRAINTS ON REF COLUMN */ /* IGNORE TABLES THAT ARE STORED AS IOTS OR ARE PARTITIONED/SUBPARTITIONED */ SELECT T.OBJ#
, T.TS#
, -1
, -1
, 'TABLE HAS REF COLUMN AS OID COLUMN' FROM TAB$ T
, COL$ C WHERE C.OBJ# = T.OBJ#
AND BITAND(C.PROPERTY
, 524288)!=0
AND BITAND(T.PROPERTY
, 64)=0
AND BITAND(T.PROPERTY
, 32)=0 UNION ALL /* CAPTURE IOTS HAVING REFERENTIAL CONSTRAINTS ON REF COLUMN */ SELECT I.BO#
, I.TS#
, -1
, -1
, 'TABLE HAS REF COLUMN AS OID COLUMN' FROM IND$ I
, COL$ C
, TAB$ T WHERE C.OBJ# = I.BO# AND BITAND(C.PROPERTY
, 524288)!=0 AND BITAND(T.PROPERTY
, 64)!=0 AND T.OBJ# = I.BO# UNION ALL /* CAPTURE PARTITIONED TABLES THAT HAVE REFS HAVING REF CONSTRAINTS */ SELECT TP.BO#
, TP.TS#
, -1
, -1
, 'TABLE HAS REF COLUMN AS OID COLUMN' FROM TABPART$ TP
, COL$ C WHERE C.OBJ# = TP.BO#
AND BITAND(C.PROPERTY
, 524288)!=0 UNION ALL /* CAPTURE SUBPARTITIONED TABLES THAT HAVE REFS HAVING REF CONSTRAINTS */ SELECT TCP.BO#
, TSP.TS#
, -1
, -1
, 'TABLE HAS REF COLUMN AS OID COLUMN' FROM COL$ C
, TABCOMPART$ TCP
, TABSUBPART$ TSP WHERE TSP.POBJ# = TCP.OBJ#
AND TCP.BO# = C.OBJ#
AND BITAND(C.PROPERTY
, 524288)!=0 /* END BUG 895775 */ 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 */ /* BUG 5192382: THE LOWER 2 BYTES OF TRIGFLAG ARE USED FOR VARIOUS COMBINATIONS OF SNAPSHOT/REPLICATION. THE UPPER FLAGS ARE USED BY OTHER LAYERS. SO CHECK FOR A TABLE BEING MASTER TABLE FOR SNAPSHOT/REPLICATION IS BITAND(T.TRIGFLAG
, 65535) != 0. SEE KQLDTVTF_MSK IN KQLD.H. */ 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 BITAND(T.TRIGFLAG
, 65535) = 0 UNION ALL /* MASTER TABLES USED FOR REPLICATION/SNAPSHOTS NOT ALLOWED IN RECOVERY SET EXCLUDE PARTITIONED TABLES
, THEY WILL BE CHECKED SAPERATELY. */ SELECT DISTINCT T.OBJ#
, T.TS#
, -1
, -1
, 'MASTER TABLE USED FOR SNAPSHOTS/REPLICATION NOT ALLOWED IN RECOVERY SET' FROM TAB$ T WHERE BITAND(T.TRIGFLAG
, 65535) != 0
AND BITAND(T.PROPERTY
, 32) = 0 UNION ALL /* MASTER TABLES USED FOR REPLICATION/SNAPSHOTS NOT ALLOWED IN PLUGGABLE SET TABLESPACE FOR PARTITIONED TABLES DEFAULTS TO 0
, HENCE WE NEED TO DO THE CHECK AGAINST TABPART$ */ SELECT DISTINCT T.OBJ#
, TP.TS#
, -1
, -1
, 'MASTER TABLE USED FOR SNAPSHOTS/REPLICATION NOT ALLOWED IN PLUGGABLE SET' FROM TAB$ T
, TABPART$ TP WHERE BITAND(T.TRIGFLAG
, 65535) != 0
AND T.OBJ# = TP.BO#
AND BITAND(T.PROPERTY
, 32) != 0 UNION ALL /* CAPTURE SNAPSHOTS ON SUBPARTITIONED TABLES */ SELECT DISTINCT T.OBJ#
, TSP.TS#
, -1
, -1
, 'MASTER TABLE USED FOR SNAPSHOTS/REPLICATION NOT ALLOWED IN PLUGGABLE SET' FROM TAB$ T
, TABCOMPART$ TCP
, TABSUBPART$ TSP WHERE TSP.POBJ# = TCP.OBJ#
AND TCP.BO# = T.OBJ#
AND BITAND(T.TRIGFLAG
, 65535) != 0
AND BITAND(T.PROPERTY
, 32) != 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 UNION ALL /* MAKE SURE THAT FOR IOTS THE INDEX PARTITIONS ARE ALL SELF CONTAINED */ SELECT IP1.OBJ#
, IP1.TS#
, IP2.OBJ#
, IP2.TS#
, ' IOT PARTITIONS NOT SELF CONTAINED' FROM (SELECT BO#
, MIN(TS#) KEEP (DENSE_RANK FIRST ORDER BY PART#) TS#
, MIN(OBJ#) KEEP (DENSE_RANK FIRST ORDER BY PART#) OBJ#
FROM INDPART$ GROUP BY BO#) IP1
, INDPART$ IP2
, IND$ I
, TAB$ T WHERE IP1.BO#= I.OBJ# AND IP1.TS# != IP2.TS# AND IP2.BO# = I.OBJ# AND I.BO# = T.OBJ# AND BITAND(T.PROPERTY
, 64)!=0 UNION ALL /* MAKE SURE THAT FOR IOTS
, OVERFLOW SEGMENTS
AND INDEX PARTITIONS ARE SELF CONTAINED. WE CAN TAKE THE FIRST OVERFLOW SEGMENT PARTITION
AND RUN IT AGAINST ALL THE INDEX PARTITIONS. THIS GUARANTEES COMPLETENESS SINCE ALL INDEX PARTITIONS ARE CHECKED FOR SEPERATELY FOR SELF CONTAINMENT */ SELECT TP.OBJ#
, TP.TS#
, IP.OBJ#
, IP.TS#
, ' OVERFLOW SEGMENT
AND INDEX PARTITION NOT SELF CONTAINED' FROM INDPART$ IP
, IND$ I
, TAB$ T
, (SELECT BO#
, MIN(TS#) KEEP (DENSE_RANK FIRST ORDER BY PART#) TS#
, MIN(OBJ#) KEEP (DENSE_RANK FIRST ORDER BY PART#) OBJ#
FROM TABPART$ GROUP BY BO#) TP WHERE TP.BO# = T.OBJ#
AND BITAND(T.PROPERTY
, 512)!=0
AND T.BOBJ# = I.BO#
AND IP.BO#= I.OBJ#
AND IP.TS# != TP.TS#