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
When the parameter SERVEROUTPUT is set to
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