plsql datapump and excluding both job types

Today I've spent a few hours dealing with creating an updated version of our streams recreation script. Streams is both wonderful and frustrating at the same time, I have somewhat of a love-hate relationship with it. After a successful day improving the script I thought I'd share one of the most useful aspects i had to amend to get the script to do everything in a nice clean way.

The script at the moment is set up to replicate 7 schemas across to a remote  (1000km away) datacentre - this works well but occasionally we get issues where new code deployments to the primary do not replicate properly to the remote site - something to do with the schema running the ddl not being the owner of the object - it's some kind of weird quirk. Anyway if that's not spotted quickly enough there are so many archive logs generated on the primary that's its often much more practical to recreate the entire config - possible in our case as the replica is purely a read only copy.

The script used to have a load of manual steps post instantiation to change some parameters and tidy up some bits and pieces on the remote side before we had a fully working copy.

One of the most annoying parts of this were jobs, they were created on the remote copy as part of instantiation - and if not removed could cause updates to the remote data which would then break the replication.

I wanted to get these removed at source to not have to clean them up.

Now if you've used datapump a bit you'll know that there is a 'JOB' object type which can be used in exclude/include - however 'JOB' does not include 'SCHEDULER JOB' which seems something of an oversight by oracle.

There is a command line way to solve this though using some code that was first provided by Dean Gagne and mentioned on Mike's blog

https://blogs.oracle.com/UPGRADE/entry/exclude_dbms_scheduler_jobs_from

Quite neat if you've not seen that before - PROCOBJ refers to 'procedural objects' i guess - and the code selects out object types that are related to the scheduler - including the scheduler jobs themselves and as well as window definitions etc

However there was no documented way i could find of doing this using dbms_datapump - so i had to create my own - so here is Dean's code in plsql format - this excludes all scheduler related objects by saying 'not in' for the metadata_filter on PROCOBJ. I'm basically saying give me all procedural objects where the name is not in the the list of scheduler related things i get from the dictionary.

DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'NAME_EXPR',
value => 'NOT IN (SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (47,48,49,50,66,67,68,69,71,72,74))',
object_path => 'PROCOBJ');

combined with this code to exclude old style jobs below

dbms_datapump.metadata_filter (
handle => l_dp_handle,
name => 'EXCLUDE_PATH_EXPR',
value => 'IN (''JOB'')');

means that i can do a full plsql script to do my entire streams instantiation excluding jobs of old style and new style.