2 Replies Latest reply: Nov 26, 2012 4:34 PM by user10651321 RSS

    impdp errors with ORA-01400: cannot insert NULL into

      Hi Experts,

      I have very intresting situation when I use impdp to import table back to DB.

      Database Server: Enterprise
      OS: RHEL 5.5 64-bit

      We have a table and it's size is 350+ GB and so to reclaim space I want to perform expdp/impdp operation. When I following below steps:

      1) expdp table
      2) drop table
      3) impdp full table

      It works like a charm and I am able to reclaim 83% space and table size shows about 20GB.

      But when I follow below method:

      1) expdp table
      2) truncate table (To save some time not to import indexes, stats, constraints , etc)
      3) impdp table

      I get following error:

      Connected to: Oracle Database 10g Enterprise Edition Release - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
      Starting "SYS"."SYS_IMPORT_TABLE_01": sys/******** tables=<schema>.<table_name> directory=test_dir dumpfile=<table_name>_%u.dmp logfile=impdp_<table_name>.log parallel=16 CONTENT=DATA_ONLY
      Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
      ORA-31693: Table data object "<schema"."table_name" failed to load/unload and is being skipped due to error:
      ORA-01400: cannot insert NULL into ("schema"."table_name"."ID")
      Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 16:12:28

      Please advise how to proceed further, it seems like I am hitting some sort of Bug but metalink does not show any.


      Edited by: user10651321 on Nov 9, 2012 4:38 PM