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:

keep_master=y

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,
       object_type,
       completed_rows as number_exported,
       object_type_path,
       (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,

       tab_owner,

       tab_name,

       obj_tbs,

       round((est_size / (1024 * 1024 * 1024)), 2) || ' GB' estimated_size,

       round((dump_length / (1024 * 1024 * 1024)), 2) || ' GB' actual_SIZE,

       unload_meth,

       completed_rows,

       elapsed_time

  from (select base_object_schema,

               object_name,

               (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,

               completed_rows,

               elapsed_time,

               process_order,

               dump_length,

               unload_method

          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.