Skip to content

Multiple Database Block Sizes and the Buffer Cache

December 18, 2013

Import Failing with Error:ORA-01450: maximum key length (6398) exceeded
———————————————————————————————————-

impdp ‘/ as sysdba’ DUMPFILE=malu.dmp DIRECTORY=DATA_PUMP_DIR SCHEMAS=malu logfile=impdp_d041.log  schemas=malu remap_tablespace=AMS:AMS_DAT

....
....
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-01450: maximum key length (6398) exceeded
Failing sql is:
CREATE INDEX "MALU"."ENOIDX" ON "MALU"."TEMP" ("EMPNO", "ENAME", "DEPTNO") PCTFREE 10 INITRANS 2 MAXTRANS 175  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 
1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "AMS_DAT" 
PARALLEL 1 
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"MALU"."ENOIDX" creation failed
....
....

SQL> desc malu.temp;
Name                                      Null?    Type
—————————————– ——– ——————-
EMPNO                                              VARCHAR2(4000)
ENAME                                              VARCHAR2(4000)
DEPTNO                                             VARCHAR2(4000)

SQL> select * from malu.temp;

EMPNO      ENAME      DEPTNO
———- ———- ———-
1          sam        10
2          tom        20

SQL> create index malu.enoidx on malu.temp (EMPNO,ENAME,DEPTNO) tablespace ams_dat;
create index malu.enoidx on malu.temp (EMPNO,ENAME,DEPTNO) tablespace ams_dat
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

ORA-01450 and Maximum Key Length – How it is Calculated (Doc ID 136158.1)
———————————————————————————————————————–
When creating an Index, the total length of the index cannot exceed a
certain value.  This value depends primarily on the DB_BLOCK_SIZE.  If
an attempt is made to create an index larger than the Maximum value, an
ORA-1450 is raised:

ORA-01450 maximum key length (758) exceeded  ->(2K Block)
ORA-01450 maximum key length (1578) exceeded ->(4K block)
ORA-01450 maximum key length (3218) exceeded ->(8K Block)
ORA-01450 maximum key length (6498) exceeded ->(16K Block)

Extracted index script on source looks as below
———————————————————————————

create index malu.enoidx on malu.temp (EMPNO,ENAME,DEPTNO) tablespace AMS;

***** Here tablespace name used is AMS which is getting remapped to AMS_DAT in target.

################################################################################
        Source :
################################################################################

Check tablespace name for the Index:
===========================================

SQL> select OWNER,INDEX_NAME,TABLESPACE_NAME from dba_indexes where owner=’MALU’ and index_name=’ENOIDX’;

OWNER                          INDEX_NAME                     TABLESPACE_NAME
—————————— —————————— ———————–
MALU                           ENOIDX                         AMS

Check the block size for the tablespace.
============================================
SQL> select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces where TABLESPACE_NAME=’AMS’;

TABLESPACE_NAME                BLOCK_SIZE
—————————— ———-
AMS                   16384

SQL> show parameter DB_16K_CACHE_SIZE

NAME                                 TYPE        VALUE
———————————— ———– ————————-
db_16k_cache_size                    big integer 96M

Source & Target both have same default block size – db_block_size
=======================================================
SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
———————————— ———– ——–
db_block_size                        integer     8192
SQL>

################################################################################
       Target:
################################################################################

SQL> select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces where TABLESPACE_NAME=’AMS_DAT’;

TABLESPACE_NAME                BLOCK_SIZE
—————————— ———-
AMS_DAT                               8192   ==> Create index was failing, as index was looking for 16K BLK Size.

Recreate tablespace with 16K blocksize
========================================

SQL> drop tablespace AMS_DAT including contents and datafiles;

Tablespace dropped.

SQL> CREATE TABLESPACE AMS_DAT DATAFILE
‘C:\ORACLE\ORADATA\MGD041\AMS_DAT_01.DBF’ size 20M
LOGGING ONLINE PERMANENT BLOCKSIZE 16384;
CREATE TABLESPACE AMS_DAT_01 DATAFILE
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes  ==> this fails, as supporting DB_16K_CACHE_SIZE was not set.

SQL> show parameter DB_16K_CACHE_SIZE

NAME                                 TYPE        VALUE
———————————— ———– —————————
db_16k_cache_size                    big integer 0

SQL> alter system set DB_16K_CACHE_SIZE=96M ;

System altered.

SQL> show parameter DB_16K_CACHE_SIZE

NAME                                 TYPE        VALUE
———————————— ———– ————————-
db_16k_cache_size                    big integer 96M

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
———————————— ———– ——–
db_block_size                        integer     8192
SQL>

SQL> CREATE TABLESPACE AMS_DAT DATAFILE
‘C:\ORACLE\ORADATA\MGD041\AMS_DAT_01.DBF’ size 20M
LOGGING ONLINE PERMANENT BLOCKSIZE 16384;

Tablespace created.

SQL> select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces where TABLESPACE_NAME=’AMS_DAT’;

TABLESPACE_NAME                BLOCK_SIZE
—————————— ———-
AMS_DAT                             16384

SQL> create index malu.enoidx on malu.temp (EMPNO,ENAME,DEPTNO) tablespace AMS_DAT;

Index created.

SQL> select OWNER,INDEX_NAME,TABLESPACE_NAME from dba_indexes where owner=’MALU’ and index_name=’ENOIDX’;

OWNER                          INDEX_NAME                     TABLESPACE_NAME
—————————— —————————— ——————————
MALU                           ENOIDX                         AMS_DAT

References:
ORA-01450 Error on Create Index (Doc ID 747107.1)

Advertisements

From → ORA- Issues, Oracle

One Comment

Trackbacks & Pingbacks

  1. Oracle Issues [ORA-] | Maleshg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: