Irrelevant thoughts of an oracle DBA

28 November 2007

oracle datapump, because there is no better one

Filed under: infrastructure — Freek D'Hooge @ 0:39

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.



  1. 1. DataPump is not very fast with LOBs either.
    2. Datapump requires that the export directory will be hard mounted on the server. Storage managers hate this, because export directory is usually placed on cheaper storage than the production data directories, but if it is hard mounted on a production server than if something happens to the storage, it could cause a bunch of strange issues on the production server. So, either put exports on expansive storage or take a large risk, and storage managers don’t like these decisions.

    Comment by prodlife — 28 November 2007 @ 2:57 | Reply

  2. My second comment (about the mounts) was related to the NFS solution you suggested…

    Comment by prodlife — 28 November 2007 @ 2:58 | Reply

  3. 1. I did not test it, but it seemed faster then with import.
    This calls for some proper comparison tests :)

    2. good point, did not thought of that.
    But since we are a netapp reseller I would say to create a netapp volume with sata disks: both reliable and “cheap”. ;)

    Comment by dhoogfr — 28 November 2007 @ 8:30 | Reply

  4. just came across another limitation: ORA-31679: Table data object “SESSIONUSR”.”SESSIONS” has long columns, and longs can not be loaded/unloaded using a network link

    Comment by dhoogfr — 28 November 2007 @ 9:20 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Blog at

%d bloggers like this: