After waiting hours for a large Oracle datapump import to complete, I was dismayed to see the largest table fail with a message similar to this:

ORA-31693: Table data object "SCHEMA_OWNER"."A_TABLE" failed to load/unload and is being skipped due to error:
ORA-01400: cannot insert NULL into ("SCHEMA_OWNER"."A_TABLE"."THE_COLUMN")

The destination database had been prepared by dropping the SCHEMA_OWNER user, so the source and destination tables should have been defined exactly the same. Why was the data in the source table not able to be loaded into the datapump created copy?

I had run into ‘gotchas’ that can trip one up when exporting and importing, such as datapump’s user creation failing because the user’s default tablespace been dropped in the source database, or object creation failing because the privileges or quota used to create the object originally had been revoked from schema owner.
Was this error caused by one of those gotchas?

Both the source and destination tables listed THE_COLUMN as NULLable:

SQL> desc "SCHEMA_OWNER"."A_TABLE"
Name                                                                     Null?    Type
------------------------------------------------------------------------ -------- ---------------------
A_TABLE_ID                                                               NOT NULL NUMBER
THE_COLUMN                                                                        VARCHAR2(200)

So what was causing the ORA-01400?

The answer was found in DBA_CONSTRAINTS:

SQL> @dc
Enter value for table_name: SCHEMA_OWNER.A_TABLE

OWNER                          CONSTRAINT_NAME                C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
SEARCH_CONDITION
----------------------------------------------------------------------------------------------------------------------
R_OWNER                        R_CONSTRAINT_NAME              DELETE_RU STATUS   DEFERRABLE     DEFERRED  VALIDATED
------------------------------ ------------------------------ --------- -------- -------------- --------- ------------
GENERATED      BAD RELY LAST_CHAN INDEX_OWNER                    INDEX_NAME                     INVALID VIEW_RELATED
-------------- --- ---- --------- ------------------------------ ------------------------------ ------- --------------
SCHEMA_OWNER                  SYS_C005431                    C A_TABLE
"A_TABLE_ID" IS NOT NULL
ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED
GENERATED NAME          12-JAN-08

SCHEMA_OWNER                  SYS_C005434                    C A_TABLE
"THE_COLUMN" IS NOT NULL
ENABLED  NOT DEFERRABLE IMMEDIATE NOT VALIDATED
GENERATED NAME          09-AUG-11

Aha!  There is the hidden NOT NULL constraint on THE_COLUMN.  It was enabled, but not validated on the 9th of August.  We looked up the DDL for the code that was run on that day:

alter table SCHEMA_OWNER.A_TABLE modify ( THE_COLUMN not null novalidate);

The rows with null values already in the table were not changed, but no new rows could have null values.  That explains why the data in the table couldn’t be inserted into a copy of the table.

We plan to replace the nulls in THE_COLUMN with dummy values and then validate the NOT NULL constraint.

I think the way the constraint was added in this case was chosen for speed, but was sloppy for the following reasons:

  • If the application expects THE_COLUMN to always have a value, then those old rows with NULLs might cause problems.
  • The constraint is obscured when describing the table.
  • The optimiser won’t be able to take advantage of knowing THE_COLUMN is never null.
  • And of course, datapump imports will fail.

(Names have been changed to simplify the example).

Advertisements

3 thoughts on “Spanner in the Datapump Works

  1. Good explanation my friend. I’m running into a same issue with traditional export but in my case all my constraints are validated and there is no null columns on the source table. Any guess what might be wrong?

    1. Hi Ganesh

      Without more information it could be many things, but assuming the source database is 11g, my guess would be:
      * you have a table with a NOT NULL column and a DEFAULT value
      * that column was added after there were rows in the table
      * you used exp with direct=y, so the nulls stored in the blocks were read and not replaced with the default value during export.

      If this is the case, then use direct=n or use datapump.

      See: MOS Note 826746.1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s