Oracle Data Pump for reorganizing tablespaces!!

 Author: Bob Watkins

Oracle 10g reengineered Oracle's export and import utilities. Apart from features such as the ability to disconnect and reconnect to a running export or import job, there are a number of smaller changes that help database administrator in reorganizing tablespaces.

Database Export and Import prior to Oracle 10g:

Previously, Oracle export (exp) and import (imp) utilities supported three modes

• Exporting a single table and its dependent objects such as indexes.
• Exporting all objects owned by a specific user.
• Exporting the entire database.

However there was a problem with tablespaces. Objects owned by many different users could be stored in a given tablespace, but some of their objects might be stored in other tablespaces. Therefore the only solution was to query the data dictionary to find the exact list of tables and their owners and use table-mode export to export the objects individually.

Oracle 10g Data Pump Utility:

In Oracle 10g, the Data Pump version of export (expdp) lets you directly export all the objects in a tablespace. The TABLESPACES parameter lets you specify which tablespace(s) you want to export.

TABLESPACES=name [,...]

This is particularly useful if you've inherited a database with a lot of dictionary-based tablespaces, and you want to reduce fragmentation by recreating the tablespaces as locally managed, and then re-import the contents.

Database Import prior to Oracle 10g:

Prior to Oracle 10g, the database administrator was required to pre-create the tablespaces and their datafiles before importing while migrating a database from one platform to another.

This was because the dump file created by export contained datafile pathnames in the format of the original database's operating system. These pathnames would cause errors if used with a different operating system on import.

Database Import in Oracle 10g:

In the 10g Data Pump version of import (impdp), the REMAP_DATAFILE parameter can be used to rename these datafiles on the fly.

REMAP_DATAFILE=source_datafile:target_datafile

This option is used with FULL imports only, and the userID you specify must have the IMP_FULL_DATABASE role.

impdp utility:

The impdp utility lets you load objects into different tablespaces than they came from originally.

Before Oracle 10g, first, you had to remove your quota on the original tablespace so that you had no privileges to write there.

Then, you set your default tablespace to the desired one. During the import, objects that were in the original tablespace would be stored in the default tablespace for the user.

Then you had to remember to set everything back again when you were done.

REMAP_TABLESPACE parameter:

In Oracle 10g import, the REMAP_TABLESPACE parameter makes this a lot easier. You still need to have quota on the destination tablespace, but no other preparation is required.

Simply add the below parameter and objects will be automatically sent to the new tablespace.

REMAP_TABLESPACE=source_tablespace:target_tablespace

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Tips for handling Tricky Roles in Oracle Stored Procedures!
   Disk I/O Tuning with Oracle RAID Stripe Size!!
   DBA Tips for Speeding Up Large Table Updates!!
   Saving and Restoring Old Statistics in Oracle!!