How can I list the scheduled jobs running in my database?

Question

I am new to oracle. I need to get the scheduled jobs in my database.

I queried

DBA_SCHEDULER_JOBS, 
DBA_SCHEDULER_SCHEDULES, DBA_SCHEDULER_PROGRAMS,
 DBA_SCHEDULER_JOB_CLASSES, DBA_JOBS.

But Oracle issues the error

"ORA-00942: table or view does not exist".

When I queried ALL_JOBS and USER_JOBS no rows are retrieved. Please suggest me which table should I see.

Solution 1

The DBA views are restricted. So you won't be able to query them unless you're connected as a DBA or similarly privileged user.

The ALL views show you the information you're allowed to see. Normally that would be jobs you've submitted, unless you have additional privileges.

The privileges you need are defined in the Admin Guide. Find out more.

So, either you need a DBA account or you need to chat with your DBA team about getting access to the information you need.

Solution 2

I think you need the SCHEDULER_ADMIN role to see the dba_scheduler tables (however this may grant you too may rights)

see: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/schedadmin001.htm

Solution 3

Because the SCHEDULER_ADMIN role is a powerful role allowing a grantee to execute code as any user, you should consider granting individual Scheduler system privileges instead. Object and system privileges are granted using regular SQL grant syntax. An example is if the database administrator issues the following statement:

GRANT CREATE JOB TO scott;

After this statement is executed, scott can create jobs, schedules, or programs in his schema.

copied from http://docs.oracle.com/cd/B19306_01/server.102/b14231/schedadmin.htm#i1006239

Comments

[Admin]

If you'd rather not read the manual: SELECT * FROM ALL_SCHEDULER_JOBS;.