Column Defined With BYTE Length Semantics Is Not Preserved Across DataPump Export/Import

(Doc ID 563612.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version 10.1.0.2 to 10.2.0.4 [Release 10.1 to 10.2]
Information in this document applies to any platform.

Symptoms

In source database with multibyte character set (like UTF8, AL32UTF8, etc) and NLS_LENGTH_SEMANTICS set to CHAR you created a table with a column with BYTE length semantics.

After using DataPump export/import (EXPDP/IMPDP) the length semantics of BYTE column in the table changed to CHAR. This is demonstrated by the next example:

-- Source database, multibyte character set and
-- NLS_LENGTH_SEMANTICS set to CHAR
connect / as sysdba
show parameter semantics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      char

create or replace directory tmp as '/tmp';

create user test identified by test default tablespace users temporary tablespace temp;
grant connect, resource to test;
grant read, write on directory tmp to test;

connect test/test
create table test_nls
(
   col_char    varchar2(10 CHAR),
   col_byte    varchar2(10 BYTE),
   col_default varchar2(10)
);

-- Verify the column definition
connect / as sysdba
col column_name format a15
col data_type format a10

select column_name,
       data_type,
       data_length,
       char_length,
       char_used
from   dba_tab_cols
where  owner = 'TEST' and
       table_name = 'TEST_NLS';

COLUMN_NAME     DATA_TYPE  DATA_LENGTH CHAR_LENGTH C
--------------- ---------- ----------- ----------- -
COL_DEFAULT     VARCHAR2            40          10 C
COL_BYTE        VARCHAR2            10          10 B <= BYTE length semantics
COL_CHAR        VARCHAR2            40          10 C

-- Export the table
#> expdp test/test directory=tmp dumpfile=test_nls.dmp tables=test_nls

-- Target database, multibyte character set and
-- NLS_LENGTH_SEMANTICS set to CHAR
#> impdp test/test directory=tmp dumpfile=test_nls.dmp tables=test_nls

COLUMN_NAME     DATA_TYPE  DATA_LENGTH CHAR_LENGTH C
--------------- ---------- ----------- ----------- -
COL_DEFAULT     VARCHAR2            40          10 C
COL_BYTE        VARCHAR2            40          10 C <= CHAR length semantics
COL_CHAR        VARCHAR2            40          10 C

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