What tablespaces do i need to import a datapump dumpfile?

Sometimes when you receive a datapump export file from a 3rd party source and are asked to import it you are given very little information about it's contents. For example the tablespace names and sizes you might need to create to hold all the objects.

This trick lets you see roughly what you will need before you do the import for real

So the first thing we do is upload just the master table from the dumpfile and make sure that it doesn't get removed at the end of the job - we do this with the less commonly used parameters below

impdp user/pass master_only=y keep_master=y directory=xx dumpfile=xx

Import: Release 11.2.0.3.0 - Production on Thu Oct 23 08:22:14 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Master table "OPS$ORACLE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Job "OPS$ORACLE"."SYS_IMPORT_FULL_01" successfully completed at 08:22:15

You can see this runs really quickly (just 1 second).

Now we have the master table we can query it to find out information about what is in the file - so in the simple case below we can see there is only one tablespace and the objects in it are only 1472 bytes (and empty table in the example here)


OPS$ORACLE@DB>select OBJECT_TABLESPACE,sum(SIZE_ESTIMATE) from SYS_IMPORT_FULL_01 where object_tablespace is not null group by OBJECT_TABLESPACE;

OBJECT_TABLESPACE              SUM(SIZE_ESTIMATE)
------------------------------ ------------------
XX                                           1472

You could of course just keep importing and then dropping users/objects until you create everything you need - this just seems a bit more professional.... :-)