Sample Header Ad - 728x90

Datapump metadata-only import changes datalength when importing with conversion

1 vote
1 answer
325 views
Datapump metadata only import changes datalength when importing with conversion: (export done in WE8ISO8859P15 and import done in AL32UTF8 character set and AL16UTF16 NCHAR character set) Example table: Source-SYSTEM (WE8ISO8859P15): select OWNER,TABLE_NAME,COLUMN_NAME,DATA_LENGTH from dba_tab_columns where TABLE_NAME='STRING'; OWNER TABLE_NAME COLUMN_NAME DATA_LENGTH --------------- -------------------- -------------------- ----------- SCHEMA_NAME STRING DE 2000 Target-SYSTEM (AL16UTF16): select OWNER,TABLE_NAME,COLUMN_NAME,DATA_LENGTH from dba_tab_columns where TABLE_NAME='STRING'; OWNER TABLE_NAME COLUMN_NAME DATA_LENGTH --------------- -------------------- -------------------- ----------- SCHEMA_NAME STRING DE 4000 The data length of varchar2(char 2000) has been changed to varchar2(char 4000) automatically. What is the rule set for this behavior? Is this a documented behavior? Edit: The issue seem to be not related to datapump but to the conversion from single character to multicharacter set: Source-SYSTEM (WE8ISO8859P15): create table test(name varchar2(50), name2 varchar2(5 char), name3 clob); INSERT INTO test VALUES('Susanne','Test','Hi This is Row one'); select owner,TABLE_NAME, COLUMN_NAME, DATA_LENGTH, CHAR_LENGTH, data_type,char_used FROM ALL_TAB_COLUMNS where TABLE_NAME='TEST'; SCHEMA_NAME TEST NAME 50 50 VARCHAR2 B SCHEMA_NAME TEST NAME2 5 5 VARCHAR2 C SCHEMA_NAME TEST NAME3 4000 0 CLOB Target-SYSTEM (AL16UTF16): create table test(name varchar2(50), name2 varchar2(5 char), name3 clob); INSERT INTO test VALUES('Susanne','Test','Hi This is Row one'); select owner,TABLE_NAME, COLUMN_NAME, DATA_LENGTH, CHAR_LENGTH, data_type,char_used FROM ALL_TAB_COLUMNS where TABLE_NAME='TEST'; SCHEMA_NAME TEST NAME 50 50 VARCHAR2 B SCHEMA_NAME TEST NAME2 20 5 VARCHAR2 C SCHEMA_NAME TEST NAME3 4000 0 CLOB Note: In old DB (character set = WE8ISO8859P15) DATA_LENGTH=1*CHAR_LENGTH for all columns having VARCHAR2 DATA_TYPE (indipendent of CHAR_USED). In new DB (with UNICODE character set AL32UTF8) DATA_LENGTH=4*CHAR_LENGTH (or max-value = 4000) for all columns having VARCHAR2 DATA_TYPE and CHAR_USED=C.
Asked by r0tt (1078 rep)
Aug 27, 2021, 06:25 AM
Last activity: Aug 27, 2021, 02:38 PM