oracle datapump, because there is no better one

In the last couple of days I have been busy with consolidating different databases into 2 big ones.
One of the challenges was that in the original databases there where a lot of objects in the “users” and “indx” tablespace (and the other tablespaces had no real naming convention either). To make it more fun, a high number of tables used lob datatypes.
(When you have a table with a lob datatype and one of the referenced tablespaces does not exist, then the table create statement fail, instead of failing over to the default tablespace of the schema owner. This because imp can not determine which tablespace clause – that of the table itself or that for the lob – caused the error)

In the old pre-10g ages, I would have had the choice between a) create tablespaces with the same name in the consolidated database and later on move the objects to a new tablespace, or b) precreate the tables with lob datatypes and then do the import.
Both options would be time consuming and error prone, not to mention that the import utility was not very fast when it came to importing lobs.

Fortuanatly these databases to be consolidated where all 10gR2 databases, so I had a third option: use oracle datapump to move the schemas to the new database.
With datapump you have this very nice parameter: remap_tablespace, which allows you to simple specify the old and the new tablespaces name and that is all you have to do to change tablespace_names during the import.
And to make it realy fun: you can use the network_link parameter to specify a database link, which will then be used to transport data from the source db to the target db (by by intermediate file).

Of course there are some drawbacks.
First of all: it is a server based tool (or better said, expdp/impdp are tools written arround server packages). That is, all processing happens on the server and the dmp file will be placed/read on/from the server itself.
While this gives you the possibility to start the expdp or impdp, disconnect and then reconnect at home, it may provide a (security) problem to work with files on the server itself. On nx environments NFS may provide a solution, as people can just mount the nfs volume on their local pc’s and work with the dmp files as if they where stored locally.
A second disadvantage is that datapump may be slower then exp/imp in case you have just a small dataset, but a high number of (empty or almost empty) tables.
What I also found annoying, is that the flashback_time parameter (which is more or less replacing the old consistent parameter) does not accept a datastamp value. This means that you can’t simply specify flashback_time=”systimestamp” to tell datapump you want all objects to be consistent to each other, but instead you have the specify the following: flashback_time=”to_timestamp(to_char(systimestamp, ‘DD/MM/YYYY HH24:MI:SS.FF’), ‘DD/MM/YYYY HH24:MI:SS.FF’)”.

Oh, and of course there is a bug to look out for: 5874989, which saids that data corruption can occur during a datapump import with characterset conversion.
luckily a patch exists.