More datapump trickery



We have a requirement to extract a number of schemas (with hundreds of tables), for most of them we want just the metadata (table definitions etc) but for about a dozen of them we need the data, these are spread over multiple schemas. We'd like to be able to do the export as a single job to keep things simple.

Is this possible or do we have to split it over multiple exports?

At first glance you think we can do this, then you think you can't when you get in to the detail, then you realise you can actually do it using a bit of a trick - which is shown below. Here is the parfile we ended up creating:

full=y
include=SCHEMA:"IN ('DWH_DM','DWH_IN','DWH_ST','MD_MAINTENANCE','ETL_USER','DWH_META','BOUSER','UTL_OWNER','MDA_USER','DS_PROD_LOCAL')"
directory=DATA_PUMP_DIR
QUERY="where 1=0"
QUERY=MD_MAINTENANCE.FELDLISTE:"where 1=1"
QUERY=MD_MAINTENANCE.TYP:"where 1=1"
QUERY=UTL_OWNER.MD_TAB_STAT_PREFS:"where 1=1"
QUERY=UTL_OWNER.MD_SCHEDULES:"where 1=1"
QUERY=UTL_OWNER.MD_PROGRAM_ERROR_CODES:"where 1=1"
QUERY=UTL_OWNER.MD_PROGRAM_DEPENDENCIES:"where 1=1"
QUERY=UTL_OWNER.MD_JOB_DEPENDENCIES:"where 1=1"
QUERY=UTL_OWNER.MD_APPLICATION_RELEASES:"where 1=1"
QUERY=UTL_OWNER.MD_PROGRAMS:"where 1=1"
QUERY=UTL_OWNER.MD_PARAMETERS:"where 1=1"
QUERY=UTL_OWNER.MD_DATA_OBJECTS:"where 1=1"
QUERY=UTL_OWNER.MD_DATA_LAYERS:"where 1=1"
QUERY=UTL_OWNER.MD_DATA_ERROR_CODES:"where 1=1"
QUERY=UTL_OWNER.MD_APPLICATIONS:"where 1=1"
QUERY=DWH_META.META_STAGING_TABLE_LIST:"where 1=1"
QUERY=DWH_META.META_DF_REC_STEPS:"where 1=1"
QUERY=DWH_META.META_RECOVERY_WF_LIST:"where 1=1"
QUERY=DWH_META.META_DB_STATS:"where 1=1"
QUERY=DWH_META.RELEASE_TRACKER_TABLE:"where 1=1"


The trick is the clever use of the QUERY parameter. The first QUERY setting apply's a global  "and 1=0" to every data extract - which then results in no rows being returned.

Each subsequent query is specific to the tables we want to fetch and says "and 1=1" so always returns true and fetches all the data.

The table level query= takes precedence over the top level setting so gives us exactly what we want, 99% of the tables with no rows and a limited few with data we do actually want. Quite a nice way of doing this. Previously the job was split into 5 to acheive the same thing.

0 comments:

Post a Comment