Upgrade Database from 11g to 19c is taking long time on CURSOR pkey_cons_csr
(Doc ID 2900732.1)
Last updated on NOVEMBER 13, 2022
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
...
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 |