ORA-39123: Data Pump transportable tablespace job aborted / ORA-01240: too many data files to add in one command

Today i ran into a good example for really really bad error messages: Imagine a tablespace with 720 datafiles (approx 22 TB in total size) which should be transported via transportable tablespace. For this you created a plain, new 11.2 database and you are trying to attach the transportable tablespace:
[oracle@ora1 ~]$ impdp system/manager parfile=import.par
Import: Release 11.2.0.3.0 - Production on Wed Sep 26 19:55:19 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** parfile=import.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted ORA-01240: too many data files to add in one command
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 19:55:22
“Too many datafiles”… well 720 are really a lot but within the limits oracle imposes. On MOS there is only one matching note:
“The problem here is that during TTS plugin phase we generate a single redo record containing the name of all the datafiles in the operation. If there are too many files in the transportable set then the redo record that needs to be generated goes past the MAX size redo record, resulting in the error ORA-1240. The limiting factor is the size of the NAMES of the files, not the physical size of the files themselves.”
I can tell you shortening the path names did not help at all. But while developing a testcase i found the error. It is the value of DB_FILES which is by default set to 200… by adding 720 more datafiles you will go way beyond that limit.
So let´s see what happens if we increse the parameter before the import:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter db_files
NAME     TYPE VALUE
--------------------------
db_files     integer 200
SQL> alter system set db_files=2000 scope=spfile;
System altered.

SQL> startup force
ORACLE instance started.
Total System Global Area  801701888 bytes
Fixed Size    2232640 bytes
Variable Size  230690496 bytes
Database Buffers   566231040 bytes
Redo Buffers    2547712 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ora1 ~]$ impdp system/manager parfile=import.par
Import: Release 11.2.0.3.0 - Production on Wed Sep 26 19:56:19 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** parfile=import.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 1 error(s) at 19:56:27
Problem solved :-)

Leave a Reply

Your email address will not be published.