Squeezing info out of the master table

Now the default log generated from datapump tells you enough of the basic detail to be useful (and is further enhanced in 12c) however there is more information that the log could be telling us that it doesn't.

This information can be squeezed out of the master table, where datapump keeps all its processing information, as long as you don't throw this away (which unfortunately is the default....)

So first up for it to be useful to us after the event we need to keep the table - to do this we simply specify:


A simple extra switch to the export. If we do this all that juicy metadata is left behind.

So assuming we did that what can we do with that information?

Well here are a couple of examples - these two assume you did a full db export and the master table is called OSAORACLE.SYS_EXPORT_FULL_06 (now the chance of your table being called that are pretty much zero - so you'll need to replace it with the appropriate table name).

So first example is this

select object_path_seqno,
       completed_rows as number_exported,
       (completion_time - start_time) * 60 * 60 * 24 as elapsed
  from osaoracle.sys_export_full_06
 where process_order = -5
 order by object_path_seqno

This produces the following output

So we can see how many objects of a certain type were extracted, the order they were done in and how long that extract took

Tables are a little more tricky as the metadata is split over a couple of lines - but we can sort that with some basic analytics - so the next example is this

select process_order,
       round((est_size / (1024 * 1024 * 1024)), 2) || ' GB' estimated_size,
       round((dump_length / (1024 * 1024 * 1024)), 2) || ' GB' actual_SIZE,
  from (select base_object_schema,
               (lag(object_name, 1) over(order by process_order)) as TAB_NAME,
               (lag(base_object_schema, 1) over(order by process_order)) as TAB_OWNER,
               (lag(object_tablespace, 1) over(order by process_order)) as obj_tbs,
               (lag(size_estimate, 1) over(order by process_order)) as est_size,
               (lag(unload_method, 1) over(order by process_order)) as unload_meth,
          from osaoracle.sys_export_full_06
         where object_type_path = 'DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA'
           and process_order >= 1)
 where object_name is null
 order by process_order

So with this we get some extra useful info, estimated size vs actual, tablespace names for objects, unload method used and elapsed time.

You might think this all pretty pointless but if you are trying to analyze where all the time is spent on export (and indeed import - though different queries are needed here) it can be a very useful tool.


  1. Useful blog. Every post is crisp and simple. Thanks for your time.

  2. Müşterilerimizin tam ihtiyacı olan çözümü belirleyip, tüm beklentileriniz karşılayacak şekilde hizmet vermeyi amaçlıyoruz.

  3. http://tekdennakliyat-http://tekdennakliyat.com/ 12-08T19:29:12+00:00
    Kayseri Evden eve Tekden Nakliyat Kayseri’de evden eve sektöründe yılların vermiş olduğu tecrübe ve güven ile siz Kayseri halkına en kaliteli ve en güvenilir hizmeti vermek için yıllardır bu sektörde faaliyet
    göstermektedir 2005 yılından bu yana Kayseri halkımıza evden eve sektöründe hizmet veren evden eve evden eve taşımacılık evden eve firmaları evden eve nakliyat Tekden evden eve ile kolaylaşmıştır biz günümüzün ve çağımızın en modelin ve en teknolojik aletleri ile evden eve sektöründe faaliyet göstermeKayseri Tekden Evden Eve NakliyatBy ADEM Kasım 15, 2017 KAYSERİ TEKDEN EVDEN EVE NAKLİYAT2018kteyiz çağımızın gerektirdiği gibi artık işçilik yerine nine kendini hep ön Piranha koyan teknolojik araçlarımız yer almaktadır teknolojik araçlar ile yapılan ev işlerinde kırılma dökülme ve hasar en bölümün
    seviyeye indirilmiştir.http://tekdennakliyat.com/