This discussion is archived
8 Replies Latest reply: Feb 14, 2013 3:00 AM by Nicosa RSS

IMPDP SQLFILE : multibyte characters in constraint_name leads to ORA-00972

Nicosa Expert
Currently Being Moderated
Hi,

I'm actually dealing with constraint_name made of multibyte characters (for example : constrain_name='VALIDA_CONFIRMAÇÃO_PREÇO13').
Of course this Bad Idea® is inherited (I'm against all the fancy stuff like éàù in filenames and/or directories on my filesystem....)

The scenario is as follows :
0 - I'm supposed to do a "remap_schema". Everything in the schema SCOTT should now be in a schema NEW_SCOTT.
1 - The scott schema is exported via datapump
2 - I do an impdp with SQLFILE in order to get all the DDL (table, packages, synonyms, etc...)
3 - I do some sed on the generated sqlfile to change every occurence of SCOTT to NEW_SCOTT (this part is OK)
4 - Once the modified sqlfile is executed, I do an impdp with DATA_ONLY.

(The scenario was imagined from this thread : {message:id=10628419} )

I'm getting some ORA-00972: identifier is too long at step 4 when executing the sqlfile.
I see that some DDL for constraint creation in the file (generated at step#2) is written as follow :
ALTER TABLE "TW_PRI"."B_TRANSC" ADD CONSTRAINT "VALIDA_CONFIRMAÃÃO_PREÃO14" CHECK ...
Obviously, the original name of the constraint with cedilla and tilde gets translated to something else which is longer than 30 char/byte...

As the original name is from Brazil, I also tried do add an EXPORT LANG=pt_BR.UTF-8 in my script before running the impdp for sqlfile. This didn't change anything. (the original $LANG is en_US.UTF-8)

In order to create a testcase for this thread, I tried to reproduce on my sandbox database... but, there, I don't have the issue. :-(

The real system is an 4-nodes database on Exadata (11.2.0.3) with NLS_CHARACTERSET=AL32UTF8.
My sandbox database is a (nonRAC) 11.2.0.1 on RHEL4 also AL32UTF8.

The constraint_name is the same on both system : I checked byte by byte using DUMP() on the constraint_name.

Feel free to shed any light and/or ask for clarification if needed.


Thanks in advance for those who'll take on their time to read all this.
:-)

--------
I decided to include my testcase from my sandbox database, even if it does NOT reproduce the issue +(maybe I'm missing something obvious...)+

I use the following files.
- createTable.sql :
$ cat createTable.sql 
drop table test purge;

create table test
(id integer,
val varchar2(30));

alter table test add constraint VALIDA_CONFIRMAÇÃO_PREÇO13 check (id<=10000000000);

select constraint_name, lengthb(constraint_name) lb, lengthc(constraint_name) lc, dump(constraint_name) dmp
from user_constraints where table_name='TEST';
- expdpTest.sh :
$ cat expdpTest.sh 
expdp scott/tiger directory=scottdir dumpfile=testNonAscii.dmp tables=test
- impdpTest.sh :
$ cat impdpTest.sh 
impdp scott/tiger directory=scottdir dumpfile=testNonAscii.dmp sqlfile=scottdir:test.sqlfile.sql tables=test
This is the run :
[oracle@Nicosa-oel test_nonAsciiColName]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 12 18:58:27 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @createTable

Table dropped.


Table created.


Table altered.


CONSTRAINT_NAME                  LB       LC
------------------------------ ---------- ----------
DMP
--------------------------------------------------------------------------------
VALIDA_CONFIRMAÇÃO_PREÇO13             29         26
Typ=1 Len=29: 86,65,76,73,68,65,95,67,79,78,70,73,82,77,65,195,135,195,131,79,95
,80,82,69,195,135,79,49,51


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@Nicosa-oel test_nonAsciiColName]$ ./expdpTest.sh 

Export: Release 11.2.0.1.0 - Production on Tue Feb 12 19:00:12 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=scottdir dumpfile=testNonAscii.dmp tables=test 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SCOTT"."TEST"                                  0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/scott_dir/testNonAscii.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 19:00:22

[oracle@Nicosa-oel test_nonAsciiColName]$ ./impdpTest.sh 

Import: Release 11.2.0.1.0 - Production on Tue Feb 12 19:00:26 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_SQL_FILE_TABLE_01":  scott/******** directory=scottdir dumpfile=testNonAscii.dmp sqlfile=scottdir:test.sqlfile.sql tables=test 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SCOTT"."SYS_SQL_FILE_TABLE_01" successfully completed at 19:00:32

[oracle@Nicosa-oel test_nonAsciiColName]$ cat scott_dir/test.sqlfile.sql 
-- CONNECT SCOTT
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT"."TEST" 
   (     "ID" NUMBER(*,0), 
     "VAL" VARCHAR2(30 BYTE)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR OLTP LOGGING
  TABLESPACE "MYTBSCOMP" ;
 
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "SCOTT"."TEST" ADD CONSTRAINT "VALIDA_CONFIRMAÇÃO_PREÇO13" CHECK (id<=10000000000) ENABLE;
I was expecting to have the cedilla and tilde characters displayed incorrectly....

Edited by: Nicosa on Feb 12, 2013 7:13 PM
  • 1. Re: IMPDP SQLFILE : multibyte characters in constraint_name leads to ORA-00972
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    If I understand you correctly, you are unable to reproduce the issue in the test instance, while it occurs in the production instance. Is the "schema move" being done on the same database - i.e. you are "moving" from SCOTT to NEW_SCOTT on the same database (test to test, and prod to prod) ? Do you have to physically move/copy the dmp file ? Obviously something is different in production than test - can you post the output of this command from both databases ?
    SQL> select * from NLS_DATABASE_PARAMETERS;
    HTH
    Srini
  • 2. Re: IMPDP SQLFILE : multibyte characters in constraint_name leads to ORA-00972
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Did you set NLS_LANG also when you did the import?

    Also not sure why you are doing the sed part? Do you have hard coded scheme references inside some of the plsql?

    Cheers,
    Harry
  • 3. Re: IMPDP SQLFILE : multibyte characters in constraint_name leads to ORA-00972
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Agree with Harry - you should be using REMAP_SCHEMA (http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#sthref272) rather than mucking around with sed

    HTH
    Srini
  • 4. Re: IMPDP SQLFILE : multibyte characters in constraint_name leads to ORA-00972
    Nicosa Expert
    Currently Being Moderated
    Srini Chavali wrote:
    If I understand you correctly, you are unable to reproduce the issue in the test instance, while it occurs in the production instance. Is the "schema move" being done on the same database - i.e. you are "moving" from SCOTT to NEW_SCOTT on the same database (test to test, and prod to prod) ? Do you have to physically move/copy the dmp file ?
    Hi Srini,

    On the real system, the schema move will be to and from different machines (but same DBversion).
    I'm not doing the real move for the moment, just trying to validate a way to do it, but I guess it's important to say that the dump being used for the moment comes from the same database (the long story being that due to some column using object datatype which caused error in the remap, I had to reload the dump with the "schema rename", drop the object column, and recreate a dump file without the object_datatype...).
    So Yes, the file will have to move, but in the current test, it doesn't.
    Srini Chavali wrote:
    Obviously something is different in production than test - can you post the output of this command from both databases ?
    SQL> select * from NLS_DATABASE_PARAMETERS;
    Yes Srini, something is obviously different : I'm starting to think that the difference might be in the Linux/shell side rather than on the impdp as datapump is supposed to be NLS_LANG/CHARSET-proof +(when traditional imp/exp was really sensible on those points)+

    The result on the Exadata where I have the issue :
    PARAMETER                      VALUE
    ------------------------------ --------------------------------------------------
    NLS_LANGUAGE                   AMERICAN
    NLS_TERRITORY                  AMERICA
    NLS_CURRENCY                   $
    NLS_ISO_CURRENCY               AMERICA
    NLS_NUMERIC_CHARACTERS         .,
    NLS_CHARACTERSET               AL32UTF8
    NLS_CALENDAR                   GREGORIAN
    NLS_DATE_FORMAT                DD-MON-RR
    NLS_DATE_LANGUAGE              AMERICAN
    NLS_SORT                       BINARY
    NLS_TIME_FORMAT                HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY              $
    NLS_COMP                       BINARY
    NLS_LENGTH_SEMANTICS           BYTE
    NLS_NCHAR_CONV_EXCP            FALSE
    NLS_NCHAR_CHARACTERSET         AL16UTF16
    NLS_RDBMS_VERSION              11.2.0.3.0
    the result on my sandbox DB :
    PARAMETER                      VALUE
    ------------------------------ --------------------------------------------------
    NLS_LANGUAGE                   AMERICAN
    NLS_TERRITORY                  AMERICA
    NLS_CURRENCY                   $
    NLS_ISO_CURRENCY               AMERICA
    NLS_NUMERIC_CHARACTERS         .,
    NLS_CHARACTERSET               AL32UTF8
    NLS_CALENDAR                   GREGORIAN
    NLS_DATE_FORMAT                DD-MON-RR
    NLS_DATE_LANGUAGE              AMERICAN
    NLS_SORT                       BINARY
    NLS_TIME_FORMAT                HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY              $
    NLS_COMP                       BINARY
    NLS_LENGTH_SEMANTICS           BYTE
    NLS_NCHAR_CONV_EXCP            FALSE
    NLS_NCHAR_CHARACTERSET         AL16UTF16
    NLS_RDBMS_VERSION              11.2.0.1.0
    ------
    Richard Harrison .  wrote:
    Hi,
    Did you set NLS_LANG also when you did the import?
    Yes, that is one of the difference between the Exadata and my sandbox.
    My environnement in sandbox has NLS_LANG=AMERICAN_AMERICA.AL32UTF8 where the Exadata doesn't have the variable set.
    I tried to add it, but it didn't change anything.
    Richard Harrison .  wrote:
    Also not sure why you are doing the sed part? Do you have hard coded scheme references inside some of the plsql?
    Yes, that is why I choose to sed. The (ugly) code have :
    - Procedures inside the same package that references one another with the schema prepended
    - Triggers with PL/SQL codes referencing tables with schema prepended
    - Dynamic SQL that "builds" queries with schema prepended
    - Object Type that does some %ROWTYPE on tables with schema prepended (that will be solved by dropping the column based on those types as they obviously are not needed...)
    - Data model with object whose names uses non-ascii characters
    +(In France we use to call this "gas power plant" in order to tell how a mess it is : pipes everywhere going who-knows-where...)+

    The big picture is that this kind of "schema move & rename" should be as automatic as possible, as the project is to actually consolidate several existing databases on the Exadata :
    One schema for each country, hence the rename of the schemas to include country-code.

    ------

    I actually have a workaround yet : Rename the objects that have funky characters in their name before doing the export.
    But I was curious to understand why the SQLFILE messed up the constraint_name on one sustem when it doesn't on another...
  • 5. Re: IMPDP SQLFILE : multibyte characters in constraint_name leads to ORA-00972
    Richard Harrison . Expert
    Currently Being Moderated
    Hi again,
    Where did the original expdp file come from -somewhere other than the rac you are loading it into. Did that also have al32utf8 as the character set. Perhaps the original character set is not UHF but some other character set that supports Brazilian Portuguese? And this coarsest is not a subset of UTF8 for some reason?

    Cheers,
    Harry
  • 6. Re: IMPDP SQLFILE : multibyte characters in constraint_name leads to ORA-00972
    Nicosa Expert
    Currently Being Moderated
    Richard Harrison .  wrote:
    Where did the original expdp file come from -somewhere other than the rac you are loading it into. Did that also have al32utf8 as the character set. Perhaps the original character set is not UHF but some other character set that supports Brazilian Portuguese? And this coarsest is not a subset of UTF8 for some reason?
    Hi Richard,

    Yes the original dmp file comes from another database. But it's also in AL32UTF8 (the nls_parameters are below).
    I just noticed that the original database is not 11.2.0.3 as I previously said, but rather 11.2.0.1 (not sure it's related thought).
    PARAMETER                      VALUE
    ------------------------------ --------------------------------------------------
    NLS_LANGUAGE                   AMERICAN
    NLS_TERRITORY                  AMERICA
    NLS_CURRENCY                   $
    NLS_ISO_CURRENCY               AMERICA
    NLS_NUMERIC_CHARACTERS         .,
    NLS_CHARACTERSET               AL32UTF8
    NLS_CALENDAR                   GREGORIAN
    NLS_DATE_FORMAT                DD-MON-RR
    NLS_DATE_LANGUAGE              AMERICAN
    NLS_SORT                       BINARY
    NLS_TIME_FORMAT                HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY              $
    NLS_COMP                       BINARY
    NLS_LENGTH_SEMANTICS           BYTE
    NLS_NCHAR_CONV_EXCP            FALSE
    NLS_NCHAR_CHARACTERSET         AL16UTF16
    NLS_RDBMS_VERSION              11.2.0.1.0
    I'm wondering if the issue is not purely a matter of shell/environnement :
    - In VI in the exadata the keyboard input Ctrl then Shift+o then enter display the "Ã"
    - On my sandbox linux the keyboard input ^ then Shift+o displays "Ô" (no need to hit Enter key)

    I really not at all into charset/encoding consideration at OS level : I tried to set bot $LANG and $NLS_LANG but that didn't change anything...
  • 7. Re: IMPDP SQLFILE : multibyte characters in constraint_name leads to ORA-00972
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    I've had strange effects if i didn't set the translation to be utf8 in my ssh client (putty in my case). Is that somehow an issue as is getting automatically set in one environment but not the other?

    In putty it's window->translation and then choose utf8

    Also are you sure it's not sed corrupting it rather than impdp?

    Cheers,
    Harry
  • 8. Re: IMPDP SQLFILE : multibyte characters in constraint_name leads to ORA-00972
    Nicosa Expert
    Currently Being Moderated
    Problem solved !

    The solution actually is to set the NLS_LANG +(stupid me !)+
    When I first tried the setting NLS_LANG I was checking visually (opening the file with vi) the content of the generated sqlfile instead of executing it in SQL*Plus.
    The NLS_LANG is needed for SQL*Plus, not for impdp...
    Richard Harrison .  wrote:
    I've had strange effects if i didn't set the translation to be utf8 in my ssh client (putty in my case). Is that somehow an issue as is getting automatically set in one environment but not the other?

    In putty it's window->translation and then choose utf8
    Richard, you're totally right : after modifying this settings the characters display correctly in vi (through putty).
    Thanks for pointing that out to me.
    Richard Harrison .  wrote:
    Also are you sure it's not sed corrupting it rather than impdp?
    Yes, I checked that the problematic lines were the same in the file before the sed.

    Thanks a lot to all of you for the help.

    +(Note to myself : when troubleshooting keep in mind to actually test rather than just visually check...)+
    :-)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points