Import a Cluster Table From Single Byte to Multibyte and Changing NLS_LENGTH_SEMANTICS From BYTE to CHAR Fails With ORA-1753 (Doc ID 756180.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7
This problem can occur on any platform.

Symptoms

At source (single byte database) you created a cluster and a table within this cluster:

connect / as sysdba

drop user test_clu cascade;
purge dba_recyclebin;

create user test_clu identified by test_clu default tablespace users temporary tablespace temp;
grant connect, resource to test_clu;

connect test_clu/test_clu
create cluster clu
(
   clu_key varchar2(10)
);

create index clu_idx on cluster clu;

create table clu_tab
(
   clu_key varchar2(10) not null,
   col1    varchar2(10) not null,
   col2    date
) cluster clu (clu_key);

insert into clu_tab values ('1', 'ROW 1', sysdate);
insert into clu_tab values ('2', 'ROW 2', sysdate + 10);
commit; 

Then you exported the user using original export utility (exp) with command line:

#> exp test_clu/test_clu file=test_clu.dmp owner=test_clu

At source (multibyte database) you pre-created the cluster and the table with NLS_LENGTH_SEMANTICS set to CHAR:

connect / as sysdba

drop user test_clu cascade;
purge dba_recyclebin;

create user test_clu identified by test_clu default tablespace users temporary tablespace temp;
grant connect, resource to test_clu;

connect test_clu/test_clu
create cluster clu
(
   clu_key varchar2(10 char)
);

create index clu_idx on cluster clu;

create table clu_tab
(
   clu_key varchar2(10 char) not null,
   col1    varchar2(10 char) not null,
   col2    date
) cluster clu (clu_key); 

Now you try to import the dump from above with command:

#> imp test_clu/test_clu file=test_clu.dmp full=y ignore=y

This fails with:

...
import server uses AL32UTF8 character set (possible charset conversion)
. importing TEST_CLU's objects into TEST_CLU
IMP-00017: following statement failed with ORACLE error 1753:
"CREATE TABLE "CLU_TAB" ("CLU_KEY" VARCHAR2(10) NOT NULL ENABLE, "COL1" VARC"
"HAR2(10) NOT NULL ENABLE, "COL2" DATE) CLUSTER "CLU" ("CLU_KEY")"
IMP-00003: ORACLE error 1753 encountered
ORA-01753: column definition incompatible with clustered column definition
Import terminated successfully with warnings.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms