Oracle Scheduler info schedule.sql

@schedule.sql



set pagesize 1000
set trimout on
set trimspool on
Set Feedback off
set timing off
set verify off

prompt

prompt -- ----------------------------------------------------------------------- ---

prompt -- Scheduler Attribute ---

prompt -- ----------------------------------------------------------------------- ---

prompt

Set linesize 180
Set Pagesize 50


column an format a28 word_wrapped heading "Attribute|Name"
column v format a28 word_wrapped heading "Value"

select
attribute_name an
, value v
from
Dba_Scheduler_Global_Attribute
Order
By attribute_name
;


prompt

prompt -- Schedule jobs state (users only)

prompt -- ----------------------------------------------------------------------- ---


column status format a120 wrap heading "Status"


Set Heading Off
Set Feedback Off


Select status_01||' | '||status_02 status
From
(Select ' Schedule Ok '||Lpad(count(*),16) status_01 from Dba_Scheduler_Jobs where state <> 'DISABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM'))
, (Select ' Schedule Disabled '||Lpad(count(*),13) status_02 from Dba_Scheduler_Jobs where state = 'DISABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM'))
;


Prompt

Prompt

prompt -- Schedule jobs state (full)

prompt -- ----------------------------------------------------------------------- ---

set head on

column st format a10 word_wrapped heading "State"
column o format a10 word_wrapped heading "Owner"
column en format a10 word_wrapped heading "Enabled"
column ct format 999999 heading "Count"

clear breaks
break on st -
skip 1
compute Sum of ct on st

select state st
, enabled en
, owner o
, count(*) ct
from Dba_Scheduler_Jobs
Group
By state
, enabled
, owner
Order By state
, enabled
, owner
;


prompt -- Schedule jobs details

prompt -- ----------------------------------------------------------------------- ---

column o format a10 word_wrapped heading "Owner"
column jn format a30 word_wrapped heading "Job Name|(Subname - Creator)"
column jt format a17 word_wrapped heading "Job Type"
column ja format a38 heading "Job Action"
column st format a9 word_wrapped heading "State"
column lsd format a16 word_wrapped heading "Last Start Date"
column nrd format a16 word_wrapped heading "Next Run Date"
column fc format 9999 heading "Fail.|Ct."
column rc format 999999 heading "Run|Ct."
column en format a6 word_wrapped heading "Enab."
column ri format a30 word_wrapped heading "Rep.|Int."
column sosn format a24 word_wrapped heading "Sched. |Owner|Sched. Name"
column sowc format a5 word_wrapped heading "Stop|On|Wind.|Close"
column ad format a5 word_wrapped heading "Auto|Drop"

clear breaks
break on o -
skip 1

select
owner o
, job_name||'('||decode(job_subname,null,'',job_subname||' - ') ||job_creator||')' jn
, job_type jt
, replace(Substr(job_action,1,38),chr(10),' ') ja
, state st
, to_char(last_start_date, 'DD-MM-YYYY HH24:MI') lsd
, to_char(next_run_date, 'DD-MM-YYYY HH24:MI') nrd
-- , substr(repeat_interval,1,30) ri
, failure_count fc
, run_count rc
, enabled en
-- , decode(schedule_owner,null,'',schedule_owner||' - ')||schedule_name sosn
, stop_on_window_close sowc
, auto_drop ad
from
Dba_Scheduler_Jobs
Order
By owner
, state
, enabled
, job_name
;

clear breaks



--======================

SQL> @schedule.sql

-- ----------------------------------------------------------------------- --
-- Scheduler Attribute --
-- ----------------------------------------------------------------------- --

Any Key...;

Attribute
Name Value
---------------------------- ----------------------------
CURRENT_OPEN_WINDOW
DEFAULT_TIMEZONE GMT
EMAIL_SENDER
EMAIL_SERVER
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT 0
LAST_OBSERVED_EVENT
LOG_HISTORY 30
MAX_JOB_SLAVE_PROCESSES

-- Schedule jobs state (users only)
-- ----------------------------------------------------------------------- --
Any Key...;

Schedule Ok 4 | Schedule Disabled 0


-- Schedule jobs state (full)
-- ----------------------------------------------------------------------- --
Any Key...;

State Enabled Owner Count
---------- ---------- ---------- -------
DISABLED FALSE SYS 4
********** -------
sum 4

SCHEDULED TRUE APEX_04000 4
0

TRUE EXFSYS 2
TRUE ORACLE_OCM 2
TRUE SYS 6
********** -------
sum 14

-- Schedule jobs details
-- ----------------------------------------------------------------------- --
Any Key...;

Stop
On
Job Name Fail. Run Wind. Auto
Owner (Subname - Creator) Job Type Job Action State Last Start Date Next Run Date Ct. Ct. Enab. Close Drop
---------- ------------------------------ ----------------- -------------------------------------- --------- ---------------- ---------------- ----- ------- ------ ----- -----
APEX_04000 ORACLE_APEX_DAILY_MAINTENANCE( STORED_PROCEDURE WWV_FLOW_MAINT.DAILY_MAINTENANCE SCHEDULED 17-10-2011 01:00 18-10-2011 01:00 0 126 TRUE FALSE FALSE
0 SYS)

ORACLE_APEX_MAIL_QUEUE(SYS) STORED_PROCEDURE WWV_FLOW_MAIL.PUSH_QUEUE SCHEDULED 17-10-2011 15:35 17-10-2011 15:40 0 33653 TRUE FALSE FALSE
ORACLE_APEX_PURGE_SESSIONS(SYS STORED_PROCEDURE WWV_FLOW_CACHE.PURGE_SESSIONS SCHEDULED 17-10-2011 15:00 17-10-2011 16:00 0 2818 TRUE FALSE FALSE
)

ORACLE_APEX_WS_NOTIFICATIONS(S STORED_PROCEDURE WWV_FLOW_WORKSHEET_API.DO_NOTIFY SCHEDULED 17-10-2011 15:30 17-10-2011 16:00 0 5622 TRUE FALSE FALSE
YS)


EXFSYS RLM$EVTCLEANUP(SYS) PLSQL_BLOCK begin dbms_rlmgr_dr.cleanup_events; en SCHEDULED 17-10-2011 08:31 17-10-2011 09:31 0 5005 TRUE FALSE FALSE
RLM$SCHDNEGACTION(SYS) PLSQL_BLOCK begin dbms_rlmgr_dr.execschdactions('R SCHEDULED 17-10-2011 15:24 17-10-2011 16:21 0 5207 TRUE FALSE FALSE

ORACLE_OCM MGMT_CONFIG_JOB(SYS) STORED_PROCEDURE ORACLE_OCM.MGMT_CONFIG.collect_config SCHEDULED 15-10-2011 23:00 0 212 TRUE FALSE FALSE
MGMT_STATS_CONFIG_JOB(SYS) STORED_PROCEDURE ORACLE_OCM.MGMT_CONFIG.collect_stats SCHEDULED 03-10-2011 08:17 01-11-2011 01:01 0 10 TRUE FALSE FALSE

SYS FGR$AUTOPURGE_JOB(SYS) PLSQL_BLOCK sys.dbms_file_group.purge_file_group(N DISABLED 0 0 FALSE FALSE TRUE
FILE_WATCHER(SYS) DISABLED 0 0 FALSE FALSE FALSE
HM_CREATE_OFFLINE_DICTIONARY(S STORED_PROCEDURE dbms_hm.create_offline_dictionary DISABLED 0 0 FALSE FALSE FALSE
YS)

XMLDB_NFS_CLEANUP_JOB(SYS) STORED_PROCEDURE xdb.dbms_xdbutil_int.cleanup_expired_n DISABLED 0 0 FALSE FALSE TRUE
BSLN_MAINTAIN_STATS_JOB(SYS) SCHEDULED 16-10-2011 00:00 23-10-2011 00:00 0 37 TRUE FALSE FALSE
DRA_REEVALUATE_OPEN_FAILURES(S STORED_PROCEDURE dbms_ir.reevaluateopenfailures SCHEDULED 15-10-2011 23:00 0 212 TRUE FALSE FALSE
YS)

ORA$AUTOTASK_CLEAN(SYS) SCHEDULED 17-10-2011 03:00 18-10-2011 03:00 0 225 TRUE FALSE FALSE
PURGE_LOG(SYS) SCHEDULED 17-10-2011 03:00 18-10-2011 03:00 0 225 TRUE FALSE FALSE
RSE$CLEAN_RECOVERABLE_SCRIPT(S PLSQL_BLOCK sys.dbms_streams_auto_int.clean_recove SCHEDULED 17-10-2011 00:00 18-10-2011 00:00 0 222 TRUE FALSE TRUE
YS)

SM$CLEAN_AUTO_SPLIT_MERGE(SYS) PLSQL_BLOCK sys.dbms_streams_auto_int.clean_auto_s SCHEDULED 17-10-2011 00:00 18-10-2011 00:00 0 222 TRUE FALSE TRUE