I have a dump file using expdp with compression (oracle version >= 11g). Before running impdp I want to check if there is enough space on the machine but it seems as there is no parameter in the impdp utility to assists me (ESTIMATE=BLOCKS doesn't work)

What are my possibilities?

  • My answer [here][1] should answer your question. [1]: dba.stackexchange.com/questions/105601/… – Phil Sumner Aug 25 '15 at 9:24
  • select sum(a.SIZE_ESTIMATE)/1024/1024 from "NIR_TEST"."SYS_SQL_FILE_FULL_01" a gives me size which is 13% of the actual size I get by dba_segments. Is that because of the compression? Is there other method? – Nir Aug 25 '15 at 9:40
  • Are all those segments actually used? Do you have lots of free space under the HWM? – Phil Sumner Aug 25 '15 at 9:47
  • The schema were imported using impdp so I believe most of it is used. – Nir Aug 25 '15 at 13:25
  • I think you misunderstood my question. dba_segments records how many segments are allocated for storage. It is possible to have a large number of those empty because whenever you insert data, the way that you do it causes it to be inserted above the High Water Mark (e.g. direct path inserts). Data pump only exports actual data, so it's not impossible that only 13% of the space allocated is being used for storage. It's a fairly extreme example, but it's possible. – Phil Sumner Aug 26 '15 at 13:53

Your Answer

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

Browse other questions tagged or ask your own question.