Oracle Database Discussions

select * from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB' order by log_id desc

LOG_ID	LOG_DATE	OWNER	JOB_NAME	JOB_SUBNAME	STATUS	ERROR#	REQ_START_DATE	ACTUAL_START_DATE	RUN_DURATION	INSTANCE_ID	SESSION_ID	SLAVE_PID	CPU_USED	ADDITIONAL_INFO	
1741		SYS	GATHER_STATS_JOB	<NULL>	SUCCEEDED	0	<NULL>			1	2316,20537	25785		<NULL>	

select * from dba_scheduler_jobs

OWNER	JOB_NAME	JOB_SUBNAME	JOB_CREATOR	CLIENT_ID	GLOBAL_UID	PROGRAM_OWNER	PROGRAM_NAME	JOB_TYPE	JOB_ACTION	NUMBER_OF_ARGUMENTS	SCHEDULE_OWNER	SCHEDULE_NAME	SCHEDULE_TYPE	START_DATE	REPEAT_INTERVAL	EVENT_QUEUE_OWNER	EVENT_QUEUE_NAME	EVENT_QUEUE_AGENT	EVENT_CONDITION	EVENT_RULE	END_DATE	JOB_CLASS	ENABLED	AUTO_DROP	RESTARTABLE	STATE	JOB_PRIORITY	RUN_COUNT	MAX_RUNS	FAILURE_COUNT	MAX_FAILURES	RETRY_COUNT	LAST_START_DATE	LAST_RUN_DURATION	NEXT_RUN_DATE	SCHEDULE_LIMIT	MAX_RUN_DURATION	LOGGING_LEVEL	STOP_ON_WINDOW_CLOSE	INSTANCE_STICKINESS	RAISE_EVENTS	SYSTEM	JOB_WEIGHT	NLS_ENV	SOURCE	DESTINATION	COMMENTS	FLAGS	
SYS	GATHER_STATS_JOB	<NULL>	SYS	<NULL>	<NULL>	SYS	GATHER_STATS_PROG	<NULL>	<NULL>	<NULL>	SYS	MAINTENANCE_WINDOW_GROUP	WINDOW_GROUP	<NULL>	<NULL>	<NULL>	<NULL>	<NULL>	<NULL>	<NULL>	<NULL>	AUTO_TASKS_JOB_CLASS	TRUE	FALSE	TRUE	SCHEDULED	3	125	<NULL>	0	<NULL>	0			<NULL>	<NULL>	<NULL>	RUNS	TRUE	TRUE	<NULL>	TRUE	1	NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE'	<NULL>	<NULL>	Oracle defined automatic optimizer statistics collection job	21074010	

select * from dba_schedular_schedules

OWNER	SCHEDULE_NAME	SCHEDULE_TYPE	START_DATE	REPEAT_INTERVAL	EVENT_QUEUE_OWNER	EVENT_QUEUE_NAME	EVENT_QUEUE_AGENT	EVENT_CONDITION	END_DATE	COMMENTS	
SYS	DAILY_PURGE_SCHEDULE	CALENDAR	<NULL>	freq=daily;byhour=3;byminute=0;bysecond=0	<NULL>	<NULL>	<NULL>	<NULL>	<NULL>	<NULL>	

SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name='GATHER_STATS_JOB';

select job_name, job_type, program_name, schedule_name, job_class from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';

The actual_start_date and the run_duration will give you how long did it run.

SQL> desc dba_scheduler_job_run_details
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOG_ID                                             NUMBER
 LOG_DATE                                           TIMESTAMP(6) WITH TIME ZONE
 OWNER                                              VARCHAR2(30)
 JOB_NAME                                           VARCHAR2(65)
 JOB_SUBNAME                                        VARCHAR2(65)
 STATUS                                             VARCHAR2(30)
 ERROR#                                             NUMBER
 REQ_START_DATE                                     TIMESTAMP(6) WITH TIME ZONE
 ACTUAL_START_DATE                                  TIMESTAMP(6) WITH TIME ZONE
 RUN_DURATION                                       INTERVAL DAY(3) TO SECOND(0)
 INSTANCE_ID                                        NUMBER
 SESSION_ID                                         VARCHAR2(30)
 SLAVE_PID                                          VARCHAR2(30)
 CPU_USED                                           INTERVAL DAY(3) TO SECOND(2)
 ADDITIONAL_INFO                                    VARCHAR2(4000)

SQL> select actual_start_date
  2       , actual_start_date + run_duration end_date
  3    from dba_scheduler_job_run_details
  4   where log_id = (select max(log_id)
  5                     from dba_scheduler_job_run_details
  6                    where job_name = 'GATHER_STATS_JOB');

ACTUAL_START_DATE
------------------------------------------------------------
END_DATE
------------------------------------------------------------
21-APR-09 10.00.02.244907 PM -04:00
21-APR-09 10.00.36.244907000 PM -04:00

SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name='GATHER_STATS_JOB';

select job_name, job_type, program_name, schedule_name, job_class from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';

SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name='GATHER_STATS_JOB';

JOB_NAME	STATE	
GATHER_STATS_JOB	SCHEDULED	

select job_name, job_type, program_name, schedule_name, job_class from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';

JOB_NAME	JOB_TYPE	PROGRAM_NAME	SCHEDULE_NAME	JOB_CLASS	
GATHER_STATS_JOB	<NULL>	GATHER_STATS_PROG	MAINTENANCE_WINDOW_GROUP	AUTO_TASKS_JOB_CLASS	

select actual_start_date
       , actual_start_date + run_duration end_date
    from dba_scheduler_job_run_details
   where log_id = (select max(log_id)
from dba_scheduler_job_run_details
                    where job_name = 'GATHER_STATS_JOB');


ACTUAL_START_DATE	END_DATE	

--above returned empty fields

select log_date
from dba_scheduler_job_run_details
   where log_id = (select max(log_id)
from dba_scheduler_job_run_details
                    where job_name = 'GATHER_STATS_JOB');

select count(*) from dba_scheduler_job_run_details;

select count(*) from dba_scheduler_job_run_details where log_date is null;

select count(*) from dba_scheduler_job_run_details where log_date is not null;