14
Removing Incompatibilities Before Downgrading to Release 8.1

This chapter provides step-by-step instructions on removing incompatibilities before downgrading to release 8.1. This chapter covers the following topics:

• Identifying Incompatibilities

• Removing Incompatibilities Before Downgrading to Release 8.1.6 or Release 8.1.7

• Removing Incompatibilities Before Downgrading to Release 8.1.5

• Continue with Your Downgrade to Release 8.1

  See Also: Some aspects of downgrading are operating system-specific. See your operating system-specific Oracle documentation for additional operating system-specific instructions about downgrading.

Identifying Incompatibilities

To identify any incompatibilities that may exist with the release to which you are downgrading, perform the following steps:

1. Log in to the system as the owner of the Oracle home directory.

2. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

3. Start SQL*Plus.

4. Connect to the database instance as a user with SYSDBA privileges.

5. Query the V$COMPATIBILITY dynamic performance view to identify the incompatibilities:

   SQL> SELECT * FROM v$compatibility WHERE release != '0.0.0.0.0';
   
   

   An incompatibility exists wherever the value in the RELEASE column is higher than the release to which you are downgrading.

   Note: This query does not show features with a compatibility level of 0.0.0.0.0. These features currently are not in use, and no action is required for them.

6. Run utlincmp.sql:

   SQL> SPOOL utlincmp.out
   SQL> @utlincmp.sql
   SQL> SPOOL OFF
   
   

   The utlincmp.sql script runs all of the queries described in the rest of this chapter to identify incompatibilities. Therefore, you can perform all of the SELECT statements described in the rest of this chapter simply by running the utlincmp.sql script.

   After the utlincmp.sql script runs, view the utlincmp.out file and look for instances where a SELECT statement returned values. The values returned are incompatibilities with the previous release.

7. Drop or change all incompatibilities to make your database compatible with the release to which you are downgrading.

The following sections provide detailed information about removing incompatibilities with previous releases of Oracle. Depending on the release to which you are downgrading, you may need to read some or all of the following sections.

To remove incompatibilities, you may need to complete actions that require the privileges of user SYS. Therefore, you should log in as user SYS and connect with SYSDBA privileges to perform the actions described in the following sections, unless instructed otherwise.

Note: If you are downgrading from Oracle9i Enterprise Edition to Oracle9i (formerly Workgroup Server), then, before you downgrade, modify any applications that use the advanced features of Oracle9i Enterprise Edition so that they do not use these advanced features. See Oracle9i Database New Features for more information about the differences between the editions.

Removing Incompatibilities Before Downgrading to Release 8.1.6 or Release 8.1.7

If you are downgrading to release 8.1.6 or release 8.1.7, then complete the actions in the following sections to remove incompatibilities:

• Tablespaces

• Schema Objects

• Partitioning

• Datatypes

• User-Defined Datatypes

• SQL and PL/SQL

• Constraints and Triggers

Tablespaces

This section describes removing incompatibilities relating to tablespaces.

Discontinue Use of Automatic Segment-Space Managed Tablespaces

Before you downgrade to release 8.1.7 or lower, discontinue use of all automatic segment-space managed tablespaces. To identify existing automatic segment-space managed tablespaces, issue the following SQL statement:

SELECT tablespace_name FROM dba_tablespaces
    WHERE segment_space_management = 'AUTO';

These tablespaces must be dropped before downgrading.

Discontinue Use of Automatic Undo Managed Tablespaces

Before you downgrade to release 8.1.7 or lower, discontinue use of all automatic undo managed tablespaces. To identify existing automatic undo managed tablespaces, issue the following SQL statement:

SELECT tablespace_name FROM dba_tablespaces
    WHERE contents = 'UNDO';

These tablespaces must be dropped before downgrading.

Schema Objects

This section describes removing incompatibilities relating to schema objects.

Drop External Tables

Before you downgrade to release 8.1.7 or lower, drop all external tables. To identify existing external tables, issue the following SQL statement:

SELECT o.name AS TABLE_NAME, u.name AS TABLE_OWNER
    FROM sys.user$ u, sys.obj$ o, sys.tab$ t
    WHERE t.obj# = o.obj# AND o.owner# = u.user#
        AND BITAND(t.property, 2147483648) != 0;

Drop all tables listed.

Drop All Bitmap Secondary Indexes on Index-Organized Tables

Before you downgrade to release 8.1.7 or lower, drop all bitmap secondary indexes on non-partitioned and partitioned index organized tables in your database. To identify existing bitmap secondary indexes on index-organized tables, issue the following SQL statement:

SELECT index_name, i.owner, t.table_name
    FROM dba_indexes i, dba_tables t
    WHERE i.index_type = 'BITMAP' AND i.table_name = t.table_name
        AND t.owner = i.table_owner AND t.iot_type = 'IOT';

Rebuild Index-Organized Tables without Mapping Tables

Before you downgrade to release 8.1.7 or lower, after dropping all bitmap secondary indexes on non-partitioned and partitioned index-organized tables, you need to rebuild the corresponding index-organized tables without mapping tables.

To identify index-organized tables with mapping tables, issue the following SQL statement:

SELECT owner, iot_name
    FROM dba_tables
    WHERE iot_type = 'IOT_MAPPING';

For each of the tables (for example iot), you can rebuild without mapping tables as follows:

ALTER TABLE iot MOVE NOMAPPING;

Drop All B-Tree Indexes on UROWID Datatypes on Heap and Index-Organized Tables

Before you downgrade to release 8.1.7 or lower, drop all B-tree indexes on heap and index organized tables. To identify such B-tree indexes, issue the following SQL statement:

SELECT index_owner, index_name FROM dba_ind_columns ic, dba_tab_columns tc
    WHERE tc.data_type = 'UROWID' AND tc.table_name = ic.table_name
        AND tc.column_name = ic.column_name;

Remove Indexes With Large Keys

Before downgrading to release 8.1.7 or lower, remove Any index with large keys. To identify such indexes, issue the following SQL statement:

SELECT u.name, o.name, i.flags
    FROM sys.obj$ o, sys.user$ u, sys.ind$ i
    WHERE u.user# = o.owner#
        AND o.obj# = i.obj#
        AND BITAND(i.flags, 16384) != 0;

Drop any indexes identified by this statement.

Partitioning

This section describes disabling release 9.0.1 partitioning features.

Discontinue Use of Hash Partitioned Index-Organized Tables

Before you downgrade to release 8.1.7 or lower, discontinue use of all hash partitioned index-organized tables. To identify existing hash partitioned index-organized tables, issue the following SQL statement:

SELECT t.owner, t.table_name
    FROM dba_tables t, dba_part_tables p
    WHERE t.table_name = p.table_name AND t.owner = p.owner
        AND t.iot_type = 'IOT' AND t.partitioned = 'YES'
        AND p.partitioning_type = 'HASH';

If you do not need to preserve the table data, then simply drop the tables. However, if you need to preserve the table data, you can do it in one of the following ways:

• Move the table data into a range partitioned index-organized table or non-partitioned index-organized table using the CREATE TABLE ... AS SELECT statement.

• Export the table using the Oracle9i Export utility. The data can then be loaded into a non-partitioned index-organized table or a range-partitioned index-organized table using the release 8.1 Import utility.

Datatypes

This section describes disabling datatypes that are available only in release 9.0.1 and higher.

Discontinue Use of Datetime and Interval Datatypes

Before you downgrade to release 8.1.7 or lower, the following datetime and interval datatypes have to be dropped:

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

However, when the datatype is TIMESTAMP WITH LOCAL TIME ZONE, the TIMESTAMP WITH LOCAL TIME ZONE columns can be converted to DATE columns by explicitly issuing an ALTER TABLE statement.

The ALTER TABLE statement scans all rows of the table. If the TIMESTAMP WITH LOCAL TIME ZONE data has fractional seconds, the row data for the column will be updated by rounding the fractional seconds; if the TIMESTAMP WITH LOCAL TIME ZONE data has the minute field greater than or equal to 60, the row data for the column will be updated by subtracting 60 from its minute field. When modifying a TIMESTAMP WITH LOCAL TIME ZONE column to a DATE column, the information for fractional seconds and time zone adjustment will be lost.

Downgrading will fail if any of the following objects exist in the database:

• Any table containing columns of these new datatypes

• Any procedure or function (stand alone or inside a package) declared with arguments or a result of these new datatypes

• Any object type with attributes of these new datatypes, or member functions with arguments or a result of these new datatypes

• Any collection type whose element type is one of these new datatypes

These objects have to be dropped in order to downgrade to a previous release.

To list tables with columns of type TIMESTAMP, issue the following SQL statement:

SELECT owner, table_name, column_name
    FROM dba_tab_columns
    WHERE data_type LIKE 'TIMESTAMP(%)';

For each table listed as a result of this statement, drop its TIMESTAMP datatype columns, or drop the whole table.

To list tables with columns of type TIMESTAMP WITH TIME ZONE, issue the following SQL statement:

SELECT owner, table_name, column_name
    FROM dba_tab_columns
    WHERE data_type LIKE 'TIMESTAMP(%) WITH TIME ZONE';

For each table listed as a result of this statement, drop its TIMESTAMP WITH TIME ZONE datatype columns, or drop the whole table.

To list tables with columns of type TIMESTAMP WITH LOCAL TIME ZONE, issue the following SQL statement:

SELECT owner, table_name, column_name
    FROM dba_tab_columns
    WHERE data_type LIKE 'TIMESTAMP(%) WITH LOCAL TIME ZONE';

For each table listed as a result of this statement, drop its TIMESTAMP WITH LOCAL TIME ZONE datatype columns, or drop the whole table.

To list tables with columns of type INTERVAL YEAR TO MONTH, issue the following SQL statement:

SELECT owner, table_name, column_name
    FROM dba_tab_columns
    WHERE data_type LIKE 'INTERVAL YEAR(%) TO MONTH';

For each table listed as a result of this statement, drop its INTERVAL YEAR TO MONTH datatype columns, or drop the whole table.

To list tables with columns of type INTERVAL DAY TO SECOND, issue the following SQL statement:

SELECT owner, table_name, column_name
    FROM dba_tab_columns
    WHERE data_type LIKE 'INTERVAL DAY(%) TO SECOND';

For each table listed as a result of this statement, drop its INTERVAL DAY TO SECOND datatype columns, or drop the whole table.

To find a list of procedures and functions declared with arguments or a result of type TIMESTAMP, issue the following SQL statement:

SELECT owner, object_name, package_name, argument_name
    FROM all_arguments
    WHERE data_type = 'TIMESTAMP';

To find a list of procedures and functions declared with arguments or a result of type TIMESTAMP WITH TIME ZONE, issue the following SQL statement:

SELECT owner, object_name, package_name, argument_name
    FROM all_arguments
    WHERE data_type = 'TIMESTAMP WITH TIME ZONE';

To find a list of procedures and functions declared with arguments or a result of type TIMESTAMP WITH LOCAL TIME ZONE, issue the following SQL statement:

SELECT owner, object_name, package_name, argument_name
    FROM all_arguments
    WHERE data_type = 'TIMESTAMP WITH LOCAL TIME ZONE';

To find a list of procedures and functions declared with arguments or a result of type INTERVAL YEAR TO MONTH, issue the following SQL statement:

SELECT owner, object_name, package_name, argument_name
    FROM all_arguments
    WHERE data_type = 'INTERVAL YEAR TO MONTH';

To find a list of procedures and functions declared with arguments or a result of type INTERVAL DAY TO SECOND, issue the following SQL statement:

SELECT owner, object_name, package_name, argument_name
    FROM all_arguments
    WHERE data_type = 'INTERVAL DAY TO SECOND';

To find a list of object types with attributes of type TIMESTAMP, or member functions with arguments or a result of type TIMESTAMP, issue the following SQL statement:

SELECT owner, type_name, attr_name
    FROM dba_type_attrs
    WHERE attr_type_name = 'TIMESTAMP';

SELECT owner, type_name, method_name, param_name
    FROM dba_method_params
    WHERE param_type_name = 'TIMESTAMP';

SELECT owner, type_name, method_name
    FROM dba_method_results
    WHERE result_type_name = 'TIMESTAMP';

To find a list of object types with attributes of type TIMESTAMP WITH TIME ZONE, or member functions with arguments or a result of type TIMESTAMP WITH TIME ZONE, issue the following SQL statement:

SELECT owner, type_name, attr_name
    FROM dba_type_attrs
    WHERE attr_type_name = 'TIMESTAMP WITH TIME ZONE';

SELECT owner, type_name, method_name, param_name
    FROM dba_method_params
    WHERE param_type_name = 'TIMESTAMP WITH TIME ZONE';

SELECT owner, type_name, method_name
    FROM dba_method_results
    WHERE result_type_name = 'TIMESTAMP WITH TIME ZONE';

To find a list of object types with attributes of type TIMESTAMP WITH LOCAL TIME ZONE, or member functions with arguments or a result of type TIMESTAMP WITH LOCAL TIME ZONE, issue the following SQL statement:

SELECT owner, type_name, attr_name
    FROM dba_type_attrs
    WHERE attr_type_name = 'TIMESTAMP WITH LOCAL TIME ZONE';

SELECT owner, type_name, method_name, param_name
    FROM dba_method_params
    WHERE param_type_name = 'TIMESTAMP WITH LOCAL TIME ZONE';

SELECT owner, type_name, method_name
    FROM dba_method_results
    WHERE result_type_name = 'TIMESTAMP WITH LOCAL TIME ZONE';

To find a list of object types with attributes of type INTERVAL YEAR TO MONTH, or member functions with arguments or a result of type INTERVAL YEAR TO MONTH, issue the following SQL statement:

SELECT owner, type_name, attr_name
    FROM dba_type_attrs
    WHERE attr_type_name = 'INTERVAL YEAR TO MONTH';

SELECT owner, type_name, method_name, param_name
    FROM dba_method_params
    WHERE param_type_name = 'INTERVAL YEAR TO MONTH';

SELECT owner, type_name, method_name
    FROM dba_method_results
    WHERE result_type_name = 'INTERVAL YEAR TO MONTH';

To find a list of object types with attributes of type INTERVAL DAY TO SECOND, or member functions with arguments or a result of type INTERVAL DAY TO SECOND, issue the following SQL statement:

SELECT owner, type_name, attr_name
    FROM dba_type_attrs
    WHERE attr_type_name = 'INTERVAL DAY TO SECOND';

SELECT owner, type_name, method_name, param_name
    FROM dba_method_params
    WHERE param_type_name = 'INTERVAL DAY TO SECOND';

SELECT owner, type_name, method_name
    FROM dba_method_results
    WHERE result_type_name = 'INTERVAL DAY TO SECOND';

To find a list of collection types with elements of type TIMESTAMP, issue the following SQL statement:

SELECT owner, type_name, coll_type
    FROM dba_coll_types
    WHERE elem_type_name = 'TIMESTAMP';

To find a list of collection types with elements of type TIMESTAMP WITH TIME ZONE, issue the following SQL statement:

SELECT owner, type_name, coll_type
    FROM dba_coll_types
    WHERE elem_type_name = 'TIMESTAMP WITH TIME ZONE';

To find a list of collection types with elements of type TIMESTAMP WITH LOCAL TIME ZONE, issue the following SQL statement:

SELECT owner, type_name, coll_type
    FROM dba_coll_types
    WHERE elem_type_name = 'TIMESTAMP WITH LOCAL TIME ZONE';

To find a list of collection types with elements of type INTERVAL YEAR TO MONTH, issue the following SQL statement:

SELECT owner, type_name, coll_type
    FROM dba_coll_types
    WHERE elem_type_name = 'INTERVAL YEAR TO MONTH';

To find a list of collection types with elements of type INTERVAL DAY TO SECOND, issue the following SQL statement:

SELECT owner, type_name, coll_type
    FROM dba_coll_types
    WHERE elem_type_name = 'INTERVAL DAY TO SECOND';

Discontinue Use of LOB Columns in Partitioned Index-Organized Tables

Before you downgrade to release 8.1.7 or lower, discontinue use of all LOB columns in partitioned index-organized tables. To identify existing partitioned index-organized tables with LOB columns, issue the following SQL statement:

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' AND t.partitioned = 'YES';

If you do not need to preserve the LOB columns and their data, simply drop the columns. However, if you need to preserve the LOB columns, you can create corresponding non-partitioned index-organized tables. For example, issue the following SQL statement to create non-partitioned index-organized tables corresponding to the tables listed in the previous statement:

CREATE lob_iot (c1 primary key, c2) AS SELECT * FROM lob_piot;

User-Defined Datatypes

This section describes disabling features related to user-defined datatypes that are only available in release 9.0.1 and higher.

Drop User-Defined Aggregate Functions

Before you downgrade to release 8.1.7 or lower, drop all user-defined aggregate functions. To identify existing user-defined aggregate functions, issue the following SQL statement:

SELECT procedure_name FROM dba_procedures
    WHERE aggregate = 'YES';

Drop all aggregate functions listed.

Remove All Evolved Types and Their Dependent Types and Tables

Before you downgrade to release 8.1.7 or lower, all evolved types and their dependent types and tables must be removed. To identify all evolved types, issue the following SQL statement:

SELECT UNIQUE owner, type_name
    FROM dba_types
    WHERE version_name != '$8.0';

To identify all tables that reference an evolved type, issue the following SQL statement:

SELECT UNIQUE owner, table_name
    FROM dba_tab_columns
    WHERE data_type_owner IS NOT NULL
        AND version_name != '$8.0';

Discontinue Use of Subtypes and Non-Final Types

Before you downgrade to release 8.1.7 or lower, discontinue use of all subtypes and non-final types in tables. To identify the use of existing subtypes and non-final types in tables, issue the following SQL statement:

SELECT c.name AS COLUMN_NAME, o.name AS TABLE_NAME, u.name AS TABLE_OWNER
    FROM user$ u, sys.obj$ o, sys.col$ c, sys.coltype$ ct, sys.type$ t
    WHERE u.user# = o.owner# AND o.obj# = c.obj# AND c.obj# = ct.obj#
        AND c.intcol# = ct.intcol# and ct.toid = t.toid AND o.type# = 2
        AND BITAND(t.properties, 3153928) > 0;

Discontinue Use of Varray Columns in Partitioned Index-Organized Tables

Before you downgrade to release 8.1.7 or lower, discontinue use of all varray columns in partitioned index-organized tables. To identify existing partitioned index-organized tables with varrays, issue the following SQL statement:

SELECT v.parent_table_name, 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' AND t.partitioned = 'YES';

If you do not need to preserve the varray columns and their data, simply drop the columns. However, if you need to preserve the varray columns, you can create corresponding non-partitioned index-organized tables. For example, issue the following SQL statement to create non-partitioned index-organized tables corresponding to the tables listed in the previous statement:

CREATE lob_iot (c1 primary key, c2) AS SELECT * FROM varray_piot;

SQL and PL/SQL

The following sections describe specific SQL and PL/SQL downgrading issues. The actions described in these sections help you to avoid compile and runtime errors in SQL scripts and stored procedures. Although these actions are not strictly required, Oracle Corporation recommends that you perform them before you downgrade.

Discontinue Use of Pipelined Table Functions

Before you downgrade to release 8.1.7 or lower, discontinue use of all pipelined table functions. To identify existing pipelined table functions, issue the following SQL statement:

SELECT procedure_name FROM dba_procedures
    WHERE pipelined = 'YES';

Discontinue Use of Parallel Table Functions

Before you downgrade to release 8.1.7 or lower, discontinue use of all parallel table functions. To identify existing parallel table functions, issue the following SQL statement:

SELECT procedure_name FROM dba_procedures
    WHERE parallel = 'YES';

Constraints and Triggers

This section describes removing incompatibilities relating to constraints and triggers.

Drop All View Constraints

Before you downgrade to release 8.1.7 or lower, drop all view related primary key, unqiue, and foreign key constraints. To identify existing view constraints, issue the following SQL statement:

SELECT * FROM dba_constraints WHERE view_related = 'DEPEND_ON_VIEW';

Removing Incompatibilities Before Downgrading to Release 8.1.5

If you are downgrading to release 8.1.5, then complete the actions in the following sections to remove incompatibilities:

• Tablespaces

• Datatypes

• Constraints and Triggers

• Security

• Advanced Queuing (AQ)

Tablespaces

This section describes removing incompatibilities relating to tablespaces.

Drop or Convert Migrated Tablespaces

Release 8.1.6 and higher supports tablespace migration, which allows tablespaces to be migrated from dictionary managed format to locally managed format and vice versa. When a tablespace is migrated from dictionary managed to locally managed format, the tablespace is marked as a 'migrated tablespace' under certain conditions. You cannot downgrade to a previous release of Oracle if your database has such tablespaces.

To identify incompatible migrated tablespaces, enter the following SQL statement:

SELECT DISTINCT(tablespace_name) FROM dba_segments
    WHERE segment_type = 'SPACE HEADER';

You can either convert the tablespaces listed to dictionary managed tablespaces, or you can drop them. To convert migrated tablespaces listed to dictionary managed tablespaces, execute the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL procedure. For example, if a tablespace named TS1 is listed, execute the following procedure:

EXECUTE dbms_space_admin.tablespace_migrate_from_local('TS1');

To drop them, issue a DROP TABLESPACE statement. For example, if a tablespace named TS1 is listed, issue the following statement to drop the tablespace:

DROP TABLESPACE TS1
    INCLUDING CONTENTS 
        CASCADE CONSTRAINTS;

Correct Transient Segments

During migration of tablespaces from dictionary managed format to locally managed format (and vice versa), the segments in the tablespace are temporarily put in a transient state. You cannot downgrade a database with these transient segments.

To identify transient segments, enter the following SQL statement:

SELECT DISTINCT(tablespace_name) FROM sys_dba_segs
    WHERE DECODE(BITAND(segment_flags, 16), 16, 1, 0) = 1;

To correct the transient segments listed, execute the DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_STATES procedure on the tablespace. For example, if a tablespace named TS1 is listed, execute the following procedure:

EXECUTE dbms_space_admin.tablespace_fix_segment_states('TS1');

Dropping Segments in Optimized Locally Managed Tablespaces

In release 8.1.6 and higher, dropping segments in locally managed tablespaces is optimized by storing some additional information in the data dictionary. You cannot downgrade until all of the temporary segments in the locally managed tablespaces are dropped. You can check for these segments either by querying the V$COMPATSEG view or by attempting to start up the database with a COMPATIBLE initialization parameter setting lower than 8.1.6.

To query the V$COMPATSEG view, issue the following SQL statement:

SELECT * FROM v$compatseg;

The incompatible segments have FASTDROP in the TYPE_ID column and a value of 8.1.6.0.0 in the RELEASE column.

If you start up the database with a COMPATIBLE setting lower than 8.1.6 and there are incompatible segments, startup fails and error messages similar to the following are displayed:

ORA-00402: database changes by release 8.1.6.0.0 cannot be used by release 8.1.5.0.0
ORA-00405: compatibility type "Faster segment drop"

When you reset database compatibility later in the downgrade process, Oracle will attempt to remove the temporary segments. If these segments are not removed the first time you reset compatibility, repeat the procedure to reset database compatibility.

See Also: "Reset Database Compatibility" for instructions on resetting database compatibility.

Datatypes

This section describes disabling datatypes that are available only in release 8.1.6 and higher.

Discontinue Use of CACHE READS Specification for LOBs

Before you downgrade to release 8.1.5, you must stop using the CACHE READS storage parameter for LOBs.

The following sections contain SQL statements that identify existing uses of CACHE READS specification for LOBs.

To identify tables that have LOB columns specified as cache reads, issue the following SQL statement as user SYS:

SELECT owner, table_name, column_name 
    FROM dba_lobs
    WHERE cache = 'CACHEREADS';

To identify partitioned tables that have LOB columns specified as CACHE READS as default attributes at the table level, issue the following SQL statement as user SYS:

SELECT table_owner, table_name, column_name
  FROM dba_part_lobs
  WHERE def_cache = 'CACHEREADS';

To identify partitioned tables that have LOB columns specified as CACHE READS at the partition level, issue the following SQL statement as user SYS:

SELECT table_owner, table_name, column_name, partition_name 
    FROM dba_lob_partitions 
    WHERE cache = 'CACHEREADS';

To identify partitioned tables that have LOB columns specified as CACHE READS at the subpartition level, issue the following SQL statement as user SYS:

SELECT table_owner, table_name, column_name, subpartition_name
    FROM dba_lob_subpartitions
    WHERE cache = 'CACHEREADS';

After you have identified all of the uses of the CACHE READS specification for LOBs, you can change them to use the CACHE or NOCACHE specification, because these specifications are compatible with release 8.1.5. The following sections provide examples of the SQL statements you must issue to make these changes.

To change a LOB storage parameter from CACHE READS to CACHE or NOCACHE, use the ALTER TABLE ... MODIFY LOB statement. For example, on a table named lob_tab with a LOB column named lob_col, issue the following SQL statement to change the storage specification to CACHE:

ALTER TABLE lob_tab MODIFY LOB (lob_col) (CACHE);

This statement can also be used to modify LOB storage parameters at both the table and partition level for partitioned tables. To modify only the default table level attributes of LOB columns from CACHE READS to CACHE or NOCACHE for partitioned tables, use the ALTER TABLE ... MODIFY DEFAULT ATTRIBUTES statement. For example, on a table named lob_part_tab with a LOB column named lob_col, issue the following SQL statement to change the storage specification to NOCACHE:

ALTER TABLE lob_part_tab 
    MODIFY DEFAULT ATTRIBUTES LOB (lob_col) (NOCACHE);

To modify LOB storage parameter from CACHE READS to CACHE or NOCACHE at the partition level for partitioned tables, use the ALTER TABLE ... MODIFY PARTITION statement. For example, on a table named lob_part_tab with a LOB column named lob_col and a partition named part_1, issue the following SQL statement to change the storage specification to CACHE:

ALTER TABLE lob_part_tab 
    MODIFY PARTITION part_1 LOB (lob_col) (CACHE);

Constraints and Triggers

This section describes removing incompatibilities relating to constraints and triggers.

Remove Incompatible Triggers

A new feature in release 8.1.6 and higher supports triggers on all SQL DDL statements, instead of only CREATE, ALTER, and DROP statements. These triggers must be dropped before downgrading. To check for triggers that are incompatible with release 8.1.5, connect with SYSDBA privileges and issue the following SQL statement:

SELECT owner, trigger_name, triggering_event
    FROM dba_triggers
    WHERE base_object_type LIKE '%DATABASE%' OR base_object_type LIKE '%SCHEMA%';

Drop all of the triggers that have one of the following events in the TRIGGERING_EVENT column:

• ANALYZE

• ASSOCIATE STATISTICS

• AUDIT

• COMMENT

• DDL

• DISASSOCIATE STATISTICS

• GRANT

• NOAUDIT

• RENAME

• REVOKE

• TRUNCATE

Security

This section describes removing incompatibilities relating to database security.

Removing or Recreating Global Users Whose External Name Is NULL

The schema-independent user feature of Oracle Advanced Security, in which many enterprise users access a shared schema, is not compatible with release 8.1.5. If you are using this feature, you need to identify the shared schemas. That is, the global users whose external name is NULL. To identify the shared schemas, issue the following SQL statement:

SELECT username FROM dba_users
    WHERE password = 'GLOBAL' AND external_name IS NULL;

You then need to either remove the users listed or recreate them so that they have a non-NULL external name.

Also, any enterprise users who access the shared schema need to be created as database users so that they can now access database objects. These enterprise users did not previously exist in the database. You can create them as global users (that is, authenticated by SSL), as externally authenticated users, or as users authenticated by password.

For example, suppose you created the following global user in release 8.1.6 or higher:

CONNECT system/system_password
CREATE USER user1 IDENTIFIED GLOBALLY AS '';

To make this user compatible with release 8.1.5 by authenticating the user with a password, issue the following SQL statements:

CONNECT system/system_password
ALTER USER user1 IDENTIFIED BY welcome;

Substitute your SYSTEM user password to connect.

Advanced Queuing (AQ)

Complete the following tasks to disable release 8.1.6 and higher AQ features in your queue tables:

Drop Queue Tables Containing Special User-Defined Types

Release 8.1.6 and higher creates JMS types for use in queue tables. These types are dropped automatically during downgrade. So, before downgrading, you must drop the queue tables containing these JMS types. To identify these queue tables, issue the following SQL statement:

SELECT owner, queue_table, object_type FROM all_queue_tables
    WHERE object_type LIKE 'SYS.AQ$_JMS%';

To drop the queue tables listed, execute the DBMS_AQADM.DROP_QUEUE_TABLE procedure. For example, if a queue table named QTABLE1 owned by user SCOTT is listed, execute the following procedure to drop the queue table:

EXECUTE dbms_aqadm.drop_queue_table(queue_table => 'scott.qtable1',
    force => TRUE);

Continue with Your Downgrade to Release 8.1

After you have removed all of the incompatibilities with release 8.1, go to "Reset Database Compatibility".