0

expdp, by default, generated internal DDL to create table observing current NLS_LENGTH_SEMANTICS of each column.

If a column as BYTE (the default and most used) NLS_LENGTH_SEMANTICS, the internal DDL would be like:

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3 BYTE)  NOT NULL,
    Name                  VARCHAR2(60 BYTE) NOT NULL,
    Abkuerzung            VARCHAR2(5 BYTE)  NOT NULL 
);

But I want an option to force expdp to not get/inherit the CHAR_USED value from DBA_TAB_COLUMNS, resulting into the following DDL:

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3)  NOT NULL,
    Name                  VARCHAR2(60) NOT NULL,
    Abkuerzung            VARCHAR2(5)  NOT NULL
);

The reason is because the target database has a AL32UTF8 charset and NLS_LENGTH_SEMANTICS set as CHAR. So I want the tables be converted from BYTE to CHAR (accordingly to NLS_LENGTH_SEMANTICS parameter value).

1
3

I made a quick and dirty solution which worked for me today (Oracle 12.1), no reason to wait for the next 11 years.

create or replace trigger T_mutate_len_semantics after create on database
when (SYS.DICTIONARY_OBJ_TYPE = 'TABLE' and SYS.DICTIONARY_OBJ_OWNER not in ('SYS','SYSTEM'))
 declare
    stmt varchar2(2000);
begin
  --debug 
  dbms_system.ksdwrt(2,'T_mutate_len_semantics: After creating table '||SYS.DICTIONARY_OBJ_NAME);
  for r in (select column_name, data_length, data_type
            from dba_tab_columns
            where table_name = SYS.DICTIONARY_OBJ_NAME
              and owner = SYS.DICTIONARY_OBJ_OWNER
              and data_type in ('VARCHAR2','CHAR')
              and DATA_LENGTH = CHAR_LENGTH
            )
    loop
        stmt := 'alter table '||SYS.DICTIONARY_OBJ_OWNER||'.'||SYS.DICTIONARY_OBJ_NAME||' modify ('||r.column_name||' '||r.data_type||'('||r.data_length||' CHAR))';
        dbms_system.ksdwrt(2,'T_mutate_len_semantics: '||stmt);
        execute immediate stmt;
    end loop;
end;
/
1

This feature was requested 11 years ago, and it is still not implemented:

Bug 5034478 : ENH: ADD PARAMETER TO IMPDP TO OVERWRITE NLS_LENGTH_SEMANTICS

At the moment there is no way of moving all data, or single columns/tables from BYTE to CHAR with expdp/impdp.

2
  • Thanks a lot! I think would be useless I ask for this feature too. :-) Sep 6 '17 at 17:24
  • 1
    You could always pre-create tables (with CHAR modifier) and import data only.
    – Raj
    Sep 6 '17 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.