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)
   )
; 

 157 total views,  2 views today

Leave a Reply

Your email address will not be published. Required fields are marked *