DBMS_SCHEDULER

DBMS_SCHEDULER


set feedback off
set echo off
set lines 205
column owner format a15
column job_name format a22
column program_name format a24
column status format a10
column state format a10
column last_start format a25
column last_start_norm format a18
column "DURATION (d:hh:mm:ss)" format a21
column next_run format a25
column next_run_norm format a18

var local_offset number
begin
   select extract(timezone_hour from systimestamp) into :local_offset from dual;
end;
/

select dsj.owner,
       dsj.job_name,
       dsj.program_name,
       dsjlmax.status,
       dsj.state,
--     to_char(dsj.last_start_date,'dd-mon-yyyy hh24:mi TZH:TZM') last_start,
       to_char(dsj.last_start_date + (:local_offset-extract(timezone_hour from dsj.last_start_date))/24,'dd-mon-yyyy hh24:mi') last_start_norm,
       extract(day from dsj.last_run_duration) ||':'||
          lpad(extract(hour from dsj.last_run_duration),2,'0')||':'||
          lpad(extract(minute from dsj.last_run_duration),2,'0')||':'||
          lpad(round(extract(second from dsj.last_run_duration)),2,'0') "DURATION (d:hh:mm:ss)",
--     to_char(dsj.next_run_date,'dd-mon-yyyy hh24:mi TZH:TZM') next_run,
       to_char(dsj.next_run_date + (:local_offset-extract(timezone_hour from dsj.next_run_date))/24,'dd-mon-yyyy hh24:mi') next_run_norm
from dba_scheduler_jobs dsj,
(select job_name, status
from dba_scheduler_job_log dsjl
where log_date =
   (select max(log_date)
    from dba_scheduler_job_log dsjl2
    where dsjl2.job_name = dsjl.job_name)
) dsjlmax
where dsj.owner not in ('SYSTEM','EXFSYS','ORACLE_OCM')
and dsj.job_name = dsjlmax.job_name;

------------------------------------------------------------------------------------
select obj#, program_action, comments from sys.scheduler$_job;

select client_name, status from dba_autotask_client;

dba_job_run_scheduler_details 
 
-------------------------------------------------------------------
 column job_name format a10
 column job_creator format a10
 column job_action format a10
 column start_date format a40
 column repeat_interval format a30

 select job_name, job_creator, job_type, job_action,start_date
 from user_scheduler_jobs;

Monitoring job-scheduling
Jobs can be monitored using Oracle Enterprise Manager 10g. It’s also possible to use a number of views that have been created in Oracle 10g. We will discuss some of these views here.

To show details on job run:
select log_date
,      job_name
,      status
,      req_start_date
,      actual_start_date
,      run_duration
from   dba_scheduler_job_run_details
 
To show running jobs:
select job_name
,      session_id
,      running_instance
,      elapsed_time
,      cpu_used
from dba_scheduler_running_jobs;
 
To show job history:
 select log_date
 ,      job_name
 ,      status
 from dba_scheduler_job_log;
 
 
show all schedules:
select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;
 
show all jobs and their attributes:
select *
from dba_scheduler_jobs
 
 
show all program-objects and their attributes

select *
from dba_scheduler_programs;
 
show all program-arguments:
select *
from   dba_scheduler_program_args;
  
select log_date ,JOB_NAME, STATUS, ERROR# from dba_scheduler_job_run_details where JOB_NAM
E like '%REFRESH_SPIDER%' and log_date > sysdate – 5
 
SELECT * FROM dba_scheduler_programs;
 
SELECT * FROM dba_scheduler_schedules;
 
select log_date
,      job_name
,      status
,      req_start_date
,      actual_start_date
,      run_duration
from   dba_scheduler_job_run_details
 
 
SELECT * FROM dba_scheduler_jobs;
 
SELECT *
FROM   dba_scheduler_window_groups;
SELECT *
FROM   dba_scheduler_windows;
 

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Oracle Standby database

Shutting Down a Physical Standby Database Issue the following query to find out if the standby database is performing Redo Apply or real-time apply.  If the MRP0 or MRP process exists, then the standby database is applying redo. SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; If Redo Apply is running, cancel it as shown in the following example: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Shut down the standby database. SQL> SHUTDOWN IMMEDIATE; Start and mount the physical standby database: SQL> STARTUP MOUNT; Start Redo Apply or real-time apply: To start Redo Apply, issue the following statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT FROM SESSION; To start real-time apply, issue the following statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  USING CURRENT LOGFILE; ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’; select ...
Read more

DBA_LOCKS

select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner from gv$locked_object l, dba_objects o where l.object_id = o.object_id; select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner, 'alter system kill session ''' || s.sid || ',' || s.serial# ||''';' kill_session from v$locked_object l, dba_objects o, v$session s where l.object_id = o.object_id and l.session_id = s.sid; select sid, serial# from gv$session where sid = 0000; alter sysetm kill session ''; select s.sid, s.serial#, p.spid unix_process, s.osuser, s.username, s.machine, s.program, s.sql_hash_value current_hash_value, q1.sql_text current_sql_text, s.prev_hash_value prev_hash_value, q2.sql_text prev_sql_text, to_char(s.logon_time,'DD/MM/YYYY HH24:MI') session_logon_time, o.owner ||'.'|| o.object_name locked_object, l.type lock_type, l.lmode lock_mode, l.ctime lock_time_in_sec from v$lock l, v$session s, v$pro...
Read more