Rem
Rem NAME
Rem   CATPLUG.SQL
Rem FUNCTION
Rem   Pluggable Tablespace check views
Rem NOTES
Rem   This script must be run while connected as SYS or INTERNAL
Rem    MODIFIED   (MM/DD/YY)
Rem     rshaikh   11/10/99 -  fix for sqlplus
Rem     apareek   08/05/99 -  block REF with referential cons- bug895775
Rem     apareek   07/19/99 -  fix bug 860417
Rem     mjungerm  06/15/99 -  add java shared data object type
Rem     apareek   06/16/99 -  ignore dropped undo segs
Rem     apareek   03/25/99 -  iots with lobs should go against ind$
Rem     apareek   02/22/99 -  bug824907 - partitioned iots
Rem     apareek   03/11/99 -  go against ind$ for iots
Rem     jwlee     01/06/99 -  Allow AQ to be transported
Rem     apareek   12/03/98 -  fix blanks for sqlplus
Rem     apareek   10/20/98 -  block functional indexes
Rem     apareek   10/12/98 -  add check for default tablespace
Rem     apareek   09/01/98 -  check for undo segs
Rem     apareek   08/25/98 -  IOT and overflow segment self contained
Rem     apareek   08/24/98 -  foreign key constraint is only a 1 way violation
Rem     apareek   08/20/98 -  allow VARRAY, NESTED TABLES, IOTS
Rem     apareek   08/06/98 -  disallow SYS owned objects
Rem     apareek   08/06/98 -  capture secondary objects, domain indexes
Rem     apareek   08/05/98 -  allow FILE columns
Rem     apareek   07/31/98 -  fix bug 707999
Rem	apareek   07/31/98 -  add checks for subpartitions, partitioned lobs
Rem     ncramesh  08/04/98 -  change for sqlplus
Rem     apareek   06/24/98 -  TS_PITR_INFO -> TS_PLUG_INFO
Rem     apareek   05/19/98 -  Add mesg_id column
Rem     jwlee     05/18/98 -  STRADDLING_RS_OBJECTS -> STRADDLING_TS_OBJECTS
Rem     jwlee     05/03/98 -  pluggable_set_check -> transportable_set_check
Rem     apareek   04/27/98 -  creation
Rem     apareek   04/06/98 -  Creation
Rem
Rem
Rem View Name   : PLUGGABLE_SET_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 for Pluggable check violation
Rem
Rem
Rem
Rem Comments on View : PLUGGABLE_SET_CHECK
Rem ==========================================
Rem
Rem
Rem
Rem To transport a tablespace in Oracle Version 8.1 
Rem at the time of export there can be no references going out of the 
Rem the Pluggable Set(PS) i.e the set of non-system tablespaces to be
Rem transported.  This view captures any  references not fully contained in
Rem the pluggable set.

Rem In addition, it checks for presence of objects in the pluggable 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	- Replication master tables are in the pluggable set
Rem	- Snapshot tables are in the pluggable set            (I)
Rem	- Snapshot logs are in the pluggable set              (I)
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
Rem       PS
Rem	- includes, straddling table partitions and straddling table and index
Rem	  partition
Rem	- Tables that have a parent-child relationship via referential 
Rem       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 ==============================================================================


create or replace view STRADDLING_TS_OBJECTS 
	(OBJECT1,TS1,OBJECT2,TS2,REASON_ID,MESG_ID) 
as
/* check whether base table and lob segment are in different tablespaces */
/* Exclude iots */
(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
/* check iots having lobs */
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 l.ts#!=i.ts# 
  and l.obj#=t.obj#
  and i.bo# = t.obj#
union all
/* check that iot and overflow segment are in same tablespace */
/* the following will capture the IOT table */
select t.obj#, t.ts#, i.obj#, i.ts#, 'IOT and Overflow segment not self contained',30
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 pluggable set',2
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 */
/* Exclude iots */
select t.obj# object1, t.ts# ts1, i.obj# object2, i.ts# ts2, '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, 4096) = 0
  and bitand(t.property, 131072)=0
  and bitand(t.property,64)=0
minus  /* indexes enforcing primary key constraints */
/* fix bug 860417 - exclude partitioned objects */
select t.obj# object1, t.ts# ts1, i.obj# object2, i.ts# ts2, '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#=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
/* Capture indexes enforcing primary key constraints,  include internally generated snapshot/indexes */ 
/* Exclude IOTs */
/* 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',4
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
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 pluggable set',5
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 pluggable set',6
from tabpart$ tp, tabpart$ tp1
where tp1.part# =1
  and tp1.bo# = tp.bo#
  and tp1.ts# != tp.ts#
  and tp1.file# !=0
  and tp1.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 pluggable set',7
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#
  and tp1.file# !=0 
  and tp1.block# !=0
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 pluggable set',8
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 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#
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 Transportable Set',15
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 Transportable Set',16
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
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
/* 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
/* Objects that are not supported 
	tab$.property
	- 0x1000	= primary key based OID$ column
	- 0x20000 	= AQs  
        col$.property
   Bug 895775  - 0x00080000 =  524288 = ref column (present as oid col)
*/
/* Extract all objects that have the above violations
   Need to hit all objects that have storage
   Tables, IOTs, Partitions, Subpartitions 
*/
select obj#, ts#, -1, -1 , 'Object not allowed in Pluggable Set',10
from tab$ t
where bitand(t.property ,4096) != 0  
  and bitand(t.property,64)=0 
  and bitand(t.property,32)=0
union all
select i.bo#, i.ts# , -1, -1,'Object not allowed in Pluggable Set',10
from tab$ t, ind$ i
where t.obj# = i.bo# 
  and bitand(t.property, 4096)!=0
  and bitand(t.property, 64)!=0
union all
select tp.bo#,tp.ts#,-1,-1, 'Object not allowed in Pluggable Set',10
from tabpart$ tp, tab$ t
where t.obj# = tp.bo#
  and bitand(t.property, 4096)!=0
union all
/* Capture subpartitioned tables that have REFs having ref constraints */
select tcp.bo#,tsp.ts#, -1, -1, 'Object not allowed in Pluggable Set',10
from tab$ t, tabcompart$ tcp, tabsubpart$ tsp
where tsp.pobj# = tcp.obj#
  and tcp.bo# = t.obj#
  and bitand(t.property, 4096)!=0
union all
/* 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
/* 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',42
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',42
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',42
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',42
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 pluggable set',11
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 pluggable set',12
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 pluggable set */
select distinct(t.obj#),t.ts#, -1, -1 ,'Master table used for snapshots/replication not allowed in pluggable set',13
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 pluggable set',14 
from mlog$ m, tab$ t, obj$ o
where m.log=o.name 
  and o.obj#=t.obj#
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 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
/* Capture partitioned tables owned by SYS */
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
/* Capture clusters owned by SYS */
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
/* Capture subpartitions owned by SYS */
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
/* Capture non-partitioned indexes owned by SYS */
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
/* Capture partitioned indexes owned by SYS */
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
/* Capture subpartitioned indexes owned by SYS */
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
/* Capture SYS owned 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
/* Capture 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
/* Secondary Objects not allowed in the Transportable Set */
union all
select o.obj#, t.ts#,-1,-1, 'Secondary Objects not allowed in Transportable Set',28
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 allowed in Transportable Set',29
from ind$ i
where i.type# = 9
   or i.property = 16
union all
/* the following cases ensure that default storage for a partitioned object
   is also part of the transportable set.  If not, then the pseudo create
   at the target will fail if the default tablespace doesn't exist 
   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
/* Ensure that the default partition  tablespace for index partitions is self contained */
select po.obj#, defts#, ip.obj#, ip.ts#, 'Default tablespace and partition not selfcontained',34
from indpart$ ip, partobj$ po
where po.obj# = ip.bo# 
  and po.defts# != ip.ts#
union all
/* Ensure that the default partition tablespace for subpartitions is self 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
/* Ensure that the default partition tablespace for subpartitions is self 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
/* 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
/* Default for partitioned object  and index subpartition 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
/* 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', 39 
from indpart$ ip1, indpart$ ip2, ind$ i, tab$ t
where ip1.part# =1 
and ip1.bo#= i.obj# 
and ip1.ts# != ip2.ts#
and ip1.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 and index partition not self contained',40
from   indpart$ ip, tabpart$ tp, ind$ i, tab$ t
where  tp.part#=1
  and  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#;

grant select on STRADDLING_TS_OBJECTS to SELECT_CATALOG_ROLE;


Rem =============================================================================
Rem                                                                             #
Rem      VIEW NAME	TS_PLUG_INFO			                        #
Rem                                                                             #
Rem =============================================================================

create or replace view TS_PLUG_INFO 
	(OBJ1_OWNER,OBJ1_NAME,OBJ1_SUBNAME,OBJ1_TYPE,TS1_NAME,OBJ2_NAME,OBJ2_SUBNAME,OBJ2_TYPE,OBJ2_OWNER,TS2_NAME,CONSTRAINT_NO,REASON,MESG_ID)
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', 34, 'TABLE SUBPARTITION', 
                          40, 'LOB', 56, 'JAVA DATA', '         '),
	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', 34,'TABLE SUBPARTITION', 
                          40, 'LOB', 56, 'JAVA DATA', '         '),
	o2.owner#,ts2.name,cf.con#,s.reason_id,mesg_id
from straddling_ts_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 undo segs in transportable set */
select 'SYS', u.name, NULL, 'ROLLBACK SEGMENT', ts.name,NULL , NULL, NULL,-1, NULL,-1,'Rollback Segment not allowed in transportable set', 32 
from   undo$ u, ts$ ts
where  u.ts# = ts.ts# 
  and  ts.ts# != 0
  and  u.status$ != 1;


grant select on TS_PLUG_INFO to SELECT_CATALOG_ROLE;

Rem ===========================================================================
Rem                                                                           #
Rem	VIEW NAME	UNI_PLUGGABLE_SET_CHECK                               #
Rem      								      #
Rem ===========================================================================



create or replace view UNI_PLUGGABLE_SET_CHECK
	(OBJ1_OWNER,OBJ1_NAME,OBJ1_SUBNAME,OBJ1_TYPE,TS1_NAME,
         OBJ2_NAME,OBJ2_SUBNAME,OBJ2_TYPE,OBJ2_OWNER,TS2_NAME,
         CONSTRAINT_NAME,REASON,MESG_ID)
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,mesg_id
   from  ts_plug_info t, user$ u, con$ c
   where u.user#(+)=t.obj2_owner
   and   c.con#(+)=t.constraint_no ;



Rem ===========================================================================
Rem                                                                           #
Rem	VIEW NAME	PLUGGABLE_SET_CHECK                                   #
Rem      								      #
Rem ===========================================================================


Rem Create the main view as a self union of UNI_PLUGGABLE_SET_CHECK so that we
Rem can capture dependencies in either direction

create or replace view PLUGGABLE_SET_CHECK
	(OBJ1_OWNER,OBJ1_NAME,OBJ1_SUBNAME,OBJ1_TYPE,TS1_NAME,
         OBJ2_NAME,OBJ2_SUBNAME,OBJ2_TYPE,OBJ2_OWNER,TS2_NAME,
         CONSTRAINT_NAME,REASON,MESG_ID)
as
    select * from UNI_PLUGGABLE_SET_CHECK
union all
    select OBJ2_OWNER,OBJ2_NAME,OBJ2_SUBNAME,OBJ2_TYPE,TS2_NAME,
           OBJ1_NAME,OBJ1_SUBNAME,OBJ1_TYPE,OBJ1_OWNER,TS1_NAME,
           CONSTRAINT_NAME,REASON,MESG_ID
    from  UNI_PLUGGABLE_SET_CHECK
    where obj1_type in ('TABLE PARTITION','LOB','TABLE',
                        'TABLE SUBPARTITION') and  
          obj2_type in ('TABLE PARTITION','LOB','TABLE',
                        'TABLE SUBPARTITION') 
      and mesg_id not in (2) or  mesg_id in (30);

/* Note that we skip 2, 30, 31 since 2 is a one way dependency. */
/* And 30,31 are already included both ways for iot and overflow segments */

grant select on PLUGGABLE_SET_CHECK to SELECT_CATALOG_ROLE;





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



