set pages 300 lines 300 longchunksize 300 long 30000 alter session set nls_date_format='DD/MM/YYYY HH24:Mi'; spool job_info.txt col window_name format a30 col resource_plan format a30 col duration format a30 col client_name format a30 col table_name format a30 col owner format a30 alter session set nls_date_format='DD/MM/YYYY HH24:Mi'; select window_start_time,window_duration,job_start_time,job_status,job_duration,job_info from dba_autotask_job_history where client_name like '%stats%' order by window_start_time; select * from dba_autotask_window_clients; select * from dba_autotask_status; select * from dba_autotask_operation; select WINDOW_NAME,RESOURCE_PLAN,START_DATE,DURATION,ENABLED,ACTIVE from DBA_SCHEDULER_WINDOWS; select * from dba_scheduler_window_groups; --select * from dba_rsrc_plans; --select * from dba_rsrc_plan_directives where plan in ('OPERA_DAILY_OPS','DEFAULT_MAINTENANCE_WINDOW'); --select * from v$rsrc_plan_history; PROMPT "tables with stale stats" select owner,table_name from dba_tab_statistics where stale_stats='TRUE'; --select owner,table_name,last_analyzed from dba_tables where trunc(last_analyzed)=trunc(sysdate) order by last_analyzed; select target,job_name,start_time, end_time - start_time as "Elapsed Time",notes,status from dba_optstat_operation_tasks where status !='COMPLETED' and trunc(start_time) > sysdate -1 order by 3; COLUMN REPORT FORMAT A300 VARIABLE my_report CLOB; BEGIN :my_report := DBMS_STATS.REPORT_STATS_OPERATIONS ( since => SYSDATE-7 , until => SYSDATE , detail_level => 'TYPICAL' , format => 'TEXT' , auto_only => TRUE ); END; / print my_report;