Here is the small script
column job_name format a20
column status format a12
column actual_start_date format a36
column run_duration format a14
select
job_name, status, actual_start_date, run_duration
from
dba_scheduler_job_run_details
where
job_name = 'GATHER_STATS_JOB'
order by
actual_start_date
;
JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION -------------------- ------------ -------------------------------- -------------- GATHER_STATS_JOB SUCCEEDED 21-JAN-10 22.00.03.629836 +00:00 +000 02:26:12 GATHER_STATS_JOB SUCCEEDED 22-JAN-10 22.00.05.236441 +00:00 +000 02:37:48 GATHER_STATS_JOB SUCCEEDED 23-JAN-10 06.00.11.181440 +00:00 +000 01:23:25 GATHER_STATS_JOB SUCCEEDED 25-JAN-10 22.00.02.629410 +00:00 +000 05:06:38 GATHER_STATS_JOB SUCCEEDED 26-JAN-10 22.00.01.182149 +00:00 +000 00:11:09 GATHER_STATS_JOB SUCCEEDED 27-JAN-10 22.00.04.178122 +00:00 +000 00:09:54 GATHER_STATS_JOB SUCCEEDED 28-JAN-10 22.00.01.500210 +00:00 +000 03:15:44
From the dba_opt_stat_operations,
set lines 100 col operation form a40 wrap head 'operation(on)' col target form a1 spool show_auto_stat_runs.lst select operation||decode(target,null,null,'-'||target) operation ,to_char(start_time,'YYMMDD HH24:MI:SS.FF4') start_time ,to_char( end_time,'YYMMDD HH24:MI:SS.FF4') end_time from dba_optstat_operations order by start_time desc / clear colu spool off operation(on) START_TIME END_TIME ----------------------------------- -------------------- -------------------- gather_database_stats(auto) 090518 06:09:43.3429 090520 00:00:52.7399 gather_database_stats(auto) 090517 22:00:01.0653 090518 06:01:02.1637 gather_database_stats(auto) 090516 22:00:00.5150 090517 06:00:39.3076 gather_database_stats(auto) 090515 22:00:00.4157 090516 06:01:10.6544 gather_database_stats(auto) 090514 22:00:01.6624 090515 06:01:10.3767 gather_database_stats(auto) 090513 22:00:01.0706 090514 06:01:07.7145 gather_database_stats(auto) 090511 06:01:44.2381 090513 00:01:23.3091 gather_database_stats(auto) 090510 22:00:00.3218 090511 06:00:53.7735 gather_database_stats(auto) 090509 22:00:02.4334 090510 06:01:11.2443 gather_database_stats(auto) 090508 22:00:03.2080 090509 06:01:22.6858 gather_database_stats(auto) 090507 22:00:01.4859 090508 06:01:19.1966 gather_database_stats(auto) 090506 22:00:02.1927 090507 06:03:55.9747 gather_database_stats(auto) 090504 06:00:47.3064 090506 00:00:29.0017 gather_database_stats(auto) 090503 22:00:01.0015 090504 06:00:45.7018 gather_database_stats(auto) 090502 22:00:00.9199 090503 06:00:47.9868 gather_database_stats(auto) 090501 22:00:02.3382 090502 06:01:24.6070 gather_database_stats(auto) 090530 22:00:01.4914 090501 06:01:31.4369 gather_database_stats(auto) 090529 22:00:01.1076 090530 06:01:14.7856 gather_database_stats(auto) 090527 06:01:01.0286 090529 00:01:16.2678 gather_database_stats(auto) 090526 22:00:00.3628 090527 06:00:52.0203
Thanks for such nice post Geek DBA sir
[…] To accomplish this we will have to use queries written here […]