Data Management

Reorganize tablespaces using Oracle 10g Data Pump

Oracle's export and import utilities were re-engineered in Release 10g. Buried among the more flashy features are a number of smaller changes that help DBAs reorganize tablespaces. Bob Watkins discusses three of them.

Oracle's export and import utilities were re-engineered in Release 10g. Buried among the more flashy features, such as the ability to disconnect and reconnect to a running export or import job, are a number of smaller changes that help the DBA reorganize tablespaces. Here are three of them.

Export tablespaces as a unit

In the past, the export (exp) and import (imp) utilities had three modes: You could export a single table and its dependent objects such as indexes; you could export all objects owned by a specific user; or you could export the entire database. But tablespaces were a problem. Objects owned by many different users could be stored in a given tablespace, but some of their objects might be stored in other tablespaces. So, 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.

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.

Weekly Oracle tips in your inbox
TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more.
Automatically sign up today!

Rename datafile names during import

When migrating a database from one platform to another prior to 10g, the DBA was required to pre-create the tablespaces and their datafiles before importing. Why? 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.

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

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.

Change tablespace names during import

The impdp utility also lets you load objects into different tablespaces than they came from originally. Before 10g, the way to do this was complex. 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.

In 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 parameter:

REMAP_TABLESPACE=source_tablespace:target_tablespace

Objects will be automatically sent to the new tablespace.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

Editor's Picks

Free Newsletters, In your Inbox