How to List and Monitor DBMS Jobs and Scheduler Jobs in Orac
发布时间：2023-02-17 09:19:26 所属栏目：Oracle 来源：互联网
导读：I will explain How to List and Monitor DBMS Jobs and Scheduler Jobs in Oracle Database in this post. Monitoring Oracle Jobs There are two packages related with Oracle database jobs which are called dbms_job,dbms_scheduler. DBMS_SCHEDULER J
|
I will explain How to List and Monitor DBMS Jobs and Scheduler Jobs in Oracle Database in this post. Monitoring Oracle Jobs There are two packages related with Oracle database jobs which are called dbms_job,dbms_scheduler. DBMS_SCHEDULER Jobs DBMS_SCHEDULER offers new features by adding the ability to jobs with specific privileges and roles according to DBMS_JOB. List and Monitor DBMS Jobs and Scheduler Jobs in Oracle DBMS_JOB – Gather Schema Stats 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; / DBMS_JOB 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',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',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; / Monitor or List DBMS_JOB 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) （编辑：甘南站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
