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_TS_OBJECTS

Source

View Text - Preformatted


SELECT t.obj#, t.ts#, l.lobj#, l.ts#,
         'Base table and lob object not fully contained in pluggable set', 1
  FROM   tab$ t, lob$ l
  WHERE  l.ts# != t.ts# AND l.obj# = t.obj# AND BITAND(t.property, 64) = 0
UNION ALL
  -- Report dependencies between objects in different tablespaces that are
  -- enforced through constraints.
  -- Exclude disabled constraints.
  -- Handle IOTs (property 0x40) and partitioned (property 0x20) tables
  --   whose tablespace # is always 0.
  -- Ignore when the second ts number is 2147483647. Stmt from George Eadon:
  --  "local partitioned indexes and reference partitioned tables that are
  --   created without an explicit object-level default tablespace. In these
  --   cases partitions in the object co-locate with the base table partition,
  --   by default."
  --    Local partitioned indexes is already done since about day 1 (8i).  This
  --    change is for ref partitioned child tables.
  SELECT t.obj#,
         decode(BITAND(t.property, 96), 0, t.ts#,
                32, (select po.defts# from partobj$ po where po.obj# = t.obj#),
                64, (select max(i.ts#) from ind$ i where i.bo# = t.obj#)),
         c.obj#,
         decode(BITAND(t2.property, 96), 0, t2.ts#,
                32, (select po.defts# from partobj$ po where po.obj# =t2.obj#),
                64, (select max(i.ts#) from ind$ i where i.bo# = t2.obj#)),
         'Constraint between tables not contained in pluggable set', 2
  FROM   tab$ t2, cdef$ c, tab$ t
  WHERE  c.robj# = t.obj# AND c.obj# = t2.obj# AND
         decode(BITAND(t.property, 96), 0, t.ts#,
                32, (select po.defts# from partobj$ po where po.obj# = t.obj#),
                64, (select max(i.ts#) from ind$ i where i.bo# = t.obj#)) !=
         decode(BITAND(t2.property, 96), 0, t2.ts#,
                32, (select po.defts# from partobj$ po where po.obj# =t2.obj#),
                64, (select max(i.ts#) from ind$ i where i.bo# = t2.obj#)) AND
         decode(BITAND(t2.property, 96), 0, t2.ts#,
                32, (select po.defts# from partobj$ po where po.obj# =t2.obj#),
                64, (select max(i.ts#) from ind$ i where i.bo# = t2.obj#)) !=
                2147483647 AND
         c.enabled IS NOT NULL
UNION ALL
  -- Report tables whose indexes are not in the same tablespace.
  -- Exclude partitioned objects , they are checked for separately.
  -- Exclude indexes on any unsupported TSPITR objects.
  -- Exclude indexes enforcing primary key constraints and unique constraints,
  --   these are checked for separately
  -- Exclude iots
  -- Exclude domain indexes, they are done separately
  SELECT t.obj#, t.ts#, i.obj#, i.ts#,
      'Tables and associated indexes not fully contained in the pluggable set',
         3
  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, 131072) = 0 AND
         BITAND(t.property, 64) = 0 AND i.type# != 9
  MINUS
  -- Excluding indexes enforcing primary key constraints
  -- fix bug 860417  - exclude partitioned objects */
  --     bug 1167617 - exclude indexes enforcing unique key constraints
  SELECT t.obj#, t.ts#, i.obj#, i.ts#,
      'Tables and associated indexes not fully contained in the pluggable set',
         3
  FROM   tab$ t, ind$ i , cdef$ cf
  WHERE  t.obj# = cf.obj# AND i.obj# = cf.enabled AND cf.type# in( 2,3) AND
         t.ts# != i.ts# AND i.bo#=t.obj# AND BITAND(t.property, 32) = 0
UNION ALL
  -- Report any partitioned tables where the default ts is 0
  -- Bug 11682089: exclude global partitioned index enforcing primary key/
  --               unique key constraint on non-partitioned table.
  SELECT t.obj#, t.ts#, i.obj#, i.ts#,
         'Table and Index enforcing primary key/unique key constraint not in same tablespace',
         4
  FROM   tab$ t, ind$ i , cdef$ cf
  WHERE  t.obj# = cf.obj# AND i.obj# = cf.enabled AND cf.type# in (2,3) AND
         t.ts# != i.ts# AND i.bo#=t.obj# AND BITAND(t.property, 64) = 0 AND
         BITAND(t.property, 32) = 0 AND BITAND(i.property, 2) = 0
UNION ALL
  -- Report clusters whose indexes are not in the same tablespace
  SELECT c.obj#, c.ts#, i.obj#, i.ts#,
         'Tables/Clusters and associated indexes not fully contained in the pluggable set',
         5
  FROM   clu$ c, ind$ i
  WHERE  c.obj# = i.bo# AND c.ts# != i.ts#
UNION ALL
  -- Report 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 pluggable set', 6
  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
  -- Report partitioned indexes that are in different tablespaces than any
  -- partition in the table.
  -- 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 pluggable set', 7
  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
  -- Report partitioned tables and non-partitioned index in different
  -- tablespaces
  -- Exclude domain indexes, they are done separately
  SELECT tp.obj#, tp.ts#, i.obj#, i.ts#,
         'Partitioned Objects not fully contained in the pluggable set', 8
  FROM   tabpart$ tp, ind$ i
  WHERE  tp.ts#! = i.ts# AND BITAND(i.property, 2) = 0 AND tp.bo# = i.bo# AND
         i.type# != 9
union all
  -- Report partitioned index and non-partitioned table in different
  -- tablespaces
  -- Exclude domain indexes, they are done separately
  SELECT t.obj#, t.ts#, ip.obj#, ip.ts#,
         'Partitioned Objects not fully contained in the pluggable set', 9
  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 i.type# != 9
UNION ALL
  -- Reoprt objects that are not supported
  --   tab$.property - 0x20000 = AQs
  -- Look at objects that have storage Tables, IOTs, Partitions, Subpartitions
  --
  -- 8.0 compatible AQ with multiple recipients
  SELECT t.obj#, t.ts#, -1, -1 , 'Object not allowed in Pluggable Set', 10
  FROM   sys.dba_queue_Tables q, obj$ o, user$ u, tab$ t
  WHERE  q.recipients = 'MULTIPLE' AND SUBSTR(q.compatible, 1, 3) = '8.0' AND
         q.queue_table = o.name AND q.owner = u.name AND u.user# = o.owner# AND
         o.obj# = t.obj#
UNION ALL
  -- Report any 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 Transportable Set', 15
  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
  -- Report any composite table partitions and index composite partitions that
  -- are not in the same tablespace.
  SELECT v3.obj#, v3.ts#, v4.obj#, v4.ts#,
         'Table subpartition and index subpartition not fully contained in the Transportable Set',
         16
  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
  SELECT lf.fragobj#, lf.ts#, tp.obj#, tp.ts#,
         'Table partition and lob fragment not in Transportable Set', 17
  FROM   lobfrag$ lf, tabpart$ tp
  WHERE  lf.tabfragobj# = tp.obj# AND tp.ts# !=lf.ts#
UNION ALL
  -- Report Subpartitions having lob fragments
  SELECT tsp.obj#, tsp.ts#, lf.fragobj#, lf.ts#,
         'Table Subpartition and lob fragment not fully contained in pluggable set',
         18
 FROM    tabsubpart$ tsp, lobfrag$ lf
  WHERE  tsp.obj# = lf.tabfragobj# AND tsp.ts# != lf.ts#
--UNION ALL
--  -- Report all objects owned by SYS
--  -- NON-Partitioned table
--  SELECT o.obj#, t.ts#, -1, -1,
--         'Sys owned tables not allowed in Transportable Set', 19
--  FROM   tab$ t, obj$ o
--  WHERE  t.obj# = o.obj# AND BITAND(t.property, 32) = 0 AND o.owner# = 0
--UNION ALL
--  -- Partitioned tables
--  SELECT o.obj#, tp.ts#, -1, -1,
--         'Sys owned partitions not allowed in Transportable Set', 20
--  FROM   tabpart$ tp, obj$ o
--  WHERE  tp.obj# = o.obj# AND o.owner# = 0
--UNION ALL
--  -- clusters
--  SELECT o.obj#, c.ts#, -1, -1,
--         'Sys owned clusters not allowed in Transportable Set', 21
--  FROM   clu$ c, obj$ o
--  WHERE  c.obj# = o.obj# AND o.owner# = 0
--UNION ALL
--  -- subpartitions
--  SELECT o.obj#, tsp.ts#, -1, -1,
--         'Sys owned subpartitions not allowed in Transportable Set', 22
--  FROM   tabsubpart$ tsp, obj$ o
--  WHERE  tsp.obj# = o.obj# AND o.owner# = 0
--UNION ALL
--  -- non-partitioned indexes
--  SELECT o.obj#, i.ts#, -1, -1,
--         'Sys owned indexes not allowed in Transportable Set', 23
--  FROM   ind$ i, obj$ o
--  WHERE  i.obj# = o.obj# AND o.owner# = 0 AND BITAND(i.property, 2) =0
--UNION ALL
--  -- Partitioned indexes
--  SELECT o.obj#, ip.ts#, -1, -1,
--         'Sys owned partitioned indexes not allowed in Transportable Set', 24
--  FROM   indpart$ ip, obj$ o
--  WHERE  ip.obj# = o.obj# AND o.owner# = 0
--UNION ALL
--  -- subpartitioned indexes
--  SELECT o.obj#, isp.ts#, -1, -1,
--         'Sys owned subpartitioned indexes not allowed in Transportable Set',
--         25
--  FROM   indsubpart$ isp, obj$ o
--  WHERE  isp.obj# = o.obj# AND o.owner# = 0
--UNION ALL
--  -- lobs
--  SELECT l.lobj#, l.ts#, -1, -1,
--         'Sys owned lobs not allowed in Transportable Set', 26
--  FROM   lob$ l, obj$ o
--  WHERE  l.lobj# = o.obj# AND o.owner# = 0
--UNION ALL
--  -- partitioned lobs
--  SELECT lf.fragobj#, lf.ts#, -1, -1,
--         'Sys owned lob fragments not allowed in Transportable Set', 27
--  FROM   lobfrag$ lf, obj$ o
--  WHERE  lf.fragobj# = o.obj# AND o.owner# = 0
UNION ALL
  -- Report any PL/SQL Functional Indexes
  SELECT i.obj#, i.ts#, -1, -1,
         'PLSQL Functional Indexes not allowed in Transportable Set', 29
  FROM   ind$ i
  WHERE  BITAND(i.property, 2048) = 2048
UNION ALL
  -- Report any iot and overflow segment are not in same tablespace
  -- The following will capture the IOT table.
  -- Bug-6652830: take care of partitioned objects and ignore lob indexes
  SELECT t.obj#,
         decode(BITAND(t.property, 32), 0, t.ts#, 32,
               (select po.defts# from partobj$ po where po.obj# = t.obj#)),
         i.obj#,
         decode(BITAND(i.property, 2), 0, i.ts#, 2,
               (select po.defts# from partobj$ po where po.obj# = i.obj#)),
         'IOT and Overflow segment not self contained', 30
  from   tab$ t, ind$ i
  where  t.bobj# = i.bo# AND i.type# != 8 AND
         BITAND(t.property,512) != 0 AND
         decode(BITAND(t.property, 32), 0, t.ts#, 32,
               (select po.defts# from partobj$ po where po.obj# = t.obj#)) !=
         decode(BITAND(i.property, 2), 0, i.ts#, 2,
               (select po.defts# from partobj$ po where po.obj# = i.obj#))
UNION ALL
  -- Report all default storage for a partitioned object that are outside of
  -- the transportable set.  Logical partitions are being excluded since they
  -- don't occupy storage.
  -- Exclude logical partitions
  -- Ensure that the default partition tablespace for table partitions is self
  -- contained
  SELECT po.obj#, defts#, tp.obj#, tp.ts#,
         'Default tablespace and partition not selfcontained', 33
  FROM   tabpart$ tp, partobj$ po
  WHERE  po.obj# = tp.bo# AND po.defts# != tp.ts# AND tp.block# != 0 AND
         tp.file# !=0
UNION ALL
  -- Default for partitioned object and table subpartition are self contained
  SELECT po.obj#, po.defts#, tcp.obj#, tcp.defts#,
         'Default tablespace and partition not selfcontained', 37
  FROM   tabcompart$ tcp, partobj$ po
  WHERE  tcp.bo# = po.obj# AND tcp.defts# != po.defts#
UNION ALL
  -- Report any default partition tablespace for index partitions not contained
  SELECT po.obj#, defts#, ip.obj#, ip.ts#,
         'Default tablespace and partition not selfcontained', 34
  FROM   ind$ i, indpart$ ip, partobj$ po
  WHERE  po.obj# = ip.bo# AND po.defts# != ip.ts# AND i.obj# = ip.bo# AND
         i.type# != 9
UNION ALL
  -- Report partitioned object and index subpartition default tablespace are
  -- self contained
  SELECT po.obj#, po.defts#, icp.obj#, icp.defts#,
         'Default tablespace and partition not selfcontained', 38
  FROM   indcompart$ icp, partobj$ po
  WHERE  icp.bo# = po.obj# AND icp.defts# != po.defts#
UNION ALL
  -- Report any default partition tablespace for subpartitions not contained
  -- for Tables
  SELECT tcp.obj#, defts#, tsp.obj#, tsp.ts#,
         'Default tablespace and partition not selfcontained', 35
  FROM   tabcompart$ tcp, tabsubpart$ tsp
  WHERE  tcp.obj# = tsp.pobj# AND tcp.defts# != tsp.ts#
UNION ALL
  -- Report any default partition tablespace for subpartitions not contained
  -- for Indexes
  SELECT icp.obj#, defts#, isp.obj#, isp.ts#,
         'Default tablespace and partition not selfcontained', 36
  FROM   indcompart$ icp, indsubpart$ isp
  WHERE  icp.obj# = isp.pobj# AND icp.defts# != isp.ts#
UNION ALL
  -- Report any IOTs where the index partitions are not contained
  SELECT ip1.obj#, ip1.ts#, ip2.obj#, ip2.ts#,
         'IOT partitions not self contained', 39
  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
  -- Report all IOTs, overflow segments and index partitions not 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 and index partition not self contained', 40
  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#
UNION ALL
  -- check iots having lobs
  -- Bug-6652830: exclude partitioned tables (property 0x20) since
  -- partitioned lob objects don't have ts# set while lob fragments do.
  SELECT t.obj#,i.ts#,l.lobj#, l.ts#,
         'Base table and lob object not fully contained in pluggable set', 41
  FROM   tab$ t, lob$ l, ind$ i
  WHERE  BITAND(t.property, 64)! = 0 AND BITAND(t.property, 32) = 0 AND
         l.ts#! = i.ts# AND l.obj# = t.obj# AND
         i.bo# = t.obj#
UNION ALL
  -- Report any join indexes that are not contained. The logging tables of join
  -- indexes are used during a transaction for updating purpose. They are not
  -- relevant for TTS since TTS are made read-only.
  -- Note that this is a one-way dependency.
  SELECT o1.obj#, t1.ts#, o2.obj#, t2.ts#,
         'Tables of the join index are not in the same tablespace', 43
  FROM   tts_obj_view o1, tts_obj_view o2, jijoin$ j, tts_tab_view t1,
         tts_tab_view t2
  WHERE  j.tab1obj# = o1.obj# AND j.tab2obj# = o2.obj# AND
         o1.obj# = t1.obj# AND o2.obj# = t2.obj# AND t1.ts# != t2.ts#
UNION ALL
  -- Report all tables having scoped REF constraints in different tablespaces.
  --   t.property  8 (0x08) -> has REF column
  --   Note that this is a one-way dependency
  SELECT t2.obj#, t2.ts#, o.obj#, t.ts#,
         'based table and its scoped REF object are in different tablespaces',
         44
  FROM   tts_obj_view o, tts_tab_view t, refcon$ c,
         tts_obj_view o2, tts_tab_view t2
  WHERE  o.obj# = t.obj# AND c.obj# = o.obj# AND c.stabid = o2.oid$ AND
         BITAND(c.reftyp, 1) != 0 AND o2.obj#=t2.obj# AND t.ts# != t2.ts# AND
         BITAND(t.property, 8) = 8
UNION ALL
  -- Disallow evolved type data that have not been upgraded.
  SELECT o.obj#, t.ts#, -1, -1,
         'Evolved type data that have not been upgraded are not allowed in a Transportable Set',
         45
  FROM   coltype$ c, obj$ o, tts_tab_view t
  WHERE  o.obj# = c.obj# AND t.obj# = o.obj# AND BITAND(c.flags, 256) != 0
UNION ALL
  -- Tables with encrypted columns not supported
  SELECT t.obj#, t.ts#, -1, -1,
         'Tables with encrypted columns not allowed in Transportable Set', 46
  FROM   tab$ t, tts_obj_view o
  WHERE  t.obj# = o.obj# AND BITAND(t.trigflag, 65536) = 65536
UNION ALL
  -- Tables with parent ref partition tables
  SELECT t1.bo#, t1.ts#, t2.bo#, t2.ts#,
         'Ref partitioned child table included but not parent table', 47
  FROM   tabpart$ t1, tabpart$ t2, cdef$ c
  WHERE  t1.bo# = c.obj# and t2.bo# = c.robj# and t1.part# = t2.part# and
         t1.ts# != t2.ts#
UNION ALL
  -- Partitioned tables in process of online move
  SELECT tp.bo#, tp.ts#, null, null,
         'Partitioned table in progcess of an online move partition', 48
 from sys.tabpart$ tp
 where bitand(tp.flags,4194304)!=0
UNION ALL
  -- Subpartitioned tables in process of online move
  SELECT tcp.bo#, tsp.ts#, null, null,
         'Subpartitioned table in progcess of an online move partition', 49
  FROM   sys.tabcompart$ tcp, sys.tabsubpart$ tsp
  WHERE tcp.obj#=tsp.pobj# and
        bitand(tsp.flags,4194304)!=0
UNION ALL
  -- Index with orphanded partition
  SELECT i.obj#, i.ts#, null, null,
         'Index has an orphaned entries', 50
  FROM   ind$ i
  WHERE  bitand(i.flags,268435456) != 0
UNION ALL
  -- Partitioned Index with orphanded partition
  SELECT unique ip.bo#, ip.ts#, null, null,
         'Partitioned index has an orphaned entries', 51
  FROM indpart$ ip
  where bitand(ip.flags,262144)!=0

View Text - HTML Formatted

SELECT T.OBJ#
, T.TS#
, L.LOBJ#
, L.TS#
, 'BASE TABLE
AND LOB OBJECT NOT FULLY CONTAINED IN PLUGGABLE SET'
, 1
FROM TAB$ T
, LOB$ L
WHERE L.TS# != T.TS#
AND L.OBJ# = T.OBJ#
AND BITAND(T.PROPERTY
, 64) = 0 UNION ALL -- REPORT DEPENDENCIES BETWEEN OBJECTS IN DIFFERENT TABLESPACES THAT ARE -- ENFORCED THROUGH CONSTRAINTS. -- EXCLUDE DISABLED CONSTRAINTS. -- HANDLE IOTS (PROPERTY 0X40)
AND PARTITIONED (PROPERTY 0X20) TABLES -- WHOSE TABLESPACE # IS ALWAYS 0. -- IGNORE WHEN THE SECOND TS NUMBER IS 2147483647. STMT
FROM GEORGE EADON: -- "LOCAL PARTITIONED INDEXES
AND REFERENCE PARTITIONED TABLES THAT ARE -- CREATED WITHOUT AN EXPLICIT OBJECT-LEVEL DEFAULT TABLESPACE. IN THESE -- CASES PARTITIONS IN THE OBJECT CO-LOCATE WITH THE BASE TABLE PARTITION
, -- BY DEFAULT." -- LOCAL PARTITIONED INDEXES IS ALREADY DONE SINCE ABOUT DAY 1 (8I). THIS -- CHANGE IS FOR REF PARTITIONED CHILD TABLES. SELECT T.OBJ#
, DECODE(BITAND(T.PROPERTY
, 96)
, 0
, T.TS#
, 32
, (SELECT PO.DEFTS#
FROM PARTOBJ$ PO
WHERE PO.OBJ# = T.OBJ#)
, 64
, (SELECT MAX(I.TS#)
FROM IND$ I
WHERE I.BO# = T.OBJ#))
, C.OBJ#
, DECODE(BITAND(T2.PROPERTY
, 96)
, 0
, T2.TS#
, 32
, (SELECT PO.DEFTS#
FROM PARTOBJ$ PO
WHERE PO.OBJ# =T2.OBJ#)
, 64
, (SELECT MAX(I.TS#)
FROM IND$ I
WHERE I.BO# = T2.OBJ#))
, 'CONSTRAINT BETWEEN TABLES NOT CONTAINED IN PLUGGABLE SET'
, 2
FROM TAB$ T2
, CDEF$ C
, TAB$ T
WHERE C.ROBJ# = T.OBJ#
AND C.OBJ# = T2.OBJ# AND DECODE(BITAND(T.PROPERTY
, 96)
, 0
, T.TS#
, 32
, (SELECT PO.DEFTS#
FROM PARTOBJ$ PO
WHERE PO.OBJ# = T.OBJ#)
, 64
, (SELECT MAX(I.TS#)
FROM IND$ I
WHERE I.BO# = T.OBJ#)) != DECODE(BITAND(T2.PROPERTY
, 96)
, 0
, T2.TS#
, 32
, (SELECT PO.DEFTS#
FROM PARTOBJ$ PO
WHERE PO.OBJ# =T2.OBJ#)
, 64
, (SELECT MAX(I.TS#)
FROM IND$ I
WHERE I.BO# = T2.OBJ#)) AND DECODE(BITAND(T2.PROPERTY
, 96)
, 0
, T2.TS#
, 32
, (SELECT PO.DEFTS#
FROM PARTOBJ$ PO
WHERE PO.OBJ# =T2.OBJ#)
, 64
, (SELECT MAX(I.TS#)
FROM IND$ I
WHERE I.BO# = T2.OBJ#)) != 2147483647 AND C.ENABLED IS NOT NULL UNION ALL -- REPORT TABLES WHOSE INDEXES ARE NOT IN THE SAME TABLESPACE. -- EXCLUDE PARTITIONED OBJECTS
, THEY ARE CHECKED FOR SEPARATELY. -- EXCLUDE INDEXES ON ANY UNSUPPORTED TSPITR OBJECTS. -- EXCLUDE INDEXES ENFORCING PRIMARY KEY CONSTRAINTS
AND UNIQUE CONSTRAINTS
, -- THESE ARE CHECKED FOR SEPARATELY -- EXCLUDE IOTS -- EXCLUDE DOMAIN INDEXES
, THEY ARE DONE SEPARATELY SELECT T.OBJ#
, T.TS#
, I.OBJ#
, I.TS#
, 'TABLES
AND ASSOCIATED INDEXES NOT FULLY CONTAINED IN THE PLUGGABLE SET'
, 3
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
, 131072) = 0 AND BITAND(T.PROPERTY
, 64) = 0
AND I.TYPE# != 9 MINUS -- EXCLUDING INDEXES ENFORCING PRIMARY KEY CONSTRAINTS -- FIX BUG 860417 - EXCLUDE PARTITIONED OBJECTS */ -- BUG 1167617 - EXCLUDE INDEXES ENFORCING UNIQUE KEY CONSTRAINTS SELECT T.OBJ#
, T.TS#
, I.OBJ#
, I.TS#
, 'TABLES
AND ASSOCIATED INDEXES NOT FULLY CONTAINED IN THE PLUGGABLE SET'
, 3
FROM TAB$ T
, IND$ I
, CDEF$ CF
WHERE T.OBJ# = CF.OBJ#
AND I.OBJ# = CF.ENABLED
AND CF.TYPE# IN( 2
, 3) AND T.TS# != I.TS#
AND I.BO#=T.OBJ#
AND BITAND(T.PROPERTY
, 32) = 0 UNION ALL -- REPORT ANY PARTITIONED TABLES
WHERE THE DEFAULT TS IS 0 -- BUG 11682089: EXCLUDE GLOBAL PARTITIONED INDEX ENFORCING PRIMARY KEY/ -- UNIQUE KEY CONSTRAINT ON NON-PARTITIONED TABLE. SELECT T.OBJ#
, T.TS#
, I.OBJ#
, I.TS#
, 'TABLE
AND INDEX ENFORCING PRIMARY KEY/UNIQUE KEY CONSTRAINT NOT IN SAME TABLESPACE'
, 4
FROM TAB$ T
, IND$ I
, CDEF$ CF
WHERE T.OBJ# = CF.OBJ#
AND I.OBJ# = CF.ENABLED
AND CF.TYPE# IN (2
, 3) AND T.TS# != I.TS#
AND I.BO#=T.OBJ#
AND BITAND(T.PROPERTY
, 64) = 0 AND BITAND(T.PROPERTY
, 32) = 0
AND BITAND(I.PROPERTY
, 2) = 0 UNION ALL -- REPORT CLUSTERS WHOSE INDEXES ARE NOT IN THE SAME TABLESPACE SELECT C.OBJ#
, C.TS#
, I.OBJ#
, I.TS#
, 'TABLES/CLUSTERS
AND ASSOCIATED INDEXES NOT FULLY CONTAINED IN THE PLUGGABLE SET'
, 5
FROM CLU$ C
, IND$ I
WHERE C.OBJ# = I.BO#
AND C.TS# != I.TS# UNION ALL -- REPORT 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 PLUGGABLE SET'
, 6
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 -- REPORT PARTITIONED INDEXES THAT ARE IN DIFFERENT TABLESPACES THAN ANY -- PARTITION IN THE TABLE. -- 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 PLUGGABLE SET'
, 7
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 -- REPORT PARTITIONED TABLES
AND NON-PARTITIONED INDEX IN DIFFERENT -- TABLESPACES -- EXCLUDE DOMAIN INDEXES
, THEY ARE DONE SEPARATELY SELECT TP.OBJ#
, TP.TS#
, I.OBJ#
, I.TS#
, 'PARTITIONED OBJECTS NOT FULLY CONTAINED IN THE PLUGGABLE SET'
, 8
FROM TABPART$ TP
, IND$ I
WHERE TP.TS#! = I.TS#
AND BITAND(I.PROPERTY
, 2) = 0
AND TP.BO# = I.BO# AND I.TYPE# != 9 UNION ALL -- REPORT PARTITIONED INDEX
AND NON-PARTITIONED TABLE IN DIFFERENT -- TABLESPACES -- EXCLUDE DOMAIN INDEXES
, THEY ARE DONE SEPARATELY SELECT T.OBJ#
, T.TS#
, IP.OBJ#
, IP.TS#
, 'PARTITIONED OBJECTS NOT FULLY CONTAINED IN THE PLUGGABLE SET'
, 9
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 I.TYPE# != 9 UNION ALL -- REOPRT OBJECTS THAT ARE NOT SUPPORTED -- TAB$.PROPERTY - 0X20000 = AQS -- LOOK AT OBJECTS THAT HAVE STORAGE TABLES
, IOTS
, PARTITIONS
, SUBPARTITIONS -- -- 8.0 COMPATIBLE AQ WITH MULTIPLE RECIPIENTS SELECT T.OBJ#
, T.TS#
, -1
, -1
, 'OBJECT NOT ALLOWED IN PLUGGABLE SET'
, 10
FROM SYS.DBA_QUEUE_TABLES Q
, OBJ$ O
, USER$ U
, TAB$ T
WHERE Q.RECIPIENTS = 'MULTIPLE'
AND SUBSTR(Q.COMPATIBLE
, 1
, 3) = '8.0' AND Q.QUEUE_TABLE = O.NAME
AND Q.OWNER = U.NAME
AND U.USER# = O.OWNER# AND O.OBJ# = T.OBJ# UNION ALL -- REPORT ANY 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 TRANSPORTABLE SET'
, 15
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 -- REPORT ANY COMPOSITE TABLE PARTITIONS
AND INDEX COMPOSITE PARTITIONS THAT -- ARE NOT IN THE SAME TABLESPACE. SELECT V3.OBJ#
, V3.TS#
, V4.OBJ#
, V4.TS#
, 'TABLE SUBPARTITION
AND INDEX SUBPARTITION NOT FULLY CONTAINED IN THE TRANSPORTABLE SET'
, 16
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 SELECT LF.FRAGOBJ#
, LF.TS#
, TP.OBJ#
, TP.TS#
, 'TABLE PARTITION
AND LOB FRAGMENT NOT IN TRANSPORTABLE SET'
, 17
FROM LOBFRAG$ LF
, TABPART$ TP
WHERE LF.TABFRAGOBJ# = TP.OBJ#
AND TP.TS# !=LF.TS# UNION ALL -- REPORT SUBPARTITIONS HAVING LOB FRAGMENTS SELECT TSP.OBJ#
, TSP.TS#
, LF.FRAGOBJ#
, LF.TS#
, 'TABLE SUBPARTITION
AND LOB FRAGMENT NOT FULLY CONTAINED IN PLUGGABLE SET'
, 18
FROM TABSUBPART$ TSP
, LOBFRAG$ LF
WHERE TSP.OBJ# = LF.TABFRAGOBJ#
AND TSP.TS# != LF.TS# --UNION ALL -- -- REPORT ALL OBJECTS OWNED BY SYS -- -- NON-PARTITIONED TABLE -- SELECT O.OBJ#
, T.TS#
, -1
, -1
, -- 'SYS OWNED TABLES NOT ALLOWED IN TRANSPORTABLE SET'
, 19 --
FROM TAB$ T
, OBJ$ O --
WHERE T.OBJ# = O.OBJ#
AND BITAND(T.PROPERTY
, 32) = 0
AND O.OWNER# = 0 --UNION ALL -- -- PARTITIONED TABLES -- SELECT O.OBJ#
, TP.TS#
, -1
, -1
, -- 'SYS OWNED PARTITIONS NOT ALLOWED IN TRANSPORTABLE SET'
, 20 --
FROM TABPART$ TP
, OBJ$ O --
WHERE TP.OBJ# = O.OBJ#
AND O.OWNER# = 0 --UNION ALL -- -- CLUSTERS -- SELECT O.OBJ#
, C.TS#
, -1
, -1
, -- 'SYS OWNED CLUSTERS NOT ALLOWED IN TRANSPORTABLE SET'
, 21 --
FROM CLU$ C
, OBJ$ O --
WHERE C.OBJ# = O.OBJ#
AND O.OWNER# = 0 --UNION ALL -- -- SUBPARTITIONS -- SELECT O.OBJ#
, TSP.TS#
, -1
, -1
, -- 'SYS OWNED SUBPARTITIONS NOT ALLOWED IN TRANSPORTABLE SET'
, 22 --
FROM TABSUBPART$ TSP
, OBJ$ O --
WHERE TSP.OBJ# = O.OBJ#
AND O.OWNER# = 0 --UNION ALL -- -- NON-PARTITIONED INDEXES -- SELECT O.OBJ#
, I.TS#
, -1
, -1
, -- 'SYS OWNED INDEXES NOT ALLOWED IN TRANSPORTABLE SET'
, 23 --
FROM IND$ I
, OBJ$ O --
WHERE I.OBJ# = O.OBJ#
AND O.OWNER# = 0
AND BITAND(I.PROPERTY
, 2) =0 --UNION ALL -- -- PARTITIONED INDEXES -- SELECT O.OBJ#
, IP.TS#
, -1
, -1
, -- 'SYS OWNED PARTITIONED INDEXES NOT ALLOWED IN TRANSPORTABLE SET'
, 24 --
FROM INDPART$ IP
, OBJ$ O --
WHERE IP.OBJ# = O.OBJ#
AND O.OWNER# = 0 --UNION ALL -- -- SUBPARTITIONED INDEXES -- SELECT O.OBJ#
, ISP.TS#
, -1
, -1
, -- 'SYS OWNED SUBPARTITIONED INDEXES NOT ALLOWED IN TRANSPORTABLE SET'
, -- 25 --
FROM INDSUBPART$ ISP
, OBJ$ O --
WHERE ISP.OBJ# = O.OBJ#
AND O.OWNER# = 0 --UNION ALL -- -- LOBS -- SELECT L.LOBJ#
, L.TS#
, -1
, -1
, -- 'SYS OWNED LOBS NOT ALLOWED IN TRANSPORTABLE SET'
, 26 --
FROM LOB$ L
, OBJ$ O --
WHERE L.LOBJ# = O.OBJ#
AND O.OWNER# = 0 --UNION ALL -- -- PARTITIONED LOBS -- SELECT LF.FRAGOBJ#
, LF.TS#
, -1
, -1
, -- 'SYS OWNED LOB FRAGMENTS NOT ALLOWED IN TRANSPORTABLE SET'
, 27 --
FROM LOBFRAG$ LF
, OBJ$ O --
WHERE LF.FRAGOBJ# = O.OBJ#
AND O.OWNER# = 0 UNION ALL -- REPORT ANY PL/SQL FUNCTIONAL INDEXES SELECT I.OBJ#
, I.TS#
, -1
, -1
, 'PLSQL FUNCTIONAL INDEXES NOT ALLOWED IN TRANSPORTABLE SET'
, 29
FROM IND$ I
WHERE BITAND(I.PROPERTY
, 2048) = 2048 UNION ALL -- REPORT ANY IOT
AND OVERFLOW SEGMENT ARE NOT IN SAME TABLESPACE -- THE FOLLOWING WILL CAPTURE THE IOT TABLE. -- BUG-6652830: TAKE CARE OF PARTITIONED OBJECTS
AND IGNORE LOB INDEXES SELECT T.OBJ#
, DECODE(BITAND(T.PROPERTY
, 32)
, 0
, T.TS#
, 32
, (SELECT PO.DEFTS#
FROM PARTOBJ$ PO
WHERE PO.OBJ# = T.OBJ#))
, I.OBJ#
, DECODE(BITAND(I.PROPERTY
, 2)
, 0
, I.TS#
, 2
, (SELECT PO.DEFTS#
FROM PARTOBJ$ PO
WHERE PO.OBJ# = I.OBJ#))
, 'IOT
AND OVERFLOW SEGMENT NOT SELF CONTAINED'
, 30
FROM TAB$ T
, IND$ I
WHERE T.BOBJ# = I.BO#
AND I.TYPE# != 8 AND BITAND(T.PROPERTY
, 512) != 0 AND DECODE(BITAND(T.PROPERTY
, 32)
, 0
, T.TS#
, 32
, (SELECT PO.DEFTS#
FROM PARTOBJ$ PO
WHERE PO.OBJ# = T.OBJ#)) != DECODE(BITAND(I.PROPERTY
, 2)
, 0
, I.TS#
, 2
, (SELECT PO.DEFTS#
FROM PARTOBJ$ PO
WHERE PO.OBJ# = I.OBJ#)) UNION ALL -- REPORT ALL DEFAULT STORAGE FOR A PARTITIONED OBJECT THAT ARE OUTSIDE OF -- THE TRANSPORTABLE SET. LOGICAL PARTITIONS ARE BEING EXCLUDED SINCE THEY -- DON'T OCCUPY STORAGE. -- EXCLUDE LOGICAL PARTITIONS -- ENSURE THAT THE DEFAULT PARTITION TABLESPACE FOR TABLE PARTITIONS IS SELF -- CONTAINED SELECT PO.OBJ#
, DEFTS#
, TP.OBJ#
, TP.TS#
, 'DEFAULT TABLESPACE
AND PARTITION NOT SELFCONTAINED'
, 33
FROM TABPART$ TP
, PARTOBJ$ PO
WHERE PO.OBJ# = TP.BO#
AND PO.DEFTS# != TP.TS#
AND TP.BLOCK# != 0 AND TP.FILE# !=0 UNION ALL -- DEFAULT FOR PARTITIONED OBJECT
AND TABLE SUBPARTITION ARE SELF CONTAINED SELECT PO.OBJ#
, PO.DEFTS#
, TCP.OBJ#
, TCP.DEFTS#
, 'DEFAULT TABLESPACE
AND PARTITION NOT SELFCONTAINED'
, 37
FROM TABCOMPART$ TCP
, PARTOBJ$ PO
WHERE TCP.BO# = PO.OBJ#
AND TCP.DEFTS# != PO.DEFTS# UNION ALL -- REPORT ANY DEFAULT PARTITION TABLESPACE FOR INDEX PARTITIONS NOT CONTAINED SELECT PO.OBJ#
, DEFTS#
, IP.OBJ#
, IP.TS#
, 'DEFAULT TABLESPACE
AND PARTITION NOT SELFCONTAINED'
, 34
FROM IND$ I
, INDPART$ IP
, PARTOBJ$ PO
WHERE PO.OBJ# = IP.BO#
AND PO.DEFTS# != IP.TS#
AND I.OBJ# = IP.BO# AND I.TYPE# != 9 UNION ALL -- REPORT PARTITIONED OBJECT
AND INDEX SUBPARTITION DEFAULT TABLESPACE ARE -- SELF CONTAINED SELECT PO.OBJ#
, PO.DEFTS#
, ICP.OBJ#
, ICP.DEFTS#
, 'DEFAULT TABLESPACE
AND PARTITION NOT SELFCONTAINED'
, 38
FROM INDCOMPART$ ICP
, PARTOBJ$ PO
WHERE ICP.BO# = PO.OBJ#
AND ICP.DEFTS# != PO.DEFTS# UNION ALL -- REPORT ANY DEFAULT PARTITION TABLESPACE FOR SUBPARTITIONS NOT CONTAINED -- FOR TABLES SELECT TCP.OBJ#
, DEFTS#
, TSP.OBJ#
, TSP.TS#
, 'DEFAULT TABLESPACE
AND PARTITION NOT SELFCONTAINED'
, 35
FROM TABCOMPART$ TCP
, TABSUBPART$ TSP
WHERE TCP.OBJ# = TSP.POBJ#
AND TCP.DEFTS# != TSP.TS# UNION ALL -- REPORT ANY DEFAULT PARTITION TABLESPACE FOR SUBPARTITIONS NOT CONTAINED -- FOR INDEXES SELECT ICP.OBJ#
, DEFTS#
, ISP.OBJ#
, ISP.TS#
, 'DEFAULT TABLESPACE
AND PARTITION NOT SELFCONTAINED'
, 36
FROM INDCOMPART$ ICP
, INDSUBPART$ ISP
WHERE ICP.OBJ# = ISP.POBJ#
AND ICP.DEFTS# != ISP.TS# UNION ALL -- REPORT ANY IOTS
WHERE THE INDEX PARTITIONS ARE NOT CONTAINED SELECT IP1.OBJ#
, IP1.TS#
, IP2.OBJ#
, IP2.TS#
, 'IOT PARTITIONS NOT SELF CONTAINED'
, 39
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 -- REPORT ALL IOTS
, OVERFLOW SEGMENTS
AND INDEX PARTITIONS NOT 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
AND INDEX PARTITION NOT SELF CONTAINED'
, 40
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# UNION ALL -- CHECK IOTS HAVING LOBS -- BUG-6652830: EXCLUDE PARTITIONED TABLES (PROPERTY 0X20) SINCE -- PARTITIONED LOB OBJECTS DON'T HAVE TS# SET WHILE LOB FRAGMENTS DO. SELECT T.OBJ#
, I.TS#
, L.LOBJ#
, L.TS#
, 'BASE TABLE
AND LOB OBJECT NOT FULLY CONTAINED IN PLUGGABLE SET'
, 41
FROM TAB$ T
, LOB$ L
, IND$ I
WHERE BITAND(T.PROPERTY
, 64)! = 0
AND BITAND(T.PROPERTY
, 32) = 0 AND L.TS#! = I.TS#
AND L.OBJ# = T.OBJ# AND I.BO# = T.OBJ# UNION ALL -- REPORT ANY JOIN INDEXES THAT ARE NOT CONTAINED. THE LOGGING TABLES OF JOIN -- INDEXES ARE USED DURING A TRANSACTION FOR UPDATING PURPOSE. THEY ARE NOT -- RELEVANT FOR TTS SINCE TTS ARE MADE READ-ONLY. -- NOTE THAT THIS IS A ONE-WAY DEPENDENCY. SELECT O1.OBJ#
, T1.TS#
, O2.OBJ#
, T2.TS#
, 'TABLES OF THE JOIN INDEX ARE NOT IN THE SAME TABLESPACE'
, 43
FROM TTS_OBJ_VIEW O1
, TTS_OBJ_VIEW O2
, JIJOIN$ J
, TTS_TAB_VIEW T1
, TTS_TAB_VIEW T2
WHERE J.TAB1OBJ# = O1.OBJ#
AND J.TAB2OBJ# = O2.OBJ# AND O1.OBJ# = T1.OBJ#
AND O2.OBJ# = T2.OBJ#
AND T1.TS# != T2.TS# UNION ALL -- REPORT ALL TABLES HAVING SCOPED REF CONSTRAINTS IN DIFFERENT TABLESPACES. -- T.PROPERTY 8 (0X08) -> HAS REF COLUMN -- NOTE THAT THIS IS A ONE-WAY DEPENDENCY SELECT T2.OBJ#
, T2.TS#
, O.OBJ#
, T.TS#
, 'BASED TABLE
AND ITS SCOPED REF OBJECT ARE IN DIFFERENT TABLESPACES'
, 44
FROM TTS_OBJ_VIEW O
, TTS_TAB_VIEW T
, REFCON$ C
, TTS_OBJ_VIEW O2
, TTS_TAB_VIEW T2
WHERE O.OBJ# = T.OBJ#
AND C.OBJ# = O.OBJ#
AND C.STABID = O2.OID$ AND BITAND(C.REFTYP
, 1) != 0
AND O2.OBJ#=T2.OBJ#
AND T.TS# != T2.TS# AND BITAND(T.PROPERTY
, 8) = 8 UNION ALL -- DISALLOW EVOLVED TYPE DATA THAT HAVE NOT BEEN UPGRADED. SELECT O.OBJ#
, T.TS#
, -1
, -1
, 'EVOLVED TYPE DATA THAT HAVE NOT BEEN UPGRADED ARE NOT ALLOWED IN A TRANSPORTABLE SET'
, 45
FROM COLTYPE$ C
, OBJ$ O
, TTS_TAB_VIEW T
WHERE O.OBJ# = C.OBJ#
AND T.OBJ# = O.OBJ#
AND BITAND(C.FLAGS
, 256) != 0 UNION ALL -- TABLES WITH ENCRYPTED COLUMNS NOT SUPPORTED SELECT T.OBJ#
, T.TS#
, -1
, -1
, 'TABLES WITH ENCRYPTED COLUMNS NOT ALLOWED IN TRANSPORTABLE SET'
, 46
FROM TAB$ T
, TTS_OBJ_VIEW O
WHERE T.OBJ# = O.OBJ#
AND BITAND(T.TRIGFLAG
, 65536) = 65536 UNION ALL -- TABLES WITH PARENT REF PARTITION TABLES SELECT T1.BO#
, T1.TS#
, T2.BO#
, T2.TS#
, 'REF PARTITIONED CHILD TABLE INCLUDED BUT NOT PARENT TABLE'
, 47
FROM TABPART$ T1
, TABPART$ T2
, CDEF$ C
WHERE T1.BO# = C.OBJ#
AND T2.BO# = C.ROBJ#
AND T1.PART# = T2.PART# AND T1.TS# != T2.TS# UNION ALL -- PARTITIONED TABLES IN PROCESS OF ONLINE MOVE SELECT TP.BO#
, TP.TS#
, NULL
, NULL
, 'PARTITIONED TABLE IN PROGCESS OF AN ONLINE MOVE PARTITION'
, 48
FROM SYS.TABPART$ TP
WHERE BITAND(TP.FLAGS
, 4194304)!=0 UNION ALL -- SUBPARTITIONED TABLES IN PROCESS OF ONLINE MOVE SELECT TCP.BO#
, TSP.TS#
, NULL
, NULL
, 'SUBPARTITIONED TABLE IN PROGCESS OF AN ONLINE MOVE PARTITION'
, 49
FROM SYS.TABCOMPART$ TCP
, SYS.TABSUBPART$ TSP
WHERE TCP.OBJ#=TSP.POBJ# AND BITAND(TSP.FLAGS
, 4194304)!=0 UNION ALL -- INDEX WITH ORPHANDED PARTITION SELECT I.OBJ#
, I.TS#
, NULL
, NULL
, 'INDEX HAS AN ORPHANED ENTRIES'
, 50
FROM IND$ I
WHERE BITAND(I.FLAGS
, 268435456) != 0 UNION ALL -- PARTITIONED INDEX WITH ORPHANDED PARTITION SELECT UNIQUE IP.BO#
, IP.TS#
, NULL
, NULL
, 'PARTITIONED INDEX HAS AN ORPHANED ENTRIES'
, 51
FROM INDPART$ IP
WHERE BITAND(IP.FLAGS
, 262144)!=0