Rem
Rem $Header: utlincmp.sql 11-aug-99.11:24:15 rshaikh Exp $
Rem
Rem utlincmpt.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      utlincmpt.sql - UTiLity script to check for INCoMPaTible 
Rem                      objects in the database before a downgrade
Rem
Rem    DESCRIPTION
Rem      This script contains a set of queries that the user can use
Rem      to determine the database objects that are incompatible with
Rem      the release that they are downgrading to.  These queries
Rem	 are taken from the Oracle8 Migration manual.  Please refer
Rem	 to the section on removing incompatibilities for detailed
Rem	 instructions on how to remove incompatible objects
Rem
Rem    NOTES
Rem      * You must be connected as INTERNAL to run this script
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rshaikh     08/11/99 - bug 897520: match statements with mig manual
Rem    rshaikh     12/28/98 - add more compat checks
Rem    rshaikh     11/17/98 - add all incompatibility checks here
Rem    kmuthiah    10/15/98 - compatibility query for udrefs and sgrefs with rc
Rem    smuralid    07/13/98 - Created
Rem

set echo on

REM
REM incompatible triggers (for downgrading to release 8.1.5 or below)
REM
select u.name as trigger_owner, o.name as trigger_name
  from obj$ o, user$ u, trigger$ t
  where bitand(t.sys_evts, 8388352) > 0
    and t.obj# = o.obj#
    and o.owner# = u.user#
  order by o.obj#;

REM 
REM transported tablespaces
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 

select tablespace_name, plugged_in
  from dba_tablespaces
  where plugged_in = 'YES';


REM 
REM locally managed tablespaces
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select tablespace_name, extent_management
  from dba_tablespaces
  where extent_management = 'LOCAL';

REM 
REM temporary tables
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, table_name from dba_tables
  where temporary = 'Y' and
	table_name not like 'RUPD$%' and
	table_name not like 'ATEMPTAB$';

REM 
REM key compression on indexes
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select index_name, index_type, table_owner, table_name
  from dba_indexes where compression = 'ENABLED';


REM 
REM lobs in index organized tables
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select column_name, t.owner, t.table_name
  from dba_lobs l, dba_tables t
  where l.table_name = t.table_name 
	and l.owner = t.owner
	and t.iot_type = 'IOT';

REM 
REM varrays in index organized tables
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select v.parent_table_column, t.owner, t.table_name 
  from dba_varrays v, dba_tables t
  where v.parent_table_name = t.table_name
	and v.owner = t.owner
	and t.iot_type = 'IOT';

REM 
REM secondary indexes on index organized tables
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select index_name, i.owner, t.table_name
  from dba_indexes i, dba_tables t
  where i.index_type = 'NORMAL'
  and i.table_name = t.table_name
  and t.owner = i.table_owner
  and t.iot_type = 'IOT';

REM 
REM unused columns
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select * from dba_unused_col_tabs;

REM 
REM partially dropped columns
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select * from dba_partial_drop_tabs;

REM 
REM single table-only hash clusters
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select cluster_name, single_table from dba_clusters
  where single_table like '%Y%';

REM
REM incompatible materialized views
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM
select mv.owner, mv.name
  from dba_snapshots mv, dba_mview_analysis mva
  where mva.owner = mv.owner
  and mva.mview_name = mv.name;

REM 
REM refresh on commit mode
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, name, refresh_mode
  from dba_snapshots
  where refresh_mode = 'COMMIT';

REM 
REM never refresh mode materialized views
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, name, type
  from dba_snapshots
  where type = 'NEVER';

REM 
REM Bit-Mapped Indexes
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select o.name index_name, u.name index_owner
  from sys.user$ u, sys.obj$ o, sys.ind$ i, sys.tab$ t
  where t.obj# = o.obj#
  and i.bo# = t.obj# and t.spare1 > 32767
  and i.type# = 2 and o.owner# = u.user#;

REM 
REM function based indexes
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select distinct index_owner, index_name from dba_ind_columns
  where column_name is null;

REM 
REM domain indexes
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, index_name, index_type
  from dba_indexes
  where index_type = 'DOMAIN';

REM 
REM indextypes
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, indextype_name from dba_indextypes;

REM 
REM operators
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, operator_name from dba_operators;

REM 
REM dimensions
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select * from dba_dimensions;

REM 
REM partitioned iot
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select table_name, tablespace_name, iot_type, partitioned
  from dba_tables where partitioned = 'YES' and iot_type = 'IOT';

REM 
REM partitioned object tables
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select unique t.table_name, t.owner
  from dba_part_tables t, dba_tab_columns c
  where t.table_name = c.table_name
  and c.data_type in 
  (select type_name
	from dba_types
	where predefined = 'NO');

REM 
REM partitioned tables that use composite methods
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, table_name from dba_part_tables
  where partitioning_type != 'RANGE' or subpartitioning_type !='NONE';

REM 
REM universal rowid
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, table_name, column_name from dba_tab_columns
  where data_type = 'UROWID' order by owner, table_name;

REM 
REM stored procedures with urowid arguments
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, object_name, package_name, argument_name
  from all_arguments where data_type = 'UROWID' and
  package_name not in ('STANDARD')
  order by owner, object_name, package_name;

REM 
REM clobs and nclobs in table with varying-width character set **do more
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select table_name, column_name from dba_lobs
  where dba_lobs.owner != 'SYSTEM' and table_name not in
   ('KOTAD$', 'KOTMD$', 'KOTTB$', 'KOTTD$');

REM 
REM lob columns in partitioned tables
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select table_name, lob_name from dba_part_lobs;

REM 
REM user-defined datatypes to 8.1 format
REM This query selects out all the tables (and columns) which contain 
REM objects stored in the new image format
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select u.name as USER_NAME, o.name as TABLE_NAME, c.name as COLUMN_NAME
from sys.user$ u, sys.obj$ o, sys.tab$ t, sys.col$ c, sys.coltype$ ct
where bitand(ct.flags, 128) != 128 and
      o.obj# = c.obj# and o.obj# = ct.obj# and t.obj# = o.obj# and
      c.intcol# = ct.intcol# and 
      bitand(t.property, 8192) = 0 and                 /* not a nested table */
      u.user# = o.owner# and
      o.type# = 2 and                                /* must be a base table */
      bitand(c.property, 32) = 0 and                  /* not a hidden column */
      (c.type# = 123 or                                     /* varray column */
                                                   /* non-virtual ADT column */
       (c.type# = 121 and bitand(c.property, 8) = 0) or
                /* nested table column with adt columns in the storage table */
       (c.type# = 122 and exists 
          (select * from sys.ntab$ n1, sys.col$ c1, sys.coltype$ ct1
           where n1.obj# = c.obj# and n1.intcol# = c.intcol# and 
                 n1.ntab# = ct1.obj# and bitand(ct1.flags, 128) = 0 and
                 ct1.obj# = c1.obj# and ct1.intcol# = c1.intcol# and
                 bitand(c1.property, 8) = 0)));

REM 
REM user-defined object identifiers
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select table_name from dba_object_tables
  where object_id_type = 'USER-DEFINED';

REM 
REM ref columns based on user-defined objects
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select table_name, column_name from dba_refs
  where object_id_type = 'USER-DEFINED';

REM 
REM nested table features
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, parent_table_name
  from dba_nested_tables
  where storage_spec like '%USER_SPECIFIED%'
  or return_type like '%LOCATOR%';

REM 
REM 8.1 varray features
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, parent_table_name
  from dba_varrays
  where storage_spec like '%USER_SPECIFIED%';

REM
REM java
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM
select object_name, owner from all_objects where object_type like 'JAVA%';

REM 
REM non-persistent queues and queue tables
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, queue_table from dba_queue_tables
  where queue_table = 'AQ$_MEM_MC' or queue_table = 'AQ$_MEM_SC';

REM 
REM 8.1 compatible queue tables
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select queue_table, compatible from dba_queue_tables
  where compatible like '8.1%';

REM 
REM object level and system level privileges
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, table_name, privilege
  from dba_tab_privs where privilege like '%QUEUE';

select * from dba_sys_privs where privilege in 
  ('MANAGE ANY QUEUE', 'ENQUEUE ANY QUEUE', 'DEQUEUE ANY QUEUE');

REM
REM propagation (only for downgrading to release 8.0.3 or earlier)
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM
select owner, queue_table from dba_queue_tables
  where recipients = 'MULTIPLE';

REM
REM dbms_repair
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM
SELECT owner, table_name from dba_tables where skip_corrupt = 'ENABLED';

REM 
REM disable validate
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select constraint_name, status, validated
  from dba_constraints
  where status = 'DISABLED'
  and validated = 'VALIDATED';

REM 
REM triggers on nested table view columns
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select trigger_name, table_name, column_name
  from dba_triggers
  where column_name is not null;

REM 
REM incompatible triggers 
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select trigger_name, base_object_type, action_type
  from dba_triggers
  where base_object_type like '%DATABASE%' 
  or base_object_type like '%SCHEMA%'
  or action_type like  '%CALL%';

REM 
REM extensible optimizer
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select object_owner, object_name, column_name, object_type
  from dba_associations;

REM 
REM optimizer plan stability
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, name from dba_outlines;

REM 
REM application contexts
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select * from dba_context;

REM 
REM user-defined security policies
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select object_owner, object_name, policy_name 
  from dba_policies;

REM 
REM proxy users
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select * from proxy_users;

REM 
REM object groups at the top flavor
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select gname, fname from dba_repgroup
  where fname is not null;

REM 
REM temporary updatable snapshot logs
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
REM 
select owner, table_name from dba_tables
  where temporary='Y' and
  table_name like 'RUPD$%';


Rem
Rem This query selects out all system generated REF columns which 
Rem have a referential constraint defined on them
REM   See "Oracle8 Migration", Remove Incompatibilities for
REM   instructions on removing incompatibilities
Rem 
select u.name as USER_NAME, o.name as TABLE_NAME, 
       decode(bitand(c.property, 1), 1, ac.name, c.name) as COLUMN_NAME
from refcon$ r, coltype$ ct, obj$ o, user$ u, col$ c, attrcol$ ac
where bitand(r.reftyp, 4) != 4                     /* not a user-defined ref */
  and ct.obj# = r.obj#
  and ct.intcol# = r.intcoL#
  and ct.intcols = 1
  and c.obj# = r.obj#
  and c.intcol# = r.intcol#
  and o.obj# = r.obj#
  and o.owner# = u.user#
  and c.obj# = ac.obj#(+)
  and c.intcol# = ac.intcol#(+);




