7
Downgrading a Database Back to the Previous Oracle Release

This chapter guides you through the process of downgrading a database back to the previous Oracle release. This chapter covers the following topics:

Supported Releases for Downgrading
Perform a Full Offline Backup
Remove Incompatibilities
Reset Database Compatibility
Downgrading Specific Components

Downgrade the Database

See Also:

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

Supported Releases for Downgrading

In Oracle9i release 9.2, only the following releases are supported for downgrading:

Release 9.0.1.3.0
Release 8.1.7.3.0

If the release number of your previous database is an 8.1.7 release earleir than release 8.1.7.3.0, then install the 8.1.7.3.0 patch release software before you downgrade.

Similarly, if the release number of your database is a 9.0.1 release earleir than release 9.0.1.3.0, then install the 9.0.1.3.0 patch release software before you downgrade.

You do not need to first upgrade your previous database to the 8.1.7.3.0 or 9.0.1.3.0 patch releases, but the patch release software must be installed before the downgrade from release 9.2.

Perform a Full Offline Backup

Perform a full offline backup of your release 9.2 database before you downgrade.

See Also:

Oracle9i User-Managed Backup and Recovery Guide for more information

Remove Incompatibilities

The process of removing incompatibilities depends on the release to which you are downgrading. First, check the compatibility level of your database to see if your database might have incompatibilities with the release to which you are downgrading.

Checking the Compatibility Level of Your Database

If the compatibility level of your database is higher than the release to which you are downgrading, then your database may have incompatibilities with the previous release that must be removed before you downgrade. Your compatibility level is determined by the setting of the COMPATIBLE initialization parameter. Check your COMPATIBLE initialization parameter setting by issuing the following SQL statement:

SQL> SELECT name, value, description FROM v$parameter
         WHERE name='compatible';

You do not need to remove incompatibilities if the COMPATIBLE parameter is set to the release to which you are downgrading or lower. For example, if you are downgrading to release 9.0.1 and the COMPATIBLE parameter is set to 9.0.0 or lower, then you do not need to remove incompatibilities. In this case, no incompatibilities exist in your database with the release to which you are downgrading, and you can skip the rest of this section and proceed to "Downgrading Specific Components".

However, if the COMPATIBLE parameter is set higher than the release to which you are downgrading, then some incompatibilities may exist. For example, if you are downgrading to release 8.1.7 and COMPATIBLE is set to 9.0.0 or higher, then incompatibilities may exist.

Identifying Incompatibilities

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

Log in to the system as the owner of the Oracle home directory.
At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.
Start SQL*Plus.
Connect to the database instance as a user with SYSDBA privileges.
Start up the instance in RESTRICT mode:
```
SQL> STARTUP RESTRICT
```
You may need to use the PFILE option to specify the location of your initialization parameter file.
Query the V$COMPATIBILITY dynamic performance view to identify any 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 are not currently in use, and no action is required for them.
Run utlincmp.sql:
```
SQL> SPOOL utlincmp.log
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.log file and look for instances where a SELECT statement returned values. The values returned are incompatibilities that may need to be removed depending on the release to which you are downgrading.
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 Oracle releases. Depending on the release to which you are downgrading, you may need to complete the steps in some or all of the following sections.

For example, if you are downgrading to release 9.0.1, then you only need to complete the steps in "Removing Release 9.2 Incompatibilities". However, if you are downgrading to release 8.1.7, then you need to complete the steps in "Removing Release 9.2 Incompatibilities" as well as the steps in "Removing Release 9.0.1 Incompatibilities".

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 Release 9.2 Incompatibilities

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

Release 9.2 DEFAULT Partitions

This section describes removing incompatibilities relating to release 9.2 DEFAULT partitions.

Drop All DEFAULT Partitions on List Partitioned Tables

Before you downgrade to release 9.0.1 or lower, drop all DEFAULT partitions on list partitioned tables. To identify all list partitioned tables with DEFAULT partitions, issue the following SQL statement:

SELECT u.name AS OWNER, o.name AS TABLE_NAME, o.subname AS PARTITION_NAME
    FROM sys.user$ u, sys.obj$ o, sys.tabpart$ tp
    WHERE BITAND(tp.flags, 16384) = 16384
        AND tp.obj# = o.obj# AND o.owner# = u.user#;

For each partition represented by the PARTITION_NAME column in the table represented by the OWNER.TABLE_NAME columns, simply drop the partition:

ALTER TABLE OWNER.TABLE_NAME DROP PARTITION PARTITION_NAME;

Release 9.2 Partitioning Methods

This section describes removing incompatibilities relating to release 9.2 partitioning methods.

Drop All Partitioned Tables That Use Range-List Methods

Before you downgrade to release 9.0.1 or lower, drop all partitioned tables that use range-list methods. To identify existing tables partitioned with range-list methods, issue the following SQL statement:

SELECT u.name AS OWNER, o.name AS TABLE_NAME
    FROM sys.user$ u, sys.obj$ o, sys.partobj$ po
    WHERE po.parttype = 1 AND MOD(po.spare2, 256) = 4
        AND o.obj# = po.obj# AND o.owner# = u.user#;

If you do not need to preserve the table data, then, for each table represented by the OWNER.TABLE_NAME columns, simply drop the table:

DROP TABLE OWNER.TABLE_NAME;

However, if you need to preserve the table data, then copy the data into non-partitioned tables, or copy the data into tables partitioned by range, hash, list, or another composite method.

Release 9.2 Streams

This section describes removing incompatibilities relating to release 9.2 Streams.

Drop All Streams Capture Processes

Before you downgrade to release 9.0.1 or lower, drop all Streams capture processes. To identify existing capture processes, issue the following SQL statement:

SELECT capture_name FROM dba_capture;

For each capture process listed in the CAPTURE_NAME column, issue the following SQL statement:

EXECUTE dbms_capture_adm.drop_capture(capture_name => 'CAPTURE_NAME');

Drop All Streams Apply Processes

Before you downgrade to release 9.0.1 or lower, drop all Streams apply processes. To identify existing apply processes, issue the following SQL statement:

SELECT apply_name FROM dba_apply;

For each apply process listed in the APPLY_NAME column, issue the following SQL statement:

EXECUTE dbms_apply_adm.drop_apply(apply_name => 'APPLY_NAME');

Release 9.2 Subpartition Templates

This section describes removing incompatibilities relating to release 9.2 subpartition templates.

Drop All Subpartition Templates in Composite Partitioned Tables

Before you downgrade to release 9.0.1 or lower, drop all subpartition templates in composite partitioned tables. To identify existing composite partitioned tables with subpartition templates, issue the following SQL statement:

SELECT u.name AS OWNER, o.name AS TABLE_NAME
    FROM sys.user$ u, sys.obj$ o
    WHERE o.owner# = u.user#
        AND o.obj# in (SELECT DISTINCT bo# FROM defsubpart$)
UNION
SELECT u.name AS OWNER, o.name AS TABLE_NAME
    FROM sys.user$ u, sys.obj$ o
    WHERE o.owner# = u.user#
        AND o.obj# in (SELECT DISTINCT bo# from defsubpartlob$);

For each table represented by the OWNER.TABLE_NAME columns, simply drop the subpartition template:

ALTER TABLE OWNER.TABLE_NAME SET SUBPARTITION TEMPLATE ();

LOB Retention

This section describes removing incompatibilities relating to LOB retention.

Drop Retention Stored in LOB Columns

Before you downgrade to release 9.0.1 or lower, drop retention stored in LOB columns. To identify existing LOB columns with retention, issue the following SQL statement:

SELECT u.name AS OWNER, o.name AS TABLE_NAME, c.name AS LOB_COL_NAME
    FROM sys.user$ u, sys.obj$ o, sys.col$ c, sys.lob$ l
    WHERE BITAND(l.flags, 64) = 64 AND l.obj# = o.obj#
        AND c.obj# = o.obj# AND c.col# = l.col#
        AND o.owner# = u.user#;

For each column represented by the LOB_COL_NAME column in the table represented by the OWNER.TABLE_NAME columns, simply drop the retention:

ALTER TABLE OWNER.TABLE_NAME MODIFY LOB(LOB_COL_NAME)
    (REBUILD FREEPOOLS);

Automatic Segment-Space Managed Tablespaces with LOBs

This section describes removing incompatibilities relating to automatic segment-space managed tablespaces with LOBs.

Drop LOB Columns in Automatic Segment-Space Managed Tablespaces

Before you downgrade to release 9.0.1 or lower, drop all LOB columns in automatic segment-space managed tablespaces. To identify existing automatic segment-space managed tablespaces with LOB columns, issue the following SQL statement:

SELECT u.name AS OWNER, o.name AS TABLE_NAME, c.name AS LOB_COL_NAME
    FROM sys.lob$ l, sys.ts$ t, sys.user$ u, sys.obj$ o, sys.col$ c
    WHERE l.ts# = t.ts# AND
        (DECODE(BITAND(t.flags, 32), 32, 1, 0) = 1 AND t.online$ <> 3) AND
        o.owner# = u.user# AND l.obj# = o.obj# AND
        l.obj# = c.obj# AND l.col# = c.col#;

For each LOB segment listed, perform one of the following actions:

The LOB columns can be moved to a tablespace that is not auto segment-space managed:

ALTER TABLE OWNER.TABLE_NAME MOVE LOB(LOB_COL_NAME) STORE AS
    (TABLESPACE manual segment space-managed tablespace);

The table containing the LOB columns can be dropped:
```
DROP TABLE OWNER.TABLE_NAME;
```

Removing Release 9.0.1 Incompatibilities

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

Tablespaces

This section describes removing incompatibilities relating to tablespaces that were introduced in release 9.0.1.

Drop All Automatic Segment-Space Managed Tablespaces

Before you downgrade to release 8.1.7 or lower, drop 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';

For each tablespace represented by the TABLESPACE_NAME column, simply drop the tablespace:

DROP TABLESPACE TABLESPACE_NAME;

Drop All Undo Tablespaces

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

SELECT name AS TABLESPACE_NAME FROM sys.ts$
    WHERE BITAND(flags, 16) = 16 AND online$ <> 3;

For each tablespace represented by the TABLESPACE_NAME column, simply drop the tablespace:

DROP TABLESPACE TABLESPACE_NAME;

Schema Objects

This section describes removing incompatibilities relating to schema objects that were introduced in release 9.0.1.

Drop All 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 u.name AS OWNER, o.name AS TABLE_NAME
    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;

For each table represented by the OWNER.TABLE_NAME columns, simply drop the table:

DROP TABLE OWNER.TABLE_NAME;

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.

Release 9.0 Partitioning Methods

This section describes removing incompatibilities relating to release 9.0 partitioning methods.

Drop All Partitioned Tables That Use List Methods

Before you downgrade to release 8.1.7 or lower, drop all partitioned tables that use list methods. To identify existing tables partitioned with list methods, issue the following SQL statement:

SELECT u.name AS OWNER, o.name AS TABLE_NAME
    FROM sys.user$ u, sys.obj$ o, sys.partobj$ po
    WHERE po.parttype = 4
        AND o.obj# = po.obj# AND o.owner# = u.user#;

If you do not need to preserve the table data, then, for each table represented by the OWNER.TABLE_NAME columns, simply drop the table:

DROP TABLE OWNER.TABLE_NAME;

However, if you need to preserve the table data, then copy the data into non-partitioned tables, or copy the data into tables partitioned by range, hash, or another composite method.

Hash Partitioned Index-Organized Tables

This section describes removing incompatibilities relating to hash partitioned index-organized tables.

Drop All Hash Partitioned Index-Organized Tables

Before you downgrade to release 8.1.7 or lower, drop 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, for each table represented by the OWNER.TABLE_NAME columns, simply drop the table:

DROP TABLE OWNER.TABLE_NAME;

However, if you need to preserve the table data, then 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.
```
CREATE range or non-partitioned index-organized table ... AS SELECT * FROM 
OWNER.TABLE_NAME;
DROP TABLE OWNER.TABLE_NAME;
```
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.7 Import utility.

PDML ITL Invariants

Before you downgrade to release 8.1.7 or lower, remove all PDML ITL invariants. To identify existing PDML ITL invariants, issue the following SQL statement:

SELECT COUNT(*) FROM sys.tab$
    WHERE BITAND(property, 536870912) > 0;

If this query returns a result greater than 0, then perform the following steps:

Change to the ORACLE_HOME/rdbms/admin directory.
Run utlpitl.sql:
```
SQL> @utlpitl.sql
```

Partitioned Index-Organized Tables with LOBs

This section describes removing incompatibilities relating to partitioned index-organized tables with LOBs.

Drop All LOB Columns in Partitioned Index-Organized Tables

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

SELECT t.OWNER, t.TABLE_NAME, l.COLUMN_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, then, for each column represented by the COLUMN_NAME column in the table represented by the OWNER.TABLE_NAME columns, simply drop the column:

ALTER TABLE OWNER.TABLE_NAME DROP COLUMN COLUMN_NAME;

However, if you need to preserve the LOB columns, then you can create corresponding non-partitioned index-organized tables:

CREATE non-partitioned index-organized table ... AS SELECT * FROM OWNER.TABLE_
NAME;
DROP TABLE OWNER.TABLE_NAME;

Drop All Varray Columns in Partitioned Index-Organized Tables

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

SELECT v.OWNER, v.PARENT_TABLE_NAME, v.PARENT_TABLE_COLUMN
    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, then, for each column represented by the PARENT_TABLE_COLUMN column in the table represented by the OWNER.PARENT_TABLE_NAME columns, simply drop the column:

ALTER TABLE OWNER.PARENT_TABLE_NAME DROP COLUMN PARENT_TABLE_COLUMN;

However, if you need to preserve the varray columns, then you can create corresponding non-partitioned index-organized tables:

CREATE non-partitioned index-organized table ... AS SELECT * FROM OWNER.PARENT_
TABLE_NAME;
DROP TABLE OWNER.PARENT_TABLE_NAME;

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';

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;

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';

Reset Database Compatibility

After you have removed all of the incompatibilities with the release to which you are downgrading, reset the compatibility level of the database to the previous release.

If your database fails to open after lowering the value of the COMPATIBLE initialization parameter, then some incompatibilities still exist. If so, reset the COMPATIBLE initialization parameter to the higher setting. Remove the incompatibilities and attempt to reset database compatibility again. All incompatibilities with the release to which you are downgrading must be removed before you proceed with the downgrade process.

See Also:

"Remove Incompatibilities" for information about removing incompatibilities

Downgrading Specific Components

Some components of the Oracle database server require a downgrade separate from the general downgrade procedure discussed in "Downgrade the Database". Table 7-1 lists components and their downgrade status:

Table 7-1 Component Downgrade Status

Installed Component	Automatically Downgraded
Oracle9i Catalog Views	Yes
Oracle9i Packages and Types	Yes
JServer JAVA Virtual Machine	Yes
Oracle9i Java Packages	Yes
Oracle XDK for Java	Yes
Messaging Gateway	Yes
Oracle Text	No
Oracle9i Real Application Clusters	Yes
Oracle Workspace Manager	Yes
Oracle Data Mining	Yes
Oracle Ultra Search	No
OLAP Catalog	Yes
OLAP Analytic Workspace	Yes
Oracle Spatial	No
Oracle interMedia	No
Oracle Visual Information Retrieval	No
Oracle Label Security	Yes

Complete the actions in the following sections to downgrade components that are not automatically downgraded.

Removing OLAP Incompatibilities Before Downgrading to Release 8.1.7

Run the following script to remove OLAP incompatibilities:

ORACLE_HOME/olap/admin/olapidrp.sql

Downgrading Oracle Spatial

If the Oracle system has Oracle Spatial installed, then see the Oracle Spatial User's Guide and Reference for instructions about downgrading Oracle Spatial.

Downgrading Oracle interMedia

Downgrade instructions for Oracle interMedia can be found in ORACLE_HOME/ord/im/admin/README.txt on UNIX platforms and in ORACLE_HOME\ord\im\admin\README.txt on Windows platforms.

Downgrading Oracle Visual Information Retrieval

Downgrade instructions for Oracle Visual Information Retrieval can be found in ORACLE_HOME/ord/vir/admin/README.txt on UNIX platforms and in ORACLE_HOME\ord\vir\admin\README.txt on Windows platforms.

Downgrading Oracle Text

If the Oracle system has Oracle Text installed, then complete the following steps:

Log in to the system as the owner of the release 9.2 Oracle home directory.
At a system prompt, change to the ORACLE_HOME/ctx/admin directory.
Start SQL*Plus.
Connect to the database instance as user CTXSYS.
If the instance is running, then shut it down using SHUTDOWN IMMEDIATE:
```
SQL> SHUTDOWN IMMEDIATE
```
Start up the instance in RESTRICT mode:
```
SQL> STARTUP RESTRICT
```
You may need to use the PFILE option to specify the location of your initialization parameter file.
Run the appropriate downgrade script to downgrade Oracle Text.

If you are downgrading to release 8.1.7, then run the following script:
```
SQL> @d0801070.sql
```
If you are downgrading to release 9.0.1, then run the following script:
```
SQL> @d0900010.sql
```
Shut down the instance:
```
SQL> SHUTDOWN IMMEDIATE
```
Exit SQL*Plus.

Downgrading Oracle Ultra Search

If the Oracle system has Oracle Ultra Search installed, then complete the following steps:

Start SQL*Plus.
Connect to the database instance as user WKSYS.

Issue the following SQL statement:

SQL> EXECUTE dbms_registry.downgraded('WK','9.0.1.0.0');

Exit SQL*Plus.

Downgrade the Database

Make sure your database is compatible with the release to which you are downgrading before you perform the downgrade steps in this section.

See Also:

"Remove Incompatibilities" if you have not yet removed incompatibilities

Complete the following steps to downgrade your release 9.2 database to the previous Oracle release:

Log in to the system as the owner of the release 9.2 Oracle home directory.
At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.
Start SQL*Plus.
Connect to the database instance as a user with SYSDBA privileges.
Start up the instance in MIGRATE mode:
```
SQL> STARTUP MIGRATE
```
You may need to use the PFILE option to specify the location of your initialization parameter file.
Set the system to spool results to a log file for later verification of success:
```
SQL> SPOOL downgrade.log
```
Run dold_release.sql, where old_release refers to the release to which you are downgrading. See Table 7-2 to choose the correct script. Each script provides a direct downgrade to the release specified in the "Downgrading To" column.

To run a script, enter the following:
```
SQL> @dold_release.sql
```
Table 7-2 Downgrade Scripts

Downgrading To Run Script

9.0.1

d0900010.sql

8.1.7

d0801070.sql

Note:
If the release to which you are downgrading is not included in Table 7-2, then see the README files in the new installation for the correct downgrade script to run.

The following are notes about running the script:
- You must use the version of the script included with release 9.2.
- You must run the script in the release 9.2 environment.
- You only need to run one script, even if your downgrade spans more than one release. For example, if you are downgrading to release 8.1.7, then you only need to run d0801070.sql.
If you encounter any problems when you run the script, or any of the scripts in the remaining steps, then correct the causes of the problems and rerun the script. You can rerun any of the scripts described in this chapter as many times as necessary.
Turn off the spooling of script results to the log file:
```
SQL> SPOOL OFF
```
Then, check the spool file and verify that the packages and procedures compiled successfully. You named the spool file in Step 6; the suggested name was downgrade.log. Correct any problems you find in this file and rerun the appropriate downgrade script if necessary.
Shut down the instance:
```
SQL> SHUTDOWN IMMEDIATE
```
If you are downgrading a cluster database, then shut down all instances.
Exit SQL*Plus.

Downgrading To	Run Script
9.0.1	`d0900010.sql`
8.1.7	`d0801070.sql`

If you are downgrading to release 9.0.1, then copy the following files from the release 9.2 Oracle home to the release 9.0.1 Oracle home:

Component	Copy from Release 9.2 Oracle Home	Copy to Previous Oracle Home
JServer JAVA Virtual Machine	`ORACLE_HOME``/javavm/install/jvmd901.sql`	`ORACLE_HOME``/javavm/install`
Oracle XDK for Java	`ORACLE_HOME``/xdk/admin/xmld901.sql`	`ORACLE_HOME``/xdk/admin`
Messaging Gateway	`ORACLE_HOME``/mgw/admin/mgwd901.sql`	`ORACLE_HOME``/mgw/admin`
Oracle Workspace Manager	`ORACLE_HOME``/rdbms/admin/owmd901.plb`	`ORACLE_HOME``/rdbms/admin`

If you are downgrading to release 8.1.7, then copy the following files from the release 9.2 Oracle home to the release 8.1.7 Oracle home:

Component	Copy from Release 9.2 Oracle Home	Copy to Previous Oracle Home
JServer JAVA Virtual Machine	`ORACLE_HOME``/javavm/install/jvmd817.sql`	`ORACLE_HOME``/javavm/install`
Oracle XDK for Java	`ORACLE_HOME``/xdk/admin/xmld817.sql`	`ORACLE_HOME``/oracore/admin`
Oracle Label Security	`ORACLE_HOME``/rdbms/admin/olsd817.sql`	`ORACLE_HOME``/lbac/admin`

If your operating system is UNIX, then change the following environment variables to point to the directories of the release to which you are downgrading:
- ORACLE_HOME
- PATH
- ORA_NLS33
- LD_LIBRARY_PATH
  
  Note:
  If you are downgrading a cluster database, then perform this step on all nodes in which this cluster database has instances configured.
  
  See Also:
  Your operating system-specific Oracle9i installation documents for information about setting other important environment variables on your operating system.

If your operating system is Windows, then complete the following steps:

Stop all Oracle services, including the OracleServiceSID Oracle service of the release 9.2 database, where SID is the instance name.

For example, if your SID is ORCL, then enter the following at a command prompt:
```
C:\> NET STOP OracleServiceORCL
```
See Also:
Your Administrator's Guide for Windows for information about stopping services
Delete the Oracle service at a command prompt by issuing the ORADIM command. For example, if your SID is ORCL, then enter the following command:
```
C:\> ORADIM -DELETE -SID ORCL
```

Create the Oracle service of the database to which you are downgrading at a command prompt using the ORADIM command.

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS
     -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

This syntax includes the following variables:

SID	is the same SID name as the SID of the database being downgraded.
PASSWORD	is the password for the database instance. This is the password for the user connected with `SYSDBA` privileges. The -INTPWD option is not required. If you do not specify it, then operating system authentication is used, and no password is required.
USERS	is the maximum number of users who can be granted `SYSDBA` and `SYSOPER` privileges.
ORACLE_HOME	is the Oracle home directory of the database to which you are downgrading. Ensure that you specify the full pathname with the -PFILE option, including drive letter of the Oracle home directory.

For example, if you are downgrading to release 8.1.7, if your SID is ORCL, your PASSWORD is TWxy579, the maximum number of USERS is 10, and the ORACLE_HOME directory is C:\ORANT, then enter the following command:

C:\> ORADIM -NEW -SID ORCL -INTPWD TWxy579 -MAXUSERS 10
     -STARTMODE AUTO -PFILE C:\ORANT\DATABASE\INITORCL.ORA

If you are using a server parameter file to start up the instance, or if your initialization parameter file has an SPFILE (server parameter file) entry, then complete the following steps:
1. Export the server parameter file to a traditional initialization parameter file:
```
CREATE PFILE[=pfile-name] [FROM spfile-name];
```
  The initialization parameter file will be created as a text file. In an Oracle9i Real Application Clusters environment, it will contain all parameter settings of all instances.
2. If you used the SPFILE parameter to specify a server parameter file, then change the SPFILE parameter to an IFILE parameter in the initialization parameter file used to start up the instance. Make sure the IFILE parameter points to the initialization parameter file that you exported from the server parameter file.
3. If you are using Oracle9i Real Application Clusters, then create instance-specific initialization parameter files. Remove all instance-specific parameters from the initialization parameter file that you exported from the server parameter file.
  
  You can use the IFILE parameter in each instance-specific parameter file to point to the initialization parameter file that you exported from the server parameter file.

Copy configuration files from the release 9.2 Oracle home directory to the Oracle home of the release to which you are downgrading:

Copy your parameter file from the release 9.2 Oracle home to the Oracle home of the release to which you are downgrading. By default Oracle looks for the parameter file in ORACLE_HOME/dbs on UNIX platforms and in ORACLE_HOME\database on Windows operating systems. The initialization parameter file can reside anywhere you wish, but it should not reside in the release 9.2 Oracle home.
If your parameter file has an IFILE (include file) entry and the file specified in the IFILE entry resides within the release 9.2 Oracle home directory, then copy the file specified by the IFILE entry to the Oracle home of the release to which you are downgrading. The file specified in the IFILE entry contains additional initialization parameters. After you copy this file, edit the parameter file to point to its new location.

If you have a password file that resides within the release 9.2 Oracle home directory, then move or copy the password file to the Oracle home of the release to which you are downgrading. The name and location of the password file are operating system-specific. On UNIX platforms, the default password file is ORACLE_HOME/dbs/orapwsid. On Windows operating systems, the default password file is ORACLE_HOME\database\pwdsid.ora. On both UNIX platforms and Windows operating systems, sid is your Oracle instance ID.

Note:

If you are downgrading a cluster database, then perform this step on all nodes in which this cluster database has instances configured.

If you are downgrading to release 9.0.1, then set the CLUSTER_DATABASE initialization parameter to false.
If you are downgrading to release 8.1.7, then set the PARALLEL_SERVER initialization parameter to false.

After the downgrade, you must set the appropriate initialization parameter back to true.

Add the following initialization parameters to your parameter file:
```
_SYSTEM_TRIG_ENABLED = false
JOB_QUEUE_PROCESSES = 0
AQ_TM_PROCESSES = 0
```
If you are downgrading to release 9.0.1, then add the following additional initialization parameter to your parameter file:
```
NLS_LENGTH_SEMANTICS = BYTE
```
These initialization parameters should be removed from your parameter file after the downgrade is complete.
At a system prompt, change to the ORACLE_HOME/rdbms/admin directory of the previous release.
Start SQL*Plus.

Note:
If you are downgrading to release 8.1.7, then start Server Manager. Do not start SQL*Plus.
Connect to the database instance as a user with SYSDBA privileges.
Start up the instance.

On UNIX platforms, perform the following step:
```
STARTUP RESTRICT
```
You may need to use the PFILE option to specify the location of your initialization parameter file.

On Windows operating systems, perform the following step:
```
ALTER SYSTEM ENABLE RESTRICTED SESSION;
```
Set the system to spool results to a log file for later verification of success:
```
SPOOL old_scripts.log
```
Run utlip.sql:
```
@utlip.sql
```
The utlip.sql script invalidates all existing PL/SQL modules by altering certain dictionary tables so that subsequent recompilations will happen in the format required by the database. It also reloads packages STANDARD and DBMS_STANDARD, which are necessary for any PL/SQL compilations.

See Also:
"Changing Word Size" for more information about changing word size
Run the following catalog scripts:
1. Run catalog.sql:
```
@catalog.sql
```
2. Run catproc.sql:
```
@catproc.sql
```
3. If you are downgrading to release 8.1.7, then run catrep.sql:
```
@catrep.sql
```
4. If you are downgrading a cluster database to release 9.0.1, then run catclust.sql:
```
@catclust.sql
```
  If you are downgrading a cluster database to release 8.1.7, then run catparr.sql:
```
@catparr.sql
```
5. Run any additional catalog scripts as necessary. For example, to re-create Heterogeneous Services data dictionary views, tables, and packages, run caths.sql:
```
@caths.sql
```
Shut down and restart the instance:
```
SHUTDOWN IMMEDIATE

STARTUP
```
If the previous database has JServer JAVA Virtual Machine installed, then run the appropriate downgrade script (copied to the previous Oracle home in Step 11) to complete the JServer JAVA Virtual Machine downgrade. When you run the script, replace ORACLE_HOME with the full path of the previous Oracle home directory.

If you are downgrading to release 9.0.1, then run the following script:
```
@ORACLE_HOME/javavm/install/jvmd901.sql
```
If you are downgrading to release 8.1.7, then run the following script:
```
@ORACLE_HOME/javavm/install/jvmd817.sql
```
Run the following additional scripts to reload Java classes in the previous database:
```
@ORACLE_HOME/rdbms/admin/initsoxx.sql
@ORACLE_HOME/rdbms/admin/initjms.sql
```
If you are downgrading to release 9.0.1, then run the following scripts:
```
@ORACLE_HOME/rdbms/admin/initcdc.sql
@ORACLE_HOME/rdbms/admin/initqsma.sql
@ORACLE_HOME/rdbms/admin/initsjty.sql
@ORACLE_HOME/rdbms/admin/initapcx.sql
```
If the previous database has Oracle XDK for Java installed, then run the appropriate downgrade script (copied to this directory in Step 11) to complete the Oracle XDK for Java downgrade. When you run the script, replace ORACLE_HOME with the full path of the Oracle home directory of the release to which you downgraded.

If you are downgrading to release 9.0.1, then run the following script:
```
@ORACLE_HOME/xdk/admin/xmld901.sql
```
If you are downgrading to release 8.1.7, then run the following script:
```
@ORACLE_HOME/oracore/admin/xmld817.sql
```
Shut down and restart the instance:
```
SHUTDOWN IMMEDIATE

STARTUP
```
If the previous database has Messaging Gateway installed, then run the appropriate downgrade script (copied to this directory in Step 11) to complete the Messaging Gateway downgrade. When you run the script, replace ORACLE_HOME with the full path of the Oracle home directory of the release to which you downgraded.

If you are downgrading to release 9.0.1, then run the following script:
```
@ORACLE_HOME/mgw/admin/mgwd901.sql
```
If the previous database has Oracle Workspace Manager installed, then run the appropriate downgrade script (copied to this directory in Step 11) to complete the Oracle Workspace Manager downgrade. When you run the script, replace ORACLE_HOME with the full path of the Oracle home directory of the release to which you downgraded.

If you are downgrading to release 9.0.1, then run the following script:
```
@ORACLE_HOME/rdbms/admin/owmd901.plb
```
If the previous database has Oracle Label Security installed, then run the appropriate downgrade script to complete the Oracle Label Security downgrade. When you run the script, replace ORACLE_HOME with the full path of the previous Oracle home directory.

If you are downgrading to release 9.0.1, then run the following script:
```
@ORACLE_HOME/rdbms/admin/catols.sql
```
If you are downgrading to release 8.1.7, then issue the following statements:
```
GRANT RESTRICTED SESSION TO LBACSYS;

CONNECT LBACSYS/LBACSYS

@ORACLE_HOME/lbac/admin/olsd817.sql
```
Connect to the database instance as a user with SYSDBA privileges.
```
REVOKE RESTRICTED SESSION FROM LBACSYS;
```
Run utlrp.sql. This step is optional and can be done regardless of whether there was a change in word-size.
```
@utlrp.sql
```
The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, and so on. These actions are optional; however, they ensure that the cost of recompilation is incurred during installation rather than in the future.

Oracle Corporation highly recommends running utlrp.sql.
Turn off the spooling of script results to the log file:
```
SPOOL OFF
```
Then, check the spool file and verify that the packages and procedures compiled successfully. You named the spool file in Step 21; the suggested name was old_scripts.log. Correct any problems you find in this file and rerun the appropriate script if necessary.
Shut down the instance:
```
SHUTDOWN IMMEDIATE
```
Note:
For Oracle Parallel Server, set the PARALLEL_SERVER initialization parameter to false. You can change it back to true after the downgrade operation is complete.
Exit Server Manager or SQL*Plus, depending on which you started in Step 18.
Remove the initialization parameters from your parameter file that you added in Step 16.

Your database is now downgraded.

7 Downgrading a Database Back to the Previous Oracle Release

Supported Releases for Downgrading

Perform a Full Offline Backup

Remove Incompatibilities

Checking the Compatibility Level of Your Database

Identifying Incompatibilities

Removing Release 9.2 Incompatibilities

Release 9.2 DEFAULT Partitions

Drop All DEFAULT Partitions on List Partitioned Tables

Release 9.2 Partitioning Methods

Drop All Partitioned Tables That Use Range-List Methods

Release 9.2 Streams

Drop All Streams Capture Processes

Drop All Streams Apply Processes

Release 9.2 Subpartition Templates

Drop All Subpartition Templates in Composite Partitioned Tables

LOB Retention

Drop Retention Stored in LOB Columns

Automatic Segment-Space Managed Tablespaces with LOBs

Drop LOB Columns in Automatic Segment-Space Managed Tablespaces

Removing Release 9.0.1 Incompatibilities

Tablespaces

Drop All Automatic Segment-Space Managed Tablespaces

Drop All Undo Tablespaces

Schema Objects

Drop All External Tables

Drop All Bitmap Secondary Indexes on Index-Organized Tables

Rebuild Index-Organized Tables without Mapping Tables

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

Remove Indexes With Large Keys

Release 9.0 Partitioning Methods

Drop All Partitioned Tables That Use List Methods

Hash Partitioned Index-Organized Tables

Drop All Hash Partitioned Index-Organized Tables

PDML ITL Invariants

Partitioned Index-Organized Tables with LOBs

Drop All LOB Columns in Partitioned Index-Organized Tables

Drop All Varray Columns in Partitioned Index-Organized Tables

Datatypes

Discontinue Use of Datetime and Interval Datatypes

User-Defined Datatypes

Drop User-Defined Aggregate Functions

Remove All Evolved Types and Their Dependent Types and Tables

Discontinue Use of Subtypes and Non-Final Types

SQL and PL/SQL

Discontinue Use of Pipelined Table Functions

Discontinue Use of Parallel Table Functions

Constraints and Triggers

Drop All View Constraints

Reset Database Compatibility

Downgrading Specific Components

Table 7-1 Component Downgrade Status

Removing OLAP Incompatibilities Before Downgrading to Release 8.1.7

Downgrading Oracle Spatial

Downgrading Oracle interMedia

Downgrading Oracle Visual Information Retrieval

Downgrading Oracle Text

Downgrading Oracle Ultra Search

Downgrade the Database

Table 7-2 Downgrade Scripts

7
Downgrading a Database Back to the Previous Oracle Release