As I learned from a customer this week, this patch is a must have when you use Partitioning and you attempt to upgrade to Oracle 19.9.0 or earlier. So please apply patch 31088341 before Upgrade to prevent ORA-1403 happening.
What is the issue?
This applies to all 19c upgrades at least until 19.9.0. You may see this error pattern in catupgrd0.log:
==Error from catupgrd0.log=== ================================================================= 20:34:11 SQL> Rem BEGIN bug 28757685: repair tabpart$ 20:34:11 SQL> Rem ========================================================================= 20:34:11 SQL> /* bug 28757685: tabpart$.hiboundval and tabpart$.hiboundlen are incorrect 20:34:11 SQL> * when tables are autolist partitioned and partitioning columns are CHAR. 20:34:11 SQL> */ 20:34:11 SQL> DECLARE 20:34:11 2 bhiboundval RAW(32767); 20:34:11 3 newhiboundval VARCHAR2(32767); 20:34:11 4 pos BINARY_INTEGER; 20:34:11 5 len BINARY_INTEGER; 20:34:11 6 multicol BOOLEAN; 20:34:11 7 numvals BINARY_INTEGER; 20:34:11 8 posnum BINARY_INTEGER; 20:34:11 9 typenum BINARY_INTEGER; 20:34:11 10 charsetform BINARY_INTEGER; 20:34:11 11 BEGIN 20:34:11 12 FOR alp IN (SELECT tp.obj#, tp.bo#, .. .. 20:34:11 129 WHEN OTHERS THEN 20:34:11 130 DBMS_SYSTEM.ksdwrt(DBMS_SYSTEM.trace_file, 20:34:11 131 'error when repairing tabpart$'); 20:34:11 132 RAISE; 20:34:11 133 END; 20:34:11 134 / DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 132 ORA-06512: at line 75 ORA-06512: at line 75 =============
At first, your database is supposed to work fine. Hence, no panic. But you may get issues when you try to export a multi-value list partitioned table.
- ORA 1403 during upgrade script for multi column list partitioned tables with pkey of either char/binary data types.
This fix will exchange the a18.sql in ?/rdbms/admin. It is tagged as Bug 31088341 – A18.SQL NEEDS CHANGES IN THE REPAIR TABPART$ PORTION ADDED AS PART OF FIX#28757685.
The issue is a follow-up to Bug 30084975 – 19C UPGRADE FAILS WITH ERROR “ORA-01403: NO DATA FOUND” which is a follow-up to an incorrect fixup script delivered for Bug 28757685 – PDML:ORA-14308 WHEN AUTO LIST PARTITIONED TABLE DBMS_METADATA.GET_DDL GENERATED DDL.
How to prevent it?
At least until Oracle Database RU 19.9.0 you need to apply this patch for Bug 31088341 which exchanges the a18.sql file in ?/rdbms/admin. Hopefully in a future RU, this fix will be included. I will update the blog post then.
More Information and Links
- Bug 31088341 – A18.SQL NEEDS CHANGES IN THE REPAIR TABPART$ PORTION ADDED AS PART OF FIX#28757685
- Bug 30084975 – 19C UPGRADE FAILS WITH ERROR “ORA-01403: NO DATA FOUND”
- Bug 28757685 – PDML:ORA-14308 WHEN AUTO LIST PARTITIONED TABLE DBMS_METADATA.GET_DDL GENERATED DDL
- MOS Note: 2579106.1 – DATABASE UPGRADE TO 19C REPORTS ERROR “ORA-01403: NOT DATA FOUND”