Reorganize tablespaces using Oracle 10g Data Pump
Thursday, September 07, 2006 02:30 PM
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.
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.
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.
Play video
There are currently no comments for this post.