My Oracle Support Banner

Upgrade Database from 11g to 19c is taking long time on CURSOR pkey_cons_csr (Doc ID 2900732.1)

Last updated on JULY 20, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 19.11.0.0.0 to 19.12.0.0.0 [Release 19]
Information in this document applies to any platform.

Symptoms

Upgrade Database from 11.2.0.4 to 19.12 is taking too much time in the below stage of the upgrade.

From "catupgrd0.log":

...
18:03:02 SQL>
18:03:02 SQL> Rem *************************************************************************
18:03:02 SQL> Rem END BUG 564110
18:03:02 SQL> Rem *************************************************************************
18:03:02 SQL>
18:03:02 SQL> Rem *************************************************************************
18:03:02 SQL> Rem BEGIN Changes for Bug 31942136
18:03:02 SQL> Rem *************************************************************************
18:03:02 SQL>
18:03:02 SQL> DECLARE
18:03:02 2 ddlstr varchar2(32767);
18:03:02 3
18:03:02 4 CURSOR pkey_cons_csr IS
18:03:02 5 select u.name user_name, o.name tbl_name, cd.enabled
18:03:02 6 from cdef$ cd, ccol$ cc, col$ cl, obj$ o, user$ u
18:03:02 7 where cl.obj# = cd.obj#
18:03:02 8 and cc.intcol# = cl.intcol#
18:03:02 9 and cd.con# = cc.con#
18:03:02 10 and cd.type# = 2
18:03:02 11 and bitand(cl.property, 1073741824) = 1073741824
18:03:02 12 and cd.obj# = o.obj#
18:03:02 13 and o.owner# = u.user#
18:03:02 14 and not exists (select * from recyclebin$ rb where rb.obj# = cd.obj#)
18:03:02 15 order by 1, 2;
18:03:02 16
18:03:02 17 BEGIN
18:03:02 18
18:03:02 19 FOR pkey_rec in pkey_cons_csr
18:03:02 20 LOOP
18:03:02 21 IF pkey_rec.enabled is NULL
18:03:02 22 THEN
18:03:02 23 ddlstr := 'ALTER TABLE ' ||
18:03:02 24 DBMS_ASSERT.ENQUOTE_NAME(pkey_rec.user_name, false) || '.' ||
18:03:02 25 DBMS_ASSERT.ENQUOTE_NAME(pkey_rec.tbl_name, false) ||
18:03:02 26 ' DISABLE PRIMARY KEY';
18:03:02 27 ELSE
18:03:02 28 ddlstr := 'ALTER TABLE ' ||
18:03:02 29 dbms_assert.enquote_name(pkey_rec.user_name, false) || '.' ||
18:03:02 30 dbms_assert.enquote_name(pkey_rec.tbl_name, false) ||
18:03:02 31 ' ENABLE PRIMARY KEY';
18:03:02 32 END IF;
18:03:02 33
18:03:02 34 EXECUTE IMMEDIATE ddlstr;
18:03:02 35 END LOOP;
18:03:02 36 END;
18:03:02 37 /

PL/SQL procedure successfully completed.

Elapsed: 07:13:58.26
...

Upgrade was completed successfully but takes over 7 hours on the above statement.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.