Skip to Main Content
  • Questions
  • DBMS_DATAPUMP.metadata_filter to exclude scheduled jobs

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dennis.

Asked: April 06, 2016 - 3:27 pm UTC

Last updated: October 08, 2021 - 1:13 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

I'm using the datapump api to export schema objects.

My issue is the user I'm trying to export has scheduled jobs and I want to exclude them

Below is the SQL to get a list of jobs for the user
select * from dba_scheduler_jobs
where owner = 'DC_PLAY'

below is my filer and I'm getting an error ORA-39001 invalid argument value.

DBMS_DATAPUMP.metadata_filter (
v_handle,
'EXCLUDE_PATH_EXPR',
'IN (SELECT NAME FROM sys.OBJ$ WHERE TYPE# = 66)',
'PROCOBJ');

I did verify that the sub query is returning what I need to exclude.

Thanks in advance

and Connor said...

Have you tried using the standard filter ?

SQL> SELECT OBJECT_PATH, COMMENTS FROM SCHEMA_EXPORT_OBJECTS
  2  where object_path like '%JOB%';

OBJECT_PATH
-------------------------------------------------------------
COMMENTS
-------------------------------------------------------------
JOB
Jobs in the selected schemas

SCHEMA_EXPORT/JOB
Jobs in the selected schemas


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very Helpful

Dennis Ching, April 07, 2016 - 1:08 pm UTC

Very prompt and easy to use site.

Many thanks!
Connor McDonald
April 08, 2016 - 2:39 am UTC

glad we could help

Mauro, October 05, 2021 - 8:57 am UTC

Which kind of jobs are they?
The ones created by DBMS_JOB, the ones created by DBMS_SCHEDULER, both of them?
I perused the docs but I was unable to find any details
Best regards
Mauro
Connor McDonald
October 08, 2021 - 1:13 am UTC

These are scheduler jobs because they are a database object (ie, you'll see them in DBA_OBJECTS).

Jobs from DBMS_JOB (pre 19) are just rows in a table, so harder to manage.


More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.