There is an important recommendation coming from Oracle regarding all new database deployments. If you check the Database Globalization Support Guide and more precisely the Choosing a Character Set chapter you will notice that for all new deployments the recommended character set is Unicode AL32UTF8.
Setting a character set for an empty database is easy. The tricky part comes when you get involved in migrating old data to this new installations. This could be related to a convergence project or decommissioning of old servers. One way or another the time will come when you will have to export some data residing in a old non-Unicode database and import it to a new AL32UTF8 installation.
If you every tried to transfer some data from a database that uses a single-byte encoding scheme (i.e. ISO 8859-1) to an AL32UTF8 database you probably bumped into multiple "value too large for column" errors (ORA-12899). In this article I will try to demonstrate and explain the problem in more details and also provide a simple way to resolve the errors and load the data.
Length semantics
When you define table columns in Oracle, the length that you provide with the column type signifies the length of the data in bytes. A VARCHAR2(20) column can store up to 20 bytes of character information. In a single-byte character sets, the number of bytes and the number of characters are basically the same. In a single-byte character set a VARCHAR2(20) column can store up to 20 characters. In a multibyte sets things are different.
If you have a database with JA16SJIS (Japanese) character set, a VARCHAR2(20) column can store up to 10 characters. This is due to the fact that each character in JA16SJIS is represented by two bytes.
If you try to export a table with a VARCHAR2(20) column from a ISO 8859-1 database and then try to import in a JA16SJIS database you will encounter multiple ORA-12899 errors. One for every row containing more than 10 characters in the VARCHAR2(20) column.
Let's see what the problem looks like in a real example.
Hitting the errors
I have two test databases called DEVDB and ORCL. There is a schema called DELO residing within DEVDB, that I would like to export and then import in ORCL. Let's check DEVDB's character set first.
C:\Documents and Settings\Administrator>sqlplus system@devdb Export: Release 10.2.0.1.0 - Production on Wed Feb 3, 2010 9:14:42 Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET'; VALUE ---------------------------------------------------------------- CL8MSWIN1251 SQL>
You can see that DEVDB is using a single-byte character set (CL8MSWIN1251). I will export the DELO schema by using Oracle Data Pump.
C:\Documents and Settings\Administrator>expdp system@devdb directory=data_pump_dir dumpfile=delo.dmp schemas=delo Export: Release 10.2.0.1.0 - Production on Wed Feb 3, 2010 9:16:44 Copyright (c) 2003, 2005, Oracle. All rights reserved. Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@devdb directory=data_pump_dir dumpfile=delo.dmp schemas=delo Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 233.6 MB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "DELO"."TABLE_REZOL_DOC" 79.48 MB 127843 rows . . exported "DELO"."TABLE_FIRST" 19.78 MB 127302 rows . . exported "DELO"."DOC" 14.05 MB 76821 rows . . exported "DELO"."JOURNAL" 11.74 MB 104861 rows . . exported "DELO"."REZOL" 8.185 MB 47088 rows . . exported "DELO"."DELO" 6.825 MB 46306 rows . . exported "DELO"."DIC_ANOT" 7.045 MB 575042 rows ... . . exported "DELO"."T_SLUJBI128" 0 KB 0 rows . . exported "DELO"."USLUGI" 0 KB 0 rows . . exported "DELO"."VERSION1" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ************************************************************************* Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: C:\ADMIN\DEVDB\DPDUMP\DELO.DMP Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:18:26 C:\Documents and Settings\Administrator>
We will import the DMP file after copying it to the ORCL's machine. But first, let's check the character set of the target database as well.
[oracle@db1 ~]$ sqlplus system@orcl SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 3 08:59:38 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET'; VALUE ---------------------------------------------------------------- AL32UTF8 SQL>
Trying to import the DMP file into ORCL looks like this:
[oracle@db1 /]$ impdp system@orcl directory=ORABACKUP dumpfile=DELO.DMP remap_tablespace=DELO:USERS Import: Release 11.2.0.1.0 - Production on Wed Feb 3 09:04:13 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl directory=ORABACKUP dumpfile=DELO.DMP remap_tablespace=DELO:USERS Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE ORA-02374: conversion error loading table "DELO"."TABLE_REZOL_DOC" ORA-12899: value too large for column COL_P (actual: 677, maximum: 500) ORA-02372: data for row: COL_P : 0X'CFD0C5C4D1D2C0C2DFCDC520C220CED2C4C5CB20CAC0CDD6C5' ORA-02374: conversion error loading table "DELO"."TABLE_REZOL_DOC" ORA-12899: value too large for column COL_P (actual: 503, maximum: 500) ORA-02372: data for row: COL_P : 0X'CFCED1D2DACFC8CBCE20CFC8D2C0CDC52020CED220CDCF2020' ORA-02374: conversion error loading table "DELO"."TABLE_REZOL_DOC" ORA-12899: value too large for column COL_V (actual: 340, maximum: 300) ORA-02372: data for row: COL_V : 0X'C3C5CEC3D0C0D4D1CAC820C8CDD1D2C8D2D3D220CDC020C1C0' ORA-02374: conversion error loading table "DELO"."TABLE_REZOL_DOC" ORA-12899: value too large for column COL_P (actual: 531, maximum: 500) ORA-02372: data for row: COL_P : 0X'C8C7CFD0C0D9C0D220CFCE20C5CBC5CAD2D0CECDCDC0D2C020' ... ORA-02374: conversion error loading table "DELO"."DOC" ORA-12899: value too large for column ANOT (actual: 300, maximum: 254) ORA-02372: data for row: ANOT : 0X'CFD0C5C4CBCEC6C5CDC8C520C7C020C2CACBDED7C2C0CDC520' . . imported "DELO"."DOC" 14.05 MB 70169 out of 76821 rows . . imported "DELO"."USLUGI" 0 KB 0 rows . . imported "DELO"."VERSION1" 0 KB 0 rows ... . . imported "DELO"."SID_DOC" 0 KB 0 rows . . imported "DELO"."SPR_DOP" 0 KB 0 rows . . imported "DELO"."SPR_UPR" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 09:12:52 [oracle@db1 /]$
You can see that more than 6000 rows were skipped out of the DOC table (76821-70169=6652). Inspecting the import log shows multiple errors similar to this one:
ORA-02374: conversion error loading table "DELO"."TABLE_REZOL_DOC" ORA-12899: value too large for column COL_V (actual: 340, maximum: 300) ORA-02372: data for row: COL_V : 0X'C3C5CEC3D0C0D4D1CAC820C8CDD1D2C8D2D3D220CDC020C1C0'
It is obvious that the COL_V column is defined as VARCHAR2(300) allowing a string with a maximum length of 300 characters in CL8MSWIN1251. In AL32UTF8 however the same string requires 340 bytes, exceeding the column's maximum length.
The solution
Warning: You should NOT use EXPDP/IMPDP when going to (AL32)UTF8 or an other multibyte characterset on ALL 10g versions lower then 10.2.0.4 (including 10.1.0.5). It will provoke data corruption unless Patch 5874989 is applied on the IMPDP side. 11.1.0.6 is also affected.
We mention that the default database behavior is to allocate 20 bytes for a VARCHAR2(20) column. We can change this by modifying the NLS_LENGTH_SEMANTICS parameter. Changing it's default value from BYTE to CHAR will switch the database to character length semantics. This means that instead of 20 bytes, the database will start allocating enough bytes to fit 20 characters (no matter how many bytes are required to present these characters in the current encoding). Unfortunately this is not enough. NLS_LENGTH_SEMANTICS has no influence over Data Pump. If a column is exported with a maximum length of 20 bytes this is how it will be created during the import.
Another approach that can overcome this limitation is to manually change each column's length from BYTE to CHAR before loading the data. This process is based on the Metalink Note 313175.1 and it goes like this:
1. Import only the object definitions from the dump file (tables etc.)
2. Edit every VARCHAR2 and CHAR column, redefining it by explicitly setting it's length in chars (i.e. VARCHAR2(20 CHAR))
3. Import the rows from the dump file and recompile all invalid objects
Let's start by dropping the user from the first import attempt.
[oracle@db1 /]$ sqlplus sys@orcl as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 3 09:53:52 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> DROP USER DELO CASCADE; User dropped. SQL>
We perform another import, this time skipping the data.
[oracle@db1 /]$ impdp system@orcl directory=ORABACKUP dumpfile=DELO.DMP remap_tablespace=DELO:USERS content=metadata_only Import: Release 11.2.0.1.0 - Production on Wed Feb 3 10:14:15 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl directory=ORABACKUP dumpfile=DELO.DMP remap_tablespace=DELO:USERS content=metadata_only Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 10:14:52 [oracle@db1 /]$
Before trying to change the CHAR and VARCHAR2 columns there are few things that we have to consider upfront. We have to check if any of the columns to change are partition keys and we also have to check if there are functional based indexes on any of those columns. It's also a good idea to check for invalid objects before performing the changes.
The thing with the partition key columns is that the database is very strict about changing their definitions, because this might invalidate the current partitions. If we try to do such a change the database will throw ORA-14060 and ORA-14265 errors about it and will simply not allow it. We can check if there are such columns involved in our case by running the following query:
select c.owner, c.table_name, c.column_name, c.data_type, c.char_length
from all_tab_columns c, all_tables t
where c.owner = t.owner
and t.owner = schema_name
and c.table_name = t.table_name
and c.char_used = 'B'
and t.partitioned='YES'
and c.table_name not in (select table_name from all_external_tables)
and c.data_type in ('VARCHAR2', 'CHAR')
It's execution looks like this:
[oracle@db1 orabackup]$ sqlplus system@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 10 13:17:08 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP, Data Mining and Real Application Testing options
SQL> SELECT C.OWNER, C.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE, C.CHAR_LENGTH
2 FROM ALL_TAB_COLUMNS C, ALL_TABLES T
3 WHERE C.OWNER = T.OWNER
4 AND T.OWNER = 'DELO'
5 AND C.TABLE_NAME = T.TABLE_NAME
6 AND C.CHAR_USED = 'B'
7 AND T.PARTITIONED='YES'
8 AND C.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)
9 AND C.DATA_TYPE IN ('VARCHAR2', 'CHAR')
10 /
no rows selected
SQL>
If the query returns no rows, we can safely proceed. If it returns any partition key columns than we have to follow Metalink Note 330964.1 (ORA-14060 or ORA-14265 when modifying column definition) before proceeding.
The situation with the functional based indexes is similar. The database won't let us change a column having such an index based on it. We will get an "ORA-30556: functional index is defined on the column to be modified" error if we try to change the column's definition.
The following query will list the functional based indexes (if any) for the given schema:
select INDEX_NAME , INDEX_TYPE, TABLE_OWNER, TABLE_NAME, STATUS, FUNCIDX_STATUS
from ALL_INDEXES
where INDEX_TYPE not in ('NORMAL', 'BITMAP','IOT - TOP')
and TABLE_OWNER = schema_name
and TABLE_NAME in
(select unique (table_name) from dba_tab_columns where char_used ='B')
Running the query looks like this:
SQL> SELECT INDEX_NAME , INDEX_TYPE, TABLE_OWNER, TABLE_NAME, STATUS, FUNCIDX_STATUS
2 FROM ALL_INDEXES
3 WHERE INDEX_TYPE NOT IN ('NORMAL', 'BITMAP','IOT - TOP')
4 AND TABLE_OWNER = 'DELO' AND TABLE_NAME IN
5 (SELECT UNIQUE (TABLE_NAME) FROM DBA_TAB_COLUMNS WHERE CHAR_USED ='B')
6 /
no rows selected
SQL>
If the query returns any indexes instead of "no rows selected" we have to extract their DDL (by using DBMS_METADATA.GET_DDL), drop the indexes and recreate them at the end. Check the 313175.1 note for more details.
It's also a good idea to check for invalid objects and fix them before proceeding.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS = 'INVALID' AND OWNER='DELO'; no rows selected SQL>
After all preliminary checks are performed we can proceed with changing the semantic from BYTE to CHAR. We start by creating a table called SEMANTIC$ that will store information about the columns that we want to redefine.
SQL> CREATE TABLE SEMANTICS$(S_OWNER VARCHAR2(40), S_TABLE_NAME VARCHAR2(40), S_COLUMN_NAME VARCHAR2(40), S_DATA_TYPE VARCHAR2(40), S_CHAR_LENGTH NUMBER); Table created. SQL>
The following statement will insert information about all columns of type CHAR and VARCHAR2 into SEMANTIC$:
insert into semantics$
select C.owner, C.table_name, C.column_name, C.data_type, C.char_length
from all_tab_columns C, all_tables T
where C.owner = T.owner
and T.owner = schema_name
and C.table_name = T.table_name
and C.char_used = 'B'
and T.partitioned != 'YES'
and C.table_name not in (select table_name from all_external_tables)
and C.data_type in ('VARCHAR2', 'CHAR')
/
Here is it's execution:
SQL> INSERT INTO SEMANTICS$
2 SELECT C.OWNER, C.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE, C.CHAR_LENGTH
3 FROM ALL_TAB_COLUMNS C, ALL_TABLES T
4 WHERE C.OWNER = T.OWNER
5 AND T.OWNER = 'DELO'
6 AND C.TABLE_NAME = T.TABLE_NAME
7 AND C.CHAR_USED = 'B'
8 AND T.PARTITIONED != 'YES'
9 AND C.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)
10 AND C.DATA_TYPE IN ('VARCHAR2', 'CHAR')
11 /
835 rows created.
SQL>
In my case the insert creates 835 rows. These are all columns from the DELO schema that I have to change. The change will be performed by simply calling a single ALTER TABLE statement for each row in the SEMANTIC$ table. Here is the script that will do the trick:
set serveroutput on
set termout on
declare
cursor c1 is select * from semantics$;
v_statement varchar2(255);
v_nc number(10);
v_nt number(10);
begin
execute immediate 'select count(*) from semantics$' into v_nc;
execute immediate 'select count(distinct s_table_name) from semantics$' into v_nt;
dbms_output.put_line ('ALTERing ' || v_nc || ' columns in ' || v_nt || ' tables');
for r1 in c1 loop
v_statement := 'ALTER TABLE "' || r1.s_owner || '"."' || r1.s_table_name;
v_statement := v_statement || '" modify ("' || r1.s_column_name || '" ';
v_statement := v_statement || r1.s_data_type || '(' || r1.s_char_length;
v_statement := v_statement || ' CHAR))';
dbms_output.put_line(v_statement);
execute immediate v_statement;
end loop;
dbms_output.put_line('Done');
end;
/
While the script is running it will print each statement that it executes.
...
ALTER TABLE "DELO"."DOC" modify ("EMAIL" VARCHAR2(100 CHAR))
ALTER TABLE "DELO"."APP_VERSION" modify ("VER" VARCHAR2(25 CHAR))
ALTER TABLE "DELO"."APP_VERSION" modify ("FILE_NAME" VARCHAR2(50 CHAR))
ALTER TABLE "DELO"."APP_PC" modify ("VER" VARCHAR2(25 CHAR))
...
ALTER TABLE "DELO"."APP_PC" modify ("NAME_PC" VARCHAR2(50 CHAR))
Done
PL/SQL procedure successfully completed.
SQL>
After the execution is completed we can drop the SEMANTIC$ table. We don't need it anymore.
SQL> DROP TABLE SEMANTICS$; Table dropped. SQL>
Let's see what happens when we try to import the data in the redefined tables.
[oracle@db1 /]$ impdp system@orcl directory=ORABACKUP dumpfile=DELO.DMP remap_tablespace=DELO:USERS content=data_only Import: Release 11.2.0.1.0 - Production on Wed Mar 10 16:33:34 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl directory=ORABACKUP dumpfile=DELO.DMP remap_tablespace=DELO:USERS content=data_only Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "DELO"."TABLE_REZOL_DOC" 79.48 MB 127843 rows . . imported "DELO"."TABLE_FIRST" 19.78 MB 127302 rows . . imported "DELO"."DOC" 14.05 MB 76821 rows . . imported "DELO"."JOURNAL" 11.74 MB 104861 rows . . imported "DELO"."REZOL" 8.185 MB 47088 rows . . imported "DELO"."DELO" 6.825 MB 46306 rows . . imported "DELO"."DIC_ANOT" 7.045 MB 575042 rows . . imported "DELO"."EKZ" 5.794 MB 95320 rows . . imported "DELO"."APP_VERSION" 6.396 MB 34 rows . . imported "DELO"."DVIG" 5.086 MB 102124 rows ... . . imported "DELO"."USLUGI" 0 KB 0 rows . . imported "DELO"."VERSION1" 0 KB 0 rows Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 16:38:48
There are no skipped rows this time. All data is imported correctly.
As an optional step we can run EXECUTE UTL_RECOMP.RECOMP_PARALLEL for the DELO schema, to recompile invalid PL/SQL modules, index types etc.
SQL> EXECUTE UTL_RECOMP.RECOMP_PARALLEL(4); PL/SQL procedure successfully completed. SQL>
Final notes
Using this technique may sometimes lead to Constraint violation errors during the data import. In order to avoid such problems we can temporarily disable the constraints before the data import and then re-enable them when the import is completed. We can create two SQL files – tmp_disable.sql and tmp_enable.sql like this:
set feedback off set verify off set echo off set termout off set pages 80 set heading off set linesize 120 spool tmp_disable.sql select 'ALTER TABLE '||c.owner||'.'||substr(c.table_name,1,35) ||' DISABLE CONSTRAINT '||constraint_name||';' from dba_constraints c, dba_tables u where (c.table_name = u.table_name) and (c.owner=schema_name);
spool tmp_enable.sql select 'ALTER TABLE '||c.owner||'.'||substr(c.table_name,1,35) ||' ENABLE CONSTRAINT '||constraint_name||';' from dba_constraints c, dba_tables u where (c.table_name = u.table_name) and (c.owner=schema_name);
If you want to learn more about character sets, length semantics, NLS parameters etc. check the Database Globalization Support Guide. It's a wonderful and very detailed source of information.