Oracle: Scheduler jobs in Oracle Database.
In this article we will learn how to create jobs and schedule the jobs.
You create one or more jobs using the DBMS_SCHEDULER.CREATE_JOB.
You must have the CREATE JOB privilege to create a job in your own schema, and the CREATE ANY JOB privilege to create a job in any schema except SYS.
Create schema and build tables
|
1 2 |
SYS>>grant dba to ktuser identified by ktuser; Grant succeeded. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SYS>>conn ktuser/ktuser Connected. KTUSER>>@demobld Building demonstration tables. Please wait. Demonstration table build is complete. KTUSER>>select * from tab; TNAME TABTYPE CLUSTERID ------- ---------- BONUS TABLE DEPT TABLE DUMMY TABLE EMP TABLE SALGRADE TABLE KTUSER>>select count(*) from emp; COUNT(*) ---------- 14 |
In emp table we have 14 records.
Now we will create a job using dbms_scheduler.create_job package.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'ktuser.UPDATESTATS' ,start_date => systimestamp ,repeat_interval => 'FREQ=HOURLY;INTERVAL=1' ,end_date => NULL ,job_class => 'DEFAULT_JOB_CLASS' ,job_type => 'PLSQL_BLOCK' ,job_action => 'begin dbms_stats.gather_schema_stats( ownname=>''KTUSER''); end;' ,comments => 'Gather statistics for KTUSER schema' ); SYS.DBMS_SCHEDULER.ENABLE (name => 'KTUSER.UPDATESTATS'); END; / |
The above package will create a job and main intention of job is to gather stats of KTUSER schema for every one hour.
To Know more examples about creation of jobs please check below link :
To know all the jobs in database using dba _scheduler_jobs view.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
KTUSER>> SELECT owner, job_name, enabled FROM dba_scheduler_jobs; OWNER Job-Name ENABL -------- ------------------------------ ----- SYS XMLDB_NFS_CLEANUP_JOB FALSE SYS LOAD_OPATCH_INVENTORY FALSE SYS SM$CLEAN_AUTO_SPLIT_MERGE TRUE SYS RSE$CLEAN_RECOVERABLE_SCRIPT TRUE SYS FGR$AUTOPURGE_JOB FALSE SYS BSLN_MAINTAIN_STATS_JOB TRUE SYS DRA_REEVALUATE_OPEN_FAILURES TRUE SYS HM_CREATE_OFFLINE_DICTIONARY FALSE SYS ORA$AUTOTASK_CLEAN TRUE SYS FILE_SIZE_UPD TRUE SYS CLEANUP_ONLINE_PMO TRUE SYS CLEANUP_TRANSIENT_PKG TRUE SYS CLEANUP_TRANSIENT_TYPE TRUE SYS CLEANUP_TAB_IOT_PMO TRUE SYS CLEANUP_ONLINE_IND_BUILD TRUE SYS CLEANUP_NON_EXIST_OBJ TRUE SYS PMO_DEFERRED_GIDX_MAINT_JOB TRUE SYS FILE_WATCHER FALSE SYS PURGE_LOG TRUE KTUSER UPDATESTATS TRUE 20 rows selected. |
To check job running details use dba_scheduler_jobs
|
1 2 3 4 5 6 7 8 9 |
KTUSER>>set linesize 500 KTUSER>>col job_name for a15 KTUSER>>col LAST_START_DATE for a25 KTUSER>> col NEXT_RUN_DATE for a25 KTUSER>>col RAISE_EVENTS for a15 KTUSER>> SELECT owner, job_name, enabled,NEXT_RUN_DATE,RAISE_EVENTS,LAST_START_DATE from dba_scheduler_jobs where JOB_NAME='UPDATESTATS'; OWNER Job-Name ENABL NEXT_RUN_DATE RAISE_EVENTS LAST_START_DATE -------- --------------- ----- ------------------------- --------------- ------------------------- KTUSER UPDATESTATS TRUE 25-FEB-19 01.56.39.631248 25-FEB-19 12.56.39.609557 PM +05:30 PM +05:30 |
To check whether job has been successfully running or not using dba_scheduler_job_run_details
|
1 2 3 4 5 |
KTUSER>> select log_date, job_name, status, actual_start_date, run_duration from dba_scheduler_job_run_details where job_name='UPDATESTATS' order by 1 LOG_DATE Job-Name STATUS ACTUAL_START_DATE RUN_DURATION ------------------------------------ ------------- ---------- ---------------- -------------------------------------------------------- 25-FEB-19 12.56.40.591253 PM +05:30 UPDATESTATS SUCCEEDED 25-FEB-19 12.56.39.609599 PM +05:30 +000 00:00:01 |
Finally check by running job , Schema stats gathered or not .
|
1 2 3 4 5 6 7 8 9 |
KTUSER>>select OWNER,TABLE_NAME , NUM_ROWS , BLOCKS , EMPTY_BLOCKS ,CHAIN_CNT, AVG_ROW_LEN CACHE, to_char(LAST_ANALYZED,'DD-MON-YYYY HH24:MI:SS') LAST_ANALYZED from dba_tables where owner='&OWNER'; OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT CACHE LAST_ANALYZED --------------- -------------------- ---------- ---------- ------------ ---------- ---------- ------------------------------ KTUSER EMP 14 5 0 0 38 25-FEB-2019 12:56:40 KTUSER DEPT 4 5 0 0 20 25-FEB-2019 12:56:40 KTUSER BONUS 0 0 0 0 0 25-FEB-2019 12:56:40 KTUSER SALGRADE 5 5 0 0 10 25-FEB-2019 12:56:40 KTUSER DUMMY 1 5 0 0 2 25-FEB-2019 12:56:40 |
How to Enable and disable a job ?
Disable a job
Execute below package to disable job
|
1 2 3 4 5 |
sys>>begin SYS.DBMS_SCHEDULER.DISABLE (name => 'KTUSER.UPDATESTATS'); END; / |
Check job status now.
|
1 2 3 4 5 |
KTUSER>>SELECT owner, job_name, enabled FROM dba_scheduler_jobs where job_name='UPDATESTATS'; OWNER Job-Name ENABL --------------- ------------------------------ ----- KTUSER UPDATESTATS FALSE |
Enable a job
|
1 2 3 4 5 |
sys>> begin SYS.DBMS_SCHEDULER.ENABLE (name => 'KTUSER.UPDATESTATS'); END; / |
Check job status now
|
1 2 3 4 5 |
KTUSER>>SELECT owner, job_name, enabled FROM dba_scheduler_jobs where owner='KTUSER'; OWNER Job-Name ENABL --------------- ------------------------------ ----- KTUSER UPDATESTATS TRUE |
How to Drop a job
|
1 2 3 4 5 |
BEGIN SYS.DBMS_SCHEDULER.DROP_JOB (job_name => 'KTUSER.UPDATESTATS'); END; / |
Thank You ….
Note: Please test scripts in Non Prod before trying in Production.
Loading...
