ORA-01450: maximum key length (6398) exceeded while doing impdp
Today while performing logical import of data(impdp) , I faced this unique error , which was failing to create index on the imported tables column’s.
The whole errors looks like below
Failing sql is:
CREATE INDEX "DECON"."IDX_RSD_2" ON "DECON"."DECON_SOURCE_DATA_2" ("RSD_COL_1", "RSD_COL_2") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINC
REASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DECON_DATA" PARALLEL 1
ORA-39083: Object type INDEX:"RECON"."IDX_ST_SK_10" failed to create with error:
ORA-01450: maximum key length (6398) exceeded
The error was somewhat misleading because the column size defined in the table was varchar2(4000) . So , I was thinking how come 6,398 bytes is exceeded when there is only 4000 char defined during the table creation .
When checked , Our database was having AL32UTF8 character set and version was 12.2 , so in 12.2 , each character can take up to to 4 bytes .
So , 4*4000=16000 Bytes was ideally the length , that’s why it was failing when impdp was creating index .
So why I was getting this error , while my both source and destination database version was same (12.2) ?
After some search , I found that my database source and target block size was different.
source db_block_size = 16k
destination db_block_size = 8k
That’s why in source the index was there created without any issue on column of length varchar2(4000).
Now , what we can do to have those indexes on our target database ?
In first place itself we must try to avoid this error , choosing using smaller varchar2 length , like varchar2(100),varchar2(500) while defining the tables , unless it is mandatory to have a larger length for columns.
One solution is to decrease the maximum length of strings for your column. depending upon application requirement, you need to check that you can use a smaller limit.
( NOTE: Don’t do this step without consulting Application team, because it can lead to disastrously situation )
If smaller value is acceptable to application team , you can shrink the table column value using
ALTER TABLE DECON_SOURCE_DATA_2 MODIFY ( RSD_COL_1 varchar2(1000) , RSD_COL_2 varchar2(1000) ) ;
673 total views, 1 views today