Skip to content

How to identify the Tablespace names from an EXPDP file

06/03/2015

If you have been given a datapump dump file to import you may not have been given the export log file or a list of schemas and tablespaces contained in that file.

Fortunately all is not lost, datapump provides an option to create a text-based file containing the SQL DDL statements used to import the file without actually executing them.

The option is sqlfile and can be used like so:

impdp system/<password>@<ORACLE_SID> sqlfile=dumpfile_sql.txt directory=data_pump_dir dumpfile=dumpfile.dmp

You’ll need to use the appropriate credentials and database name so that the data pump directory path can be retrieved, also specify the correct name of your dumpfile.

If you have a dumpfile that has been created on Enterprise edition and with the parallel option specified you may have a multi part dumpfile, in which case your impdp command should look something like:

impdp system/<password>@<ORACLE_SID> sqlfile=dumpfile_sql.txt directory=data_pump_dir dumpfile=dumpfile_%u.dmp

From the resulting file, which has been created in the data pump directory, you can identify the schemas by searching by “CREATE USER” and also the tablespaces by searching for “TABLESPACE”. You’ll likely see a DEFAULT TABLESPACE specified for the user or reference to the tablespace in an ALTER USER statement.

Once you have these details you can then either replicate these in the destination database or use them in conjunction with remap_schema and remap_tablespace within your impdp e.g.

remap_schema=exported_schema:import_schema

and

remap_tablespace=exported_tablespace:import_tablespace
Advertisements

From → Oracle

Leave a Comment

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: