Wednesday, November 26, 2008

impdp with in Peoplesoft database

I was very enthusiat by the new features offered by the Oracle import dump, the new import utility in 10g.
Especially with the option NETWORK_LINK which allow us to do an import in a database through a database link directly from the database source, without having the need of the creation of any dump file.
It is very useful regarding the size of Peoplesoft database dump, several dozen of Gb.
Until today, to save disk space, it was common to use an old export utility into a dump file and compress on the fly. Same for the import, uncompress and import into database on fly.
Since it is not possible to do with expdp, the NETWORK_LINK should offer a good alternative.

Unfortunately, two mains points make it impossible to use :

1. NETWORK_LINK does not work with tables containing LONG datatype.
It is not a joke, but Peoplesoft database (before the Peoplesoft version 9.0 and Peopletools 8.49) still contains a lot of tables like that. I was very disapointed by this limitation, and I was surprised because nothing in the documentation say anything about that limitation. You could read the Metalink note 6630677.8 (IMPDP skips table exported just before table with LONG column using NETWORK_LINK - seems to be fixed in
Ok, we could find a workaround and work through a file for these tables (with LONG datatype column in it), even if it's a pity. Or work with the very old, but much more robust SQL*Plus command COPY.

2. There is a bug in the number of imported rows reported in the log file (known bug #7146127, not solved yet). In some cases, for tables containing a lot of columns (I don't know what's the number to hit this error), it is reported 1 imported row in the log file, even if it imported several of thousands of rows. So, now how to prove how many have been imported ? Run a count against every single imported tables ? Not acceptable.

Here is the output of number of columns by tables (Peoplesoft HRMS9, Peopletools 8.49) :
Nb tables with less than 100 columns.................. : 42655
Nb tables with more than 100 and less than 200 columns : 171
Nb tables with more than 200 columns.................. : 21

What else ? Still stuck with exp/imp utility despite they are old.


Addendum (04-FEB-2009) : regarding the wrong number of rows reported in the logfile, there is one off-patch for, it is fixed in the coming 11.2, please have a look into the following Metalink note :
Impdp Logfile Reports Wrong Number of Row Imported For a Table If Using NETWORK_LINK
Doc ID: 752476.1

No comments: