Oracle 12.1.0.2

I had to load a metadata_only dump on several environnements. Prior to running impdp, I had to create a couple of tablespaces and a couple of users.

 

On environment 1 (ENV1), here's how I created my 2 tablespaces:

CREATE bigfile tablespace ARCHIVE_DATA datafile '/data/cr05/ora1/ARCHIVE_DATA.dbf' size 2G autoextend ON next 500M;

CREATE bigfile tablespace ARCHIVE_INDEX datafile '/data/cr05/ora2/ARCHIVE_INDEX.dbf' size 600M autoextend ON next 500M MAXSIZE 9T;

Then I created my 2 users, and used a par file to remap tablespaces and users for my impdp job, which ran as thus:

impdp system dumpfile=structure80tables.dmp parfile=import80tablesdeOAQ1dansOAL1.par job_name=metadata80tables

So it took 23 minutes total, including 20mn (1185 seconds) on loading 930 empty indexes.  As I said, this is metadata_only, so we're loading empty tables and indexes.  The resulting schema weighs 77GB:

Here you're probably wondering (as i did!) how come so much physical space for empty tables and indexes?  Impdp actually creates the segments for those objects and some indexes have a big INITIAL SEGMENT (up to 500Mb), and there are 968 indexes total!

 

Then I moved on to ENV2. On ENV2, here's how I created my 2 tablespaces:

CREATE bigfile tablespace ARCHIVE_DATA datafile '/data/pkgrcr04/ora1/OAL2/ARCHIVE_DATA.dbf' size 2G autoextend ON next 500M;

CREATE bigfile tablespace ARCHIVE_INDEX datafile '/data/pkgrcr04/ora2/OAL2/ARCHIVE_INDEX.dbf' size 60G autoextend ON next 500M MAXSIZE 9T;

 

Then I created my 2 users, and used a par file to remap tablespaces and users for my impdp job, which ran as thus:

impdp system dumpfile=structure80tables.dmp parfile=import80tablesdeOAQ1dansOAL1et2.par job_name=metadata80tables

This time it took 4 minutes total, including 77 seconds for those 930 empty indexes, because I had pre-allocated 60GB!