I have a 14g DMP file. How large should I size my tablespace to import it into an Oracle 11g database?

  • Fire up a VM, run Oracle 11.2. Import that file and see how big it is. Then, add that + 50% (varies on how active you database/file is). – Vérace Jun 30 '15 at 21:17
  • maybe offtopic, but usually it is helpfull to execute the rman command "report schema" on source db, to get report about datafile sizes. Also you can use imp option show=yes to get ddls for tablepsaces (is its full database export). – ibre5041 Jun 30 '15 at 21:18
  • @Verace, thanks. I'm simply using it for reporting. I assumed that I needed to create a tablespace before importing the dmp. file and that create tablespace command required a size. Import it to figure how to size it seems to be cart than horse. – John Jun 30 '15 at 21:23
  • It's my understanding of Oracle that if you don't specify tablespace, it'll just end up in the default one. If you do that and get the size before and after the operation, then you'll know the space. You can then delete your VM and import into your real system. I don't know, maybe you have a perfect test mirror of your PROD system, but I know all too well that's frequently not the case, hence the VM suggestion. – Vérace Jun 30 '15 at 21:30

At least 14 GB.

Maybe more if the dump file is compressed.

Maybe less if the tables are compressed.

A bit more or lot more if there are indexes.

Even Data Pump will miscalculate the required space in case of compressed tables.

Index data is not stored in the dump file, only index definitions. You can have a 14 GB dump file with 14 GB table data and 0 index definitions or hundreds of index definitions, and building those indexes may consume a lot more storage than the base tables, or just a fraction of it.

So the correct answer in my opinion is, test and measure it.

  • Will importing the data cause the index to re-build or will I need to do that manually? – John Jul 1 '15 at 13:56
  • Indexes will be built automatically for new tables, maintained automatically for existing tables. – Balazs Papp Jul 1 '15 at 14:04

Using a combination of MASTER_ONLY and KEEP_MASTER switches, it's possible to get Data Pump import to do the dependency tracking, calculations, and estimations of sizes that it does at the beginning, create the master table that it uses to track everything, and then stop.

You can then examine the master table to see how big the various tables are estimated to be.

Remember that when importing data, if importing to a schema/user which has a default tablespace of "XYZ", then the data will go into that tablespace no matter what tablespace it came from on the originating database.

  • Thanks for your insight. As you probably surmised, I'm not a dba and don't pretend to be one. Further I've never had access to the source database only the dmp file so I'm not sure what I'm getting myself into. – John Jul 1 '15 at 13:55
  • I couldn't find this yesterday, but got it now... link to some info on what you can do in this kind of situation: dbaharrison.blogspot.de/2014/10/… – Phil Sumner Jul 2 '15 at 7:55

Assuming you are not using bigfile tablespaces and assuming you have no standard for how large datafiles should be or how empty they should be, you should create a tablespace with one 14G datafile and allow it to autoextend to a reasonable percentage of the overall free space you have available. If you have plenty of free space, you should set the maximum to unlimited (which is actually a finite amount dependent on your block size). By doing this you can set an amount for the autoextention that reflects your balance between system performance due to frequent autoextending and the space benefits of allocating only as much space as the objects need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.