How to List and Monitor DBMS Jobs and Scheduler Jobs in Oracle Database

Hi,

I will explain How to List and Monitor DBMS Jobs and Scheduler Jobs in Oracle Database in this post.

 

There are two packages related with Oracle database jobs which are called dbms_job,dbms_scheduler.

DBMS_SCHEDULER offers new features by adding the ability to jobs with specific privileges and roles according to DBMS_JOB.

 

 

For example; Gather Schema Stats job with DBMS_JOB are as follows.

SET SERVEROUTPUT ON
DECLARE
l_job NUMBER;
BEGIN
SELECT MAX (job) + 1 INTO l_job FROM dba_jobs;
DBMS_JOB.submit(l_job,
'BEGIN DBMS_STATS.gather_schema_stats(''MSD'',estimate_percent => dbms_stats.auto_sample_size, degree=>8 ); END;',
trunc(next_day(SYSDATE,'SUNDAY'))+11/24,
'TRUNC (SYSDATE+7)+11/24');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || l_job);
END;
/

 

For example; Kill Long Session ( procedure ) with DBMS_SCHEDULER are as follows.

 

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SYS.KILL_LONG_SESSION'
,start_date => TO_TIMESTAMP_TZ('2020/02/06 16:00:00.000000 +03:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => 'FREQ=MINUTELY;INTERVAL=1'
,end_date => TO_TIMESTAMP_TZ('2090/08/18 00:00:00.000000 +03:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'SYS.KILL_LONG_SESSIONS'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'MAX_RUNS');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'AUTO_DROP'
,value => FALSE);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'SYS.KILL_LONG_SESSION');
END;
/

 

You can list and monitor the DBMS_JOB as follows.

select * from dba_jobs;

 

 

dba_jobs view columns are as follows.

 

SQL> desc dba_jobs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB                                       NOT NULL NUMBER
 LOG_USER                                  NOT NULL VARCHAR2(30)
 PRIV_USER                                 NOT NULL VARCHAR2(30)
 SCHEMA_USER                               NOT NULL VARCHAR2(30)
 LAST_DATE                                          DATE
 LAST_SEC                                           VARCHAR2(8)
 THIS_DATE                                          DATE
 THIS_SEC                                           VARCHAR2(8)
 NEXT_DATE                                 NOT NULL DATE
 NEXT_SEC                                           VARCHAR2(8)
 TOTAL_TIME                                         NUMBER
 BROKEN                                             VARCHAR2(1)
 INTERVAL                                  NOT NULL VARCHAR2(200)
 FAILURES                                           NUMBER
 WHAT                                               VARCHAR2(4000)
 NLS_ENV                                            VARCHAR2(4000)
 MISC_ENV                                           RAW(32)
 INSTANCE                                           NUMBER

SQL>

 

 

 

You can list and monitor the DBMS_SCHEDULER as follows.

select * from dba_scheduler_jobs;

 

 

You can list and view the log of  DBMS_SCHEDULER  as follows.

select * from DBA_SCHEDULER_JOB_LOG;

 

dba_scheduler_jobs view columns are as follows.

 

SQL> desc dba_scheduler_jobs;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                          VARCHAR2(30)
 JOB_NAME                                                       VARCHAR2(30)
 JOB_SUBNAME                                                    VARCHAR2(30)
 JOB_STYLE                                                      VARCHAR2(11)
 JOB_CREATOR                                                    VARCHAR2(30)
 CLIENT_ID                                                      VARCHAR2(64)
 GLOBAL_UID                                                     VARCHAR2(32)
 PROGRAM_OWNER                                                  VARCHAR2(4000)
 PROGRAM_NAME                                                   VARCHAR2(4000)
 JOB_TYPE                                                       VARCHAR2(16)
 JOB_ACTION                                                     VARCHAR2(4000)
 NUMBER_OF_ARGUMENTS                                            NUMBER
 SCHEDULE_OWNER                                                 VARCHAR2(4000)
 SCHEDULE_NAME                                                  VARCHAR2(4000)
 SCHEDULE_TYPE                                                  VARCHAR2(12)
 START_DATE                                                     TIMESTAMP(6) WITH TIME ZONE
 REPEAT_INTERVAL                                                VARCHAR2(4000)
 EVENT_QUEUE_OWNER                                              VARCHAR2(30)
 EVENT_QUEUE_NAME                                               VARCHAR2(30)
 EVENT_QUEUE_AGENT                                              VARCHAR2(256)
 EVENT_CONDITION                                                VARCHAR2(4000)
 EVENT_RULE                                                     VARCHAR2(65)
 FILE_WATCHER_OWNER                                             VARCHAR2(65)
 FILE_WATCHER_NAME                                              VARCHAR2(65)
 END_DATE                                                       TIMESTAMP(6) WITH TIME ZONE
 JOB_CLASS                                                      VARCHAR2(30)
 ENABLED                                                        VARCHAR2(5)
 AUTO_DROP                                                      VARCHAR2(5)
 RESTARTABLE                                                    VARCHAR2(5)
 STATE                                                          VARCHAR2(15)
 JOB_PRIORITY                                                   NUMBER
 RUN_COUNT                                                      NUMBER
 MAX_RUNS                                                       NUMBER
 FAILURE_COUNT                                                  NUMBER
 MAX_FAILURES                                                   NUMBER
 RETRY_COUNT                                                    NUMBER
 LAST_START_DATE                                                TIMESTAMP(6) WITH TIME ZONE
 LAST_RUN_DURATION                                              INTERVAL DAY(9) TO SECOND(6)
 NEXT_RUN_DATE                                                  TIMESTAMP(6) WITH TIME ZONE
 SCHEDULE_LIMIT                                                 INTERVAL DAY(3) TO SECOND(0)
 MAX_RUN_DURATION                                               INTERVAL DAY(3) TO SECOND(0)
 LOGGING_LEVEL                                                  VARCHAR2(11)
 STOP_ON_WINDOW_CLOSE                                           VARCHAR2(5)
 INSTANCE_STICKINESS                                            VARCHAR2(5)
 RAISE_EVENTS                                                   VARCHAR2(4000)
 SYSTEM                                                         VARCHAR2(5)
 JOB_WEIGHT                                                     NUMBER
 NLS_ENV                                                        VARCHAR2(4000)
 SOURCE                                                         VARCHAR2(128)
 NUMBER_OF_DESTINATIONS                                         NUMBER
 DESTINATION_OWNER                                              VARCHAR2(128)
 DESTINATION                                                    VARCHAR2(128)
 CREDENTIAL_OWNER                                               VARCHAR2(30)
 CREDENTIAL_NAME                                                VARCHAR2(30)
 INSTANCE_ID                                                    NUMBER
 DEFERRED_DROP                                                  VARCHAR2(5)
 ALLOW_RUNS_IN_RESTRICTED_MODE                                  VARCHAR2(5)
 COMMENTS                                                       VARCHAR2(240)
 FLAGS                                                          NUMBER

 

 

You can monitor all scheduler jobs run details as follows.

select * from ALL_SCHEDULER_JOB_RUN_DETAILS;

 

 

You can monitor dba scheduler running jobs as follows.

select * from dba_scheduler_running_jobs;

 

You can monitor dba scheduler running jobs details as follows.

select * from dba_scheduler_job_run_details;

 

 

You can run any scheduler jobs manually as follows.

begin 
dbms_scheduler.run_job('JOB_NAME');
end;
/

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

 119 views today,  6 views in last 3 hour

Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply