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 JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 19.17.0.0.0 and laterInformation 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)
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
--------------------------------------------------------------------------------
?/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 |