Upgrading Tables Dependent on Oracle-Maintained Types
Starting with Oracle Database 12c Release 2 (12.2) and later releases, you must manually upgrade user tables that depend on Oracle-Maintained types.
If your database has user tables that are dependent on Oracle-Maintained types (for example, AQ queue tables), then run the utluptabdata.sql command after the upgrade to carry out ALTER TABLE UPGRADE on any user tables affected by changes in Oracle-Maintained types. This change in behavior enables user tables to remain in READ ONLY state during an upgrade. Users are prevented from logging into applications using SYSDBA privileges (AS SYSDBA), and changing application tables that are dependent on Oracle-Maintained types.
To identify tables that you need to upgrade after the database upgrade completes, connect to the database AS SYSDBA, and run the following query:
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
SELECT DISTINCT owner, table_name
FROM dba_tab_cols
WHERE data_upgraded = 'NO'
ORDER BY 1,2;This query lists all tables that are not listed as UPGRADED. However, the utluptabdata.sql script only upgrades tables that depend on Oracle-Maintained types. If any tables are listed by the query, then run the utluptabdata.sql script to perform ALTER TABLE UPGRADE commands on dependent user tables, so that these Oracle-Maintained types are upgraded to the latest version of the type.
You must run the utluptabdata.sql script either with a user account with ALTER privileges for all of the tables dependent on Oracle-Maintained types, or with a user granted the SYSDBA system privileges, and that is logged in AS SYSDBA.
When the parameter SERVEROUTPUT is set to ON, the utluptabdata.sql script displays the names of all upgraded tables, and lists any error encountered during the table upgrade. To set the server output to ON, run the following command:
SET SERVEROUTPUT ON
@utluptabdata.sql