Как заставить Oracle expdp изменить размерность текстовых полей, определенную в NLS_LENGTH_SEMANTICS в DDL?

Метки: ,

Утилита импорта expdp, для создания таблиц собирает скрипты DDL с учетом параметра NLS_LENGTH_SEMANTICS, заданного для каждого поля таблицы. А что если при экспорте данных требуется поменять этот параметр?

Решение взято из следующего обсуждения: Is there a way to Oracle expdp not inherit char type nls semantics on DDL?

Задача

Если размерность поля измеряется в BYTE (что чаще всего встречается по-умолчанию), то неявная сборка DDL будет выглядеть следующим образом:

CREATE TABLE record (
    id NUMBER NOT NULL,
    name VARCHAR2(60 BYTE) NOT NULL,
    content VARCHAR2(4000 BYTE) NOT NULL
);

Как заставить утилиту экспорта expdp не учитывать значение поля CHAR_USED из таблицы DBA_TAB_COLUMNS? Сгенерированный DDL без явного указания NLS_LENGTH_SEMANTICS выглядел бы следующим образом: 

CREATE TABLE record (
    id NUMBER NOT NULL,
    name VARCHAR2(60) NOT NULL,
    content VARCHAR2(4000) NOT NULL
);

Такая необходимость возникает при переносе базы данных в новый экземпляр (instance) с кодировкой utf8 (AL32UTF8 charset), где параметр NLS_LENGTH_SEMANTICS имеет значение CHAR. Чтобы не возникло проблем с размерностью текстовых значений в таблицах (в кодировке utf8 для хранения символа используется 2 байта, в то время как в большинстве стандартных кодировок используется 1 байт), при импорте структуры таблицы нужно преобразовать размерности полей из BYTE в CHAR.

Решение

Вопрос об изменении NLS_LENGTH_SEMANTICS при экспорте БД возникает уже более 10 лет. До сих пор стандартной опции экспорта/импорта в утилитах oracle не появилось. Задачу можно решить вручную средствами небольшого триггера, преобразующего размерности текстовых полей по событию “после создания таблицы” (after create on database when SYS.DICTIONARY_OBJ_TYPE = ‘TABLE’).

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;
/
Close Menu