1

Topic: How to change NLS_LENGTH_SEMANTICS at usage Data Pump Import

Hello, dear colleagues!
I work with Autonomous Data Warehouse Cloud.
(Oracle Database 18c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
On Oracle Exadada Database Machine)
I load the data by means of Data Pump Import.
Initial basis in coding NLS_CHARACTERSET = CL8MSWIN1251.
Target basis (PDB) in coding NLS_CHARACTERSET = AL32UTF8.
At import I receive type errors:

 KUP-11007: conversion error loading table "MY_TAB_OWNER". "MY_TAB_NAME"
ORA-12899: value too large for column MY_COLUMN_NAME (actual: 175, maximum: 160) 

I understand that the problem is connected by that the table forms by utility Data Pump Import with instructions

CREATE TABLE "MY_TAB_OWNER". "MY_TAB_NAME" (
...
MY_COLUMN (160>>> BYTE <<<);
...
);

And at loading import tries to interpose the data into this column with Russian letters,
Which have in bytes the big length.
Normal way - to increase length of a column and then the data in it are located.
But it is necessary for me it (is desirable) to make it automatically at import of the data.
I changed at level PDB

ALTER SYSTEM SET NLS_LENGTH_SEMANTICS = CHAR SCOPE=BOTH;

It did not help, since from an export file at table creation undertakes explicit BYTE.
Parameters of import which are responsible NLS_LENGTH_SEMANTICS I did not find.
Advise, please, as at import to create tables with NLS_LENGTH_SEMANTICS = CHAR?
Thanks!

2

Re: How to change NLS_LENGTH_SEMANTICS at usage Data Pump Import

Similar in any way, only preliminary creation
ORA-2375 ORA-12899 ORA-2372 Errors While DataPump Import Done From Single Byte Characterset to Multi Byte Characterset Database (Doc ID 436240.1)

3

Re: How to change NLS_LENGTH_SEMANTICS at usage Data Pump Import

Or preliminary change on a source.

4

Re: How to change NLS_LENGTH_SEMANTICS at usage Data Pump Import

Vyacheslav, Elic;
Thanks!
You confirmed my gloomy assumptions.
I got SR on site MOS.
Still there is a hope that Data Pump Import has necessary undescribed parameters,
As for example here these: ADWC. Importing Data Using Oracle Data Pump

transform=dwcs_cvt_iots:y
transform=constraint_use_default_index:y

5

Re: How to change NLS_LENGTH_SEMANTICS at usage Data Pump Import

Hello, dear colleagues!
Unfortunately, technical support did not please me.
Sent me on
ORA-01401 / ORA-12899 / ORA-01461 While Importing Or Loading Data In An AL32UTF8 / UTF8 (Unicode) Or Other Multibyte NLS_CHARACTERSET Database. (Doc ID 1297961.1)
And there all the same fairy tale "about the white bull-calfe", but with
:-(
I will create Enhancement Request (ER) for utility Data Pump Import.
To ask, that made the parameter ordering at creation of tables
Forcedly to transform NLS_LENGTH_SEMANTICS from BYTE in CHAR.
That made something like:

TRANSFORM = NLS_LENGTH_SEMANTICS:CHAR

As though you formulated such ER?
Whether has sense to provide the forced conversion from CHAR in BYTE?

6

Re: How to change NLS_LENGTH_SEMANTICS at usage Data Pump Import

Jurinsky wrote:

Sent me on
ORA-01401 / ORA-12899 / ORA-01461 While Importing Or Loading Data In An AL32UTF8 / UTF8 (Unicode) Or Other Multibyte NLS_CHARACTERSET Database. (Doc ID 1297961.1)
And there all the same fairy tale "about the white bull-calfe", but with
:-(

And there all the same fairy tale "about the white bull-calfe", but with complication of a plot by application of utility Csscan...

7

Re: How to change NLS_LENGTH_SEMANTICS at usage Data Pump Import

Jurinsky wrote:

I Will create Enhancement Request (ER) for utility Data Pump Import.
To ask, that made the parameter ordering at creation of tables
Forcedly to transform NLS_LENGTH_SEMANTICS from BYTE in CHAR.
That made something like:

TRANSFORM = NLS_LENGTH_SEMANTICS:CHAR

As though you formulated such ER?
Whether has sense to provide the forced conversion from CHAR in BYTE?

As far as I understand NLS_LENGTH_SEMANTICS=BYTE it is necessary only at setting of patches. More truly at creation/updating of string fields of tables data dictionary. So-that something is possible type if ORACLE_MAINTAINED=Y that length semantics BYTE or is banal default length semantics = CHAR and the Oracle at writing of the system code/scripts explicitly specifies BYTE. But it pulls on more than Enhancement Request so-that adding of Oracle Data Pump of transformation is much more realistic. Only I would remove NLS. NLS_LENGTH_SEMANTICS it is a default argument used at creation  with the incomplete description. Data Pump works with with e existing  bases or their meta data where a does not exist any more. I would name transformation:

TRANSFORM = LENGTH_SEMANTICS:CHAR

SY.

8

Re: How to change NLS_LENGTH_SEMANTICS at usage Data Pump Import

SY wrote:

Only I would remove NLS. NLS_LENGTH_SEMANTICS it is a default argument used at creation  with the incomplete description. Data Pump works with with e existing  bases or their meta data where a does not exist any more. I would name transformation:

TRANSFORM = LENGTH_SEMANTICS:CHAR

the Good sentence.
Thanks!
Here only it is a pity that do this improving  can eternally... :-(
One ours enhancement request 20114154 - CREATE CONSTRAINT IN 2 STEPS TO USE PARALLEL FEATURES
since November, 28th 2014 ... :-(

9

Re: How to change NLS_LENGTH_SEMANTICS at usage Data Pump Import

Generally can fall down not only creation IOT, and, basically, any index (including unique)
So, I think, even Enhancement Request reject

10

Re: How to change NLS_LENGTH_SEMANTICS at usage Data Pump Import

Ljubomudrov wrote:

Generally can fall down not only creation IOT, and, basically, any index (including unique)
So, I think, even Enhancement Request reject

it is not necessary to think.
Let's try - to create ER.
Let's agree and lift a wave: many people offer the same one words!
It means actually so important! :-)
Give the chance (hammer), and as it to apply, ourselves solve...
Did not forbid while sale of hammers because that them sometimes beat to death on a head! :-)
If the index turns out too long - in its fire chamber.
IoT is generally separate harm which in independent bases is already forbidden.