How to identify the Tablespace names from an EXPDP file

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.








Leave a Reply

Your email address will not be published. Required fields are marked *