Thursday, January 5, 2012

DBMS_DATAPUMP - What Happened to the CONTENT=METADATA_ONLY Parameter?

I've recently taken a step towards learning the Data Pump API to explore new scripting possibilities.  After reviewing the dbms_datapump for 11gR2 reference I noticed a very close relationship to the output of expdp help=y.  I found it quite easy to map the command line parameters to the package options...except for one item, the CONTENTS command line parameter.

The CONTENTS command line parameter allows you to specify metadata only (like rows=no in standard import/export), ALL (default), or DATA_ONLY (rows only, no metadata) as input options.

When browsing the alphabetical list of options in the dbms_datapump package there is no CONTENTS parameter nor is there a CONTENTS option for the dbms_metadata.set_parameter() procedure.

After digging for a bit I found the dbms_datapump.data_filter() procedure to work:


DBMS_DATAPUMP.DATA_FILTER(
   handle      IN NUMBER,
   name        IN VARCHAR2,
   value       IN CLOB,
   table_name  IN VARCHAR2 DEFAULT NULL,
   schema_name IN VARCHAR2 DEFAULT NULL);



DBMS_DATAPUMP.DATA_FILTER(h1,'INCLUDE_ROWS',0,NULL,NULL);

I've left the table name and schema names null to include all.  Setting the value = 0 in conjunction with the INCLUDE_ROWS option disables the unloading of data by datapump (metadata_only!)

The description for the INCLUDE_ROWS option for the data filter:

If nonzero, this filter specifies that user data for the specified table should be included in the job. The default is 1.



My next task will be to write some robust data pump job monitoring scripts...

3 comments:

  1. This article had helped me....

    Thank You

    ReplyDelete
    Replies
    1. Great! that was the point of posting this - to share with others struggling with the same problem.

      Delete
  2. Thank you, it saves me a lot of time!

    ReplyDelete