My Oracle Support Banner

19.xx : datapatch fails with "ORA-01450: maximum key length (xxxx) exceeded" when running bug_29887840_apply.sql (Doc ID 2970850.1)

Last updated on OCTOBER 15, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 19.17.0.0.0 and later
Information in this document applies to any platform.

Symptoms

While executing Datapatch , after applying 19.20 DB RU, below errors are generated -

Patch 35320081 apply: WITH ERRORS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/35320081/25314491/35320081_apply_ORCL_2023Aug09_00_59_29.log (errors)
 -> Error at line 2715: script rdbms/admin/backport_files/bug_29887840_apply.sql
     - ORA-01450: maximum key length (3118) exceeded
     - ORA-06512: at line 158
 ru_logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/35320081/25314491/35320081_ru_apply_ORCL_2023Aug09_00_59_26.log (errors)

From the APPLY Log, the block generating the error is-

INSTALL_FILE
--------------------------------------------------------------------------------
?/rdbms/admin/backport_files/bug_29887840_apply.sql

SQL> ALTER SESSION SET EVENTS '55622 TRACE NAME CONTEXT FOREVER, LEVEL 2';

Session altered.

SQL>
SQL> DECLARE
 2    CURSOR TAB_CUR IS
 3      SELECT OBJ# FROM SYS_FBA_TRACKEDTABLES;
 4  
 5    /* Fetches all the columns associated with the index ordered on col pos */
 6    CURSOR CHK_IDX_NAME_CUR (P_TAB_NAME DBA_IND_COLUMNS.TABLE_NAME%TYPE,
 7      P_IDX_NAME DBA_IND_COLUMNS.INDEX_NAME%TYPE) IS
 8      SELECT COLUMN_NAME FROM DBA_IND_COLUMNS
 9      WHERE TABLE_NAME=P_TAB_NAME AND INDEX_NAME=P_IDX_NAME
10      ORDER BY COLUMN_POSITION;
11  
12    /* This cursor is used to check if a constraint with given name exists */
13    CURSOR CNST_CHK_CUR (P_CNST_NAME DBA_CONSTRAINTS.CONSTRAINT_NAME%TYPE) IS
14      SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS
15      WHERE CONSTRAINT_NAME=P_CNST_NAME;
16  
17    /* Returns the base table name associated with tcrv table */
18    CURSOR BASE_TABLE_NAME_CUR (P_OBJN DBA_OBJECTS.OBJECT_ID%TYPE) IS
19      SELECT OBJECT_NAME, OWNER FROM DBA_OBJECTS
20      WHERE OBJECT_ID=P_OBJN;
21  
22    /* Returns the owner name of the index */
23    CURSOR IDX_OWNER_NAME_CUR (P_IDX_NAME DBA_INDEXES.INDEX_NAME%TYPE) IS
24      SELECT OWNER FROM DBA_INDEXES
25      WHERE INDEX_NAME=P_IDX_NAME;
26  
27    /* Gets names of constraints to be dropped on tcrv table */
28    CURSOR DROP_CONSTRAINT_CUR (P_TAB_NAME DBA_CONSTRAINTS.TABLE_NAME%TYPE,
29      P_CNST_NAME DBA_CONSTRAINTS.CONSTRAINT_NAME%TYPE) IS
30      SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS
31      WHERE TABLE_NAME=P_TAB_NAME AND CONSTRAINT_NAME != P_CNST_NAME;
32  
33    /* Gets names of indexes to be dropped on tcrv table */
34    CURSOR DROP_IDX_CUR (P_TAB_NAME DBA_INDEXES.TABLE_NAME%TYPE,
35      P_IDX_NAME DBA_INDEXES.INDEX_NAME%TYPE) IS
36      SELECT INDEX_NAME FROM DBA_INDEXES
37      WHERE TABLE_NAME=P_TAB_NAME AND INDEX_NAME != P_IDX_NAME;
38  
39    CURSOR TABLE_EXISTS_CUR (P_TAB_NAME DBA_TABLES.TABLE_NAME%TYPE) IS
40    SELECT COUNT(*) FROM DBA_TABLES
41    WHERE TABLE_NAME=P_TAB_NAME;
42  
43    v_tab_objn SYS_FBA_TRACKEDTABLES.OBJ#%TYPE;
44  
45    v_ind_col_name DBA_IND_COLUMNS.COLUMN_NAME%TYPE;
46  
47    v_base_table_name DBA_OBJECTS.OBJECT_NAME%TYPE;
48  
49    v_tcrv_table_name DBA_IND_COLUMNS.TABLE_NAME%TYPE;
50  
51    /* index owner is also the owner of tcrv table, constraint, base table etc */
52    v_base_tab_owner_name DBA_INDEXES.OWNER%TYPE;
53    v_tcrv_idx1_name DBA_IND_COLUMNS.INDEX_NAME%TYPE;
54    v_tcrv_idx_name DBA_IND_COLUMNS.INDEX_NAME%TYPE;
55  
56    v_idx_exists BOOLEAN := FALSE;
57    v_create_idx BOOLEAN := TRUE;
58  
59    v_cnst_name ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
60  
61    v_table_count NUMBER := 0;
62  BEGIN
63    OPEN TAB_CUR;
64    LOOP
65      v_idx_exists := FALSE;
66      v_create_idx := TRUE;
67      v_table_count := 0;
68  
69      /* fetch each objn */
70      FETCH TAB_CUR INTO v_tab_objn;
71      EXIT WHEN TAB_CUR%NOTFOUND;
72  
73  
74      /* skip the current iteration if we are dealing with the dummy entry */
75      CONTINUE WHEN v_tab_objn = -1;
76  
77      OPEN BASE_TABLE_NAME_CUR(v_tab_objn);
78      FETCH BASE_TABLE_NAME_CUR INTO v_base_table_name, v_base_tab_owner_name;
79      CLOSE BASE_TABLE_NAME_CUR;
80  
81      /* create tcrv name and find indexes on that tcrv for the given objn */
82      v_tcrv_table_name := ('SYS_FBA_TCRV_' || to_char(v_tab_objn));
83  
84      OPEN TABLE_EXISTS_CUR(v_tcrv_table_name);
85      FETCH TABLE_EXISTS_CUR INTO v_table_count;
86      CLOSE TABLE_EXISTS_CUR;
87  
88      /* skip when table doesn't exist */
89      CONTINUE WHEN v_table_count != 1;
90  
91      /* check for first index, where clause on index name and number of columns
92       * if the index is present then check the columns and order
93       */
94      v_tcrv_idx1_name := ('SYS_FBA_TCRV_IDX1_' || to_char(v_tab_objn));
95  
96      /* get the owner name of the index */
97      OPEN IDX_OWNER_NAME_CUR(v_tcrv_idx1_name);
98      FETCH IDX_OWNER_NAME_CUR INTO v_base_tab_owner_name;
99      /* If there are no indexes on the TCRV table, then create an index */
100      IF IDX_OWNER_NAME_CUR%ROWCOUNT = 0 THEN
101        CLOSE IDX_OWNER_NAME_CUR;
102        GOTO create_index;
103      END IF;
104      CLOSE IDX_OWNER_NAME_CUR;
105  
106      OPEN CHK_IDX_NAME_CUR(v_tcrv_table_name, v_tcrv_idx1_name);
107  
108      v_idx_exists := TRUE;
109  
110      FETCH CHK_IDX_NAME_CUR INTO v_ind_col_name;
111      IF v_ind_col_name != 'RID' THEN
112        CLOSE CHK_IDX_NAME_CUR;
113        GOTO create_index;
114      END IF;
115      FETCH CHK_IDX_NAME_CUR INTO v_ind_col_name;
116      IF v_ind_col_name != 'STARTSCN' THEN
117        CLOSE CHK_IDX_NAME_CUR;
118        GOTO create_index;
119      END IF;
120      FETCH CHK_IDX_NAME_CUR INTO v_ind_col_name;
121      IF v_ind_col_name != 'ENDSCN' THEN
122        CLOSE CHK_IDX_NAME_CUR;
123        GOTO create_index;
124      END IF;
125  
126      IF CHK_IDX_NAME_CUR%ROWCOUNT = 3 THEN
127        v_create_idx := FALSE;
128      END IF;
129      CLOSE CHK_IDX_NAME_CUR;
130  
131      <>
132  
133      v_cnst_name:=('SYS_FBA_TCRV_IDX1_' || to_char(v_tab_objn));
134  
135      /* If the index name exists and cols are not same, then drop and recreate
136       * The index may have been created because of a constraint, so drop the
137       * constraint and recreate it again
138       */
139      IF v_create_idx THEN
140        IF v_idx_exists THEN
141          /* constraint name is same as index name */
142          OPEN CNST_CHK_CUR(v_cnst_name);
143          FETCH CNST_CHK_CUR INTO v_cnst_name;
144          /* check if there exists a constraint with the name  */
145          IF CNST_CHK_CUR%ROWCOUNT = 1 THEN
146            /* drop the constraint and recreate it again with correct columns */
147            EXECUTE IMMEDIATE 'ALTER TABLE ' || v_base_tab_owner_name || '.'
148                  || v_tcrv_table_name || ' DROP CONSTRAINT ' || v_cnst_name;
149          ELSE
150            /* drop the index and create constraint with correct columns */
151            EXECUTE IMMEDIATE 'DROP INDEX ' || v_base_tab_owner_name || '.'
152                  || v_tcrv_idx1_name;
153          END IF; /* End of check rowcount */
154          CLOSE CNST_CHK_CUR;
155        END IF; /* end of v_idx_exists */
156  
157        /* Add constraint with correct set of columns */
158        EXECUTE IMMEDIATE 'ALTER TABLE ' || v_base_tab_owner_name || '.'
159              || v_tcrv_table_name || ' ADD CONSTRAINT ' || v_cnst_name
160              || ' UNIQUE (RID, STARTSCN, ENDSCN) USING INDEX';
161  
162      END IF; /* end of v_create_idx */
163  
164      /* First drop all the indexes associated with constraints */
165      OPEN DROP_CONSTRAINT_CUR(v_tcrv_table_name, v_cnst_name);
166      LOOP
167      FETCH DROP_CONSTRAINT_CUR INTO v_cnst_name;
168      EXIT WHEN DROP_CONSTRAINT_CUR%NOTFOUND;
169      EXECUTE IMMEDIATE 'ALTER TABLE ' || v_base_tab_owner_name || '.'
170            || v_tcrv_table_name || ' DROP CONSTRAINT ' || v_cnst_name;
171      END LOOP;
172      CLOSE DROP_CONSTRAINT_CUR;
173  
174      /* drop all other indexes and constraints on tcrv */
175      OPEN DROP_IDX_CUR(v_tcrv_table_name, v_tcrv_idx1_name);
176      LOOP
177        FETCH DROP_IDX_CUR INTO v_tcrv_idx_name;
178        EXIT WHEN DROP_IDX_CUR%NOTFOUND;
179        EXECUTE IMMEDIATE 'DROP INDEX ' || v_base_tab_owner_name || '.'
180              || v_tcrv_idx_name;
181      END LOOP;
182      CLOSE DROP_IDX_CUR;
183  
184    END LOOP;
185    CLOSE TAB_CUR;
186  END;
187  /
DECLARE
*
ERROR at line 1:
ORA-01450: maximum key length (3118) exceeded
ORA-06512: at line 158






Changes

 Tracing the bug_29887840_apply.sql, as below -

 

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
Changes
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.