Shows automatic tasks and run outcomes. Useful reminder of where stats collection went.
SQL> @autotask
Auto tasks (11G)
Days [1] :
Cur INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO INSTANCE_NUMBER
--- ---------------- -------------------- ----------------- ------------------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- --- ---------------
DEV001 linux_d01 11.2.0.3.0 02/10/2015 19:58:20 OPEN YES 1 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 1
--> DEV002 linux_d02 11.2.0.3.0 02/10/2015 19:41:59 OPEN YES 2 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 2
Details from SYS.DBA_AUTOTASK_TASK
Displays information about current and past automated maintenance tasks.
--------------------------------------------------------------------------------
Client Name : auto optimizer stats collection
Task Name : gather_stats_prog
Task Target Type : System
Task Target Name : system
Operation Name : auto optimizer stats job
Attributes : VOLATILE, SAFE TO KILL
Task Priority : 2
Priority Override : 0
Status : ENABLED
Estimate Type : N/A
Estimated Duration : 0
Retry Count : 0
Last Good Date : 04/10/2015 22:02:18.263907
Last Good Priority : 2
Last Good Duration : 63
Last Good Cpu Time : 17
Last Try Date : 04/10/2015 22:02:18.263907
Last Try Priority : 0
Last Try Result : SUCCEEDED
Last Try Duration : 63
Last Try Cpu Time : 17
Mean Good Duration : 189.273240496800903274369589762890477997
Mean Good Cpu Time : 44.89951072638313887843432442604441098984
Mean Good Temp : 0
Mean Good Dop : 0
Mean Good Io : 0
Mean Good Undo : 0
Mean Good Cpu Wait : 0
Mean Good Io Wait : 0
Mean Good Undo Wait : 0
Mean Good Temp Wait : 0
Mean Good Concurrency : 0
Mean Good Contention : 0
--------------------------------------------------------------------------------
Client Name : auto space advisor
Task Name : auto_space_advisor_prog
Task Target Type : System
Task Target Name : system
Operation Name : auto space advisor job
Attributes : VOLATILE, SAFE TO KILL
Task Priority : 2
Priority Override : 0
Status : ENABLED
Estimate Type : N/A
Estimated Duration : 0
Retry Count : 0
Last Good Date : 04/10/2015 22:01:37.392929
Last Good Priority : 2
Last Good Duration : 16
Last Good Cpu Time : 3
Last Try Date : 04/10/2015 22:01:37.392929
Last Try Priority : 2
Last Try Result : SUCCEEDED
Last Try Duration : 16
Last Try Cpu Time : 3
Mean Good Duration : 145.908509036144578313253012048192771092
Mean Good Cpu Time : 12.53576807228915662650602409638554216864
Mean Good Temp : 0
Mean Good Dop : 0
Mean Good Io : 0
Mean Good Undo : 0
Mean Good Cpu Wait : 0
Mean Good Io Wait : 0
Mean Good Undo Wait : 0
Mean Good Temp Wait : 0
Mean Good Concurrency : 0
Mean Good Contention : 0
--------------------------------------------------------------------------------
Client Name : sql tuning advisor
Task Name : AUTO_SQL_TUNING_PROG
Task Target Type : SQL Workload
Task Target Name : automatic sql workload
Operation Name : automatic sql tuning task
Attributes : ONCE PER WINDOW, VOLATILE, SAFE TO KILL
Task Priority : 2
Priority Override : 0
Status : ENABLED
Estimate Type : N/A
Estimated Duration : 0
Retry Count : 0
Last Good Date : 04/10/2015 06:03:57.272660
Last Good Priority : 2
Last Good Duration : 230
Last Good Cpu Time : 203
Last Try Date : 04/10/2015 06:03:57.272660
Last Try Priority : 0
Last Try Result : SUCCEEDED
Last Try Duration : 230
Last Try Cpu Time : 203
Mean Good Duration : 308.205600000000000000000000000000000012
Mean Good Cpu Time : 186.039999999999999999999999999999999993
Mean Good Temp : 0
Mean Good Dop : 0
Mean Good Io : 0
Mean Good Undo : 0
Mean Good Cpu Wait : 0
Mean Good Io Wait : 0
Mean Good Undo Wait : 0
Mean Good Temp Wait : 0
Mean Good Concurrency : 0
Mean Good Contention : 0
Found 3 record(s)
Details from SYS.DBA_AUTOTASK_CLIENT
Displays statistical data for each automated maintenance task over 7-day and 30-day periods
--------------------------------------------------------------------------------
Client Name : auto optimizer stats collection
Status : ENABLED
Consumer Group : ORA$AUTOTASK_STATS_GROUP
Client Tag : OS
Priority Override : INVALID
Attributes : ON BY DEFAULT, VOLATILE, SAFE TO KILL
Window Group : ORA$AT_WGRP_OS
Resource Percentage : 25
Use Resource Estimates : FALSE
Mean Job Duration : +000000000 00:03:09.273240497
Mean Job Cpu : +000000000 00:00:45.405152428
Mean Job Attempts : 0
Mean Incoming Tasks 7 Days : 2.14285714285714285714285714285714285714
Mean Incoming Tasks 30 Days : 2.24137931034482758620689655172413793103
Total Cpu Last 7 Days : +000000000 00:11:12.010000000
Total Cpu Last 30 Days : +000000000 00:47:52.900000000
Max Duration Last 7 Days : +000 00:11:52
Max Duration Last 30 Days : +000 00:16:35
Window Duration Last 7 Days : +000000002 20:15:04.475300000
Window Duration Last 30 Days : +000000012 00:15:00.032978000
--------------------------------------------------------------------------------
Client Name : auto space advisor
Status : ENABLED
Consumer Group : ORA$AUTOTASK_SPACE_GROUP
Client Tag : SA
Priority Override : INVALID
Attributes : ON BY DEFAULT, VOLATILE, SAFE TO KILL
Window Group : ORA$AT_WGRP_SA
Resource Percentage : 25
Use Resource Estimates : FALSE
Mean Job Duration : +000000000 00:02:31.272111404
Mean Job Cpu : +000000000 00:00:12.981942040
Mean Job Attempts : 0
Mean Incoming Tasks 7 Days : 2.14285714285714285714285714285714285714
Mean Incoming Tasks 30 Days : 2.24137931034482758620689655172413793103
Total Cpu Last 7 Days : +000000000 00:03:04.490000000
Total Cpu Last 30 Days : +000000000 00:14:29.560000000
Max Duration Last 7 Days : +000 00:22:13
Max Duration Last 30 Days : +000 00:25:45
Window Duration Last 7 Days : +000000002 20:15:04.475300000
Window Duration Last 30 Days : +000000012 00:15:00.032978000
--------------------------------------------------------------------------------
Client Name : sql tuning advisor
Status : ENABLED
Consumer Group : ORA$AUTOTASK_SQL_GROUP
Client Tag : SQ
Priority Override : INVALID
Attributes : ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL
Window Group : ORA$AT_WGRP_SQ
Resource Percentage : 25
Use Resource Estimates : FALSE
Mean Job Duration : +000000000 00:05:08.205600000
Mean Job Cpu : +000000000 00:03:06.526680000
Mean Job Attempts : 0
Mean Incoming Tasks 7 Days : 1
Mean Incoming Tasks 30 Days : 1
Total Cpu Last 7 Days : +000000000 00:06:03.620000000
Total Cpu Last 30 Days : +000000000 00:52:07.770000000
Max Duration Last 7 Days : +000 00:04:23
Max Duration Last 30 Days : +000 00:26:50
Window Duration Last 7 Days : +000000002 20:15:04.475300000
Window Duration Last 30 Days : +000000012 00:15:00.032978000
Found 3 record(s)
Details from SYS.DBA_AUTOTASK_CLIENT_JOB
Displays information about currently running Scheduler jobs created for automated maintenance tasks.
It provides information about some objects targeted by those jobs, as well as some additional statistics
from previous instantiations of the same task. Some of this additional data is taken from generic Scheduler views.
No records found
Details from SYS.DBA_AUTOTASK_OPERATION
Displays all automated maintenance task operations for each client.
--------------------------------------------------------------------------------
Client Name : auto optimizer stats collection
Operation Name : auto optimizer stats job
Operation Tag : OPT
Priority Override : INVALID
Attributes : ON BY DEFAULT, VOLATILE, SAFE TO KILL
Use Resource Estimates : FALSE
Status : ENABLED
--------------------------------------------------------------------------------
Client Name : auto space advisor
Operation Name : auto space advisor job
Operation Tag : SPC
Priority Override : INVALID
Attributes : ON BY DEFAULT, VOLATILE, SAFE TO KILL
Use Resource Estimates : FALSE
Status : ENABLED
--------------------------------------------------------------------------------
Client Name : sql tuning advisor
Operation Name : automatic sql tuning task
Operation Tag : SQL
Priority Override : INVALID
Attributes : ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL
Use Resource Estimates : FALSE
Status : ENABLED
Found 3 record(s)
Details from SYS.DBA_AUTOTASK_JOB_HISTORY
Dsplays the history of automated maintenance task job runs. Jobs are added to this view after they finish executing
No records found
DBA_AUTOTASK_CLIENT_HISTORY
Displays per-window history of job execution counts for each automated maintenance task. This information is viewable in the Job History page of Enterprise Manager.
Other views:
DBA_AUTOTASK_WINDOW_HISTORY
Displays historical information for automated maintenance task windows.
select * from DBA_AUTOTASK_WINDOW_HISTORY order by window_start_time
/
DBA_AUTOTASK_SCHEDULE
Displays the schedule of maintenance windows for the next 32 days for each client
select * from DBA_AUTOTASK_SCHEDULE order by start_time
/
DBA_AUTOTASK_WINDOW_CLIENTS
Displays the windows that belong to MAINTENANCE_WINDOW_GROUP, along with the Enabled or Disabled status for the window for each maintenance task. Primarily used by Enterprise Manager.
select * from DBA_AUTOTASK_WINDOW_CLIENTS order by window_next_time
/
prompt
set feed off
prompt Auto tasks (11G)
prompt
accept x_days prompt "Days [1] : " default 1
prompt
select decode( b.instance_number, a.inst_id, '-->', null ) "Current",
a.instance_name, a.host_name, a.version, a.startup_time,
a.status, a.parallel, a.thread#, a.archiver, a.log_switch_wait, a.logins,
a.shutdown_pending, a.database_status, a.instance_role, a.active_state, a.blocked,
a.instance_number
from gv$instance a,
v$instance b
where b.instance_number (+)= a.inst_id
order by a.inst_id;
prompt
prompt
prompt
prompt
declare
cursor csr_main_cursor is select * from DBA_AUTOTASK_TASK order by client_name, task_name;
ln_counter number := 0;
begin
dbms_output.put_line( 'Details from SYS.DBA_AUTOTASK_TASK' );
dbms_output.put_line( 'Displays information about current and past automated maintenance tasks.' );
for i in csr_main_cursor loop
dbms_output.put_line( rpad( '-', 80, '-' ) );
if i.client_name is not null then
dbms_output.put_line( 'Client Name : ' || i.client_name );
end if;
if i.task_name is not null then
dbms_output.put_line( 'Task Name : ' || i.task_name );
end if;
if i.task_target_type is not null then
dbms_output.put_line( 'Task Target Type : ' || i.task_target_type );
end if;
if i.task_target_name is not null then
dbms_output.put_line( 'Task Target Name : ' || i.task_target_name );
end if;
if i.operation_name is not null then
dbms_output.put_line( 'Operation Name : ' || i.operation_name );
end if;
if i.attributes is not null then
dbms_output.put_line( 'Attributes : ' || i.attributes );
end if;
if i.task_priority is not null then
dbms_output.put_line( 'Task Priority : ' || i.task_priority );
end if;
if i.priority_override is not null then
dbms_output.put_line( 'Priority Override : ' || i.priority_override );
end if;
if i.status is not null then
dbms_output.put_line( 'Status : ' || i.status );
end if;
if i.deferred_window_name is not null then
dbms_output.put_line( 'Deferred Window Name : ' || i.deferred_window_name );
end if;
if i.current_job_name is not null then
dbms_output.put_line( 'Current Job Name : ' || i.current_job_name );
end if;
if i.job_scheduler_status is not null then
dbms_output.put_line( 'Job Scheduler Status : ' || i.job_scheduler_status );
end if;
if i.estimate_type is not null then
dbms_output.put_line( 'Estimate Type : ' || i.estimate_type );
end if;
if i.estimated_weight is not null then
dbms_output.put_line( 'Estimated Weight : ' || i.estimated_weight );
end if;
if i.estimated_duration is not null then
dbms_output.put_line( 'Estimated Duration : ' || i.estimated_duration );
end if;
if i.estimated_cpu_time is not null then
dbms_output.put_line( 'Estimated Cpu Time : ' || i.estimated_cpu_time );
end if;
if i.estimated_temp is not null then
dbms_output.put_line( 'Estimated Temp : ' || i.estimated_temp );
end if;
if i.estimated_dop is not null then
dbms_output.put_line( 'Estimated Dop : ' || i.estimated_dop );
end if;
if i.estimated_io_rate is not null then
dbms_output.put_line( 'Estimated Io Rate : ' || i.estimated_io_rate );
end if;
if i.estimated_undo_rate is not null then
dbms_output.put_line( 'Estimated Undo Rate : ' || i.estimated_undo_rate );
end if;
if i.retry_count is not null then
dbms_output.put_line( 'Retry Count : ' || i.retry_count );
end if;
if i.last_good_date is not null then
dbms_output.put_line( 'Last Good Date : ' || i.last_good_date );
end if;
if i.last_good_priority is not null then
dbms_output.put_line( 'Last Good Priority : ' || i.last_good_priority );
end if;
if i.last_good_duration is not null then
dbms_output.put_line( 'Last Good Duration : ' || i.last_good_duration );
end if;
if i.last_good_cpu_time is not null then
dbms_output.put_line( 'Last Good Cpu Time : ' || i.last_good_cpu_time );
end if;
if i.last_good_temp is not null then
dbms_output.put_line( 'Last Good Temp : ' || i.last_good_temp );
end if;
if i.last_good_dop is not null then
dbms_output.put_line( 'Last Good Dop : ' || i.last_good_dop );
end if;
if i.last_good_io_rate is not null then
dbms_output.put_line( 'Last Good Io Rate : ' || i.last_good_io_rate );
end if;
if i.last_good_undo_rate is not null then
dbms_output.put_line( 'Last Good Undo Rate : ' || i.last_good_undo_rate );
end if;
if i.last_good_cpu_wait is not null then
dbms_output.put_line( 'Last Good Cpu Wait : ' || i.last_good_cpu_wait );
end if;
if i.last_good_io_wait is not null then
dbms_output.put_line( 'Last Good Io Wait : ' || i.last_good_io_wait );
end if;
if i.last_good_undo_wait is not null then
dbms_output.put_line( 'Last Good Undo Wait : ' || i.last_good_undo_wait );
end if;
if i.last_good_temp_wait is not null then
dbms_output.put_line( 'Last Good Temp Wait : ' || i.last_good_temp_wait );
end if;
if i.last_good_concurrency is not null then
dbms_output.put_line( 'Last Good Concurrency : ' || i.last_good_concurrency );
end if;
if i.last_good_contention is not null then
dbms_output.put_line( 'Last Good Contention : ' || i.last_good_contention );
end if;
if i.next_try_date is not null then
dbms_output.put_line( 'Next Try Date : ' || i.next_try_date );
end if;
if i.last_try_date is not null then
dbms_output.put_line( 'Last Try Date : ' || i.last_try_date );
end if;
if i.last_try_priority is not null then
dbms_output.put_line( 'Last Try Priority : ' || i.last_try_priority );
end if;
if i.last_try_result is not null then
dbms_output.put_line( 'Last Try Result : ' || i.last_try_result );
end if;
if i.last_try_duration is not null then
dbms_output.put_line( 'Last Try Duration : ' || i.last_try_duration );
end if;
if i.last_try_cpu_time is not null then
dbms_output.put_line( 'Last Try Cpu Time : ' || i.last_try_cpu_time );
end if;
if i.last_try_temp is not null then
dbms_output.put_line( 'Last Try Temp : ' || i.last_try_temp );
end if;
if i.last_try_dop is not null then
dbms_output.put_line( 'Last Try Dop : ' || i.last_try_dop );
end if;
if i.last_try_io_rate is not null then
dbms_output.put_line( 'Last Try Io Rate : ' || i.last_try_io_rate );
end if;
if i.last_try_undo_rate is not null then
dbms_output.put_line( 'Last Try Undo Rate : ' || i.last_try_undo_rate );
end if;
if i.last_try_cpu_wait is not null then
dbms_output.put_line( 'Last Try Cpu Wait : ' || i.last_try_cpu_wait );
end if;
if i.last_try_io_wait is not null then
dbms_output.put_line( 'Last Try Io Wait : ' || i.last_try_io_wait );
end if;
if i.last_try_undo_wait is not null then
dbms_output.put_line( 'Last Try Undo Wait : ' || i.last_try_undo_wait );
end if;
if i.last_try_temp_wait is not null then
dbms_output.put_line( 'Last Try Temp Wait : ' || i.last_try_temp_wait );
end if;
if i.last_try_concurrency is not null then
dbms_output.put_line( 'Last Try Concurrency : ' || i.last_try_concurrency );
end if;
if i.last_try_contention is not null then
dbms_output.put_line( 'Last Try Contention : ' || i.last_try_contention );
end if;
if i.mean_good_duration is not null then
dbms_output.put_line( 'Mean Good Duration : ' || i.mean_good_duration );
end if;
if i.mean_good_cpu_time is not null then
dbms_output.put_line( 'Mean Good Cpu Time : ' || i.mean_good_cpu_time );
end if;
if i.mean_good_temp is not null then
dbms_output.put_line( 'Mean Good Temp : ' || i.mean_good_temp );
end if;
if i.mean_good_dop is not null then
dbms_output.put_line( 'Mean Good Dop : ' || i.mean_good_dop );
end if;
if i.mean_good_io is not null then
dbms_output.put_line( 'Mean Good Io : ' || i.mean_good_io );
end if;
if i.mean_good_undo is not null then
dbms_output.put_line( 'Mean Good Undo : ' || i.mean_good_undo );
end if;
if i.mean_good_cpu_wait is not null then
dbms_output.put_line( 'Mean Good Cpu Wait : ' || i.mean_good_cpu_wait );
end if;
if i.mean_good_io_wait is not null then
dbms_output.put_line( 'Mean Good Io Wait : ' || i.mean_good_io_wait );
end if;
if i.mean_good_undo_wait is not null then
dbms_output.put_line( 'Mean Good Undo Wait : ' || i.mean_good_undo_wait );
end if;
if i.mean_good_temp_wait is not null then
dbms_output.put_line( 'Mean Good Temp Wait : ' || i.mean_good_temp_wait );
end if;
if i.mean_good_concurrency is not null then
dbms_output.put_line( 'Mean Good Concurrency : ' || i.mean_good_concurrency );
end if;
if i.mean_good_contention is not null then
dbms_output.put_line( 'Mean Good Contention : ' || i.mean_good_contention );
end if;
if i.info_field_1 is not null then
dbms_output.put_line( 'Info Field 1 : ' || i.info_field_1 );
end if;
if i.info_field_2 is not null then
dbms_output.put_line( 'Info Field 2 : ' || i.info_field_2 );
end if;
if i.info_field_3 is not null then
dbms_output.put_line( 'Info Field 3 : ' || i.info_field_3 );
end if;
if i.info_field_4 is not null then
dbms_output.put_line( 'Info Field 4 : ' || i.info_field_4 );
end if;
dbms_output.put_line( chr(10) );
ln_counter := ln_counter + 1;
end loop;
if ln_counter = 0 then
dbms_output.put_line( chr(10) || 'No records found' || chr(10) );
else
dbms_output.put_line( 'Found ' || ln_counter || ' record(s)' );
end if;
end;
/
prompt
prompt
prompt
prompt
declare
cursor csr_main_cursor is select * from DBA_AUTOTASK_CLIENT order by client_name;
ln_counter number := 0;
begin
dbms_output.put_line( 'Details from SYS.DBA_AUTOTASK_CLIENT' );
dbms_output.put_line( 'Displays statistical data for each automated maintenance task over 7-day and 30-day periods' );
for i in csr_main_cursor loop
dbms_output.put_line( rpad( '-', 80, '-' ) );
if i.client_name is not null then
dbms_output.put_line( 'Client Name : ' || i.client_name );
end if;
if i.status is not null then
dbms_output.put_line( 'Status : ' || i.status );
end if;
if i.consumer_group is not null then
dbms_output.put_line( 'Consumer Group : ' || i.consumer_group );
end if;
if i.client_tag is not null then
dbms_output.put_line( 'Client Tag : ' || i.client_tag );
end if;
if i.priority_override is not null then
dbms_output.put_line( 'Priority Override : ' || i.priority_override );
end if;
if i.attributes is not null then
dbms_output.put_line( 'Attributes : ' || i.attributes );
end if;
if i.window_group is not null then
dbms_output.put_line( 'Window Group : ' || i.window_group );
end if;
if i.service_name is not null then
dbms_output.put_line( 'Service Name : ' || i.service_name );
end if;
if i.resource_percentage is not null then
dbms_output.put_line( 'Resource Percentage : ' || i.resource_percentage );
end if;
if i.use_resource_estimates is not null then
dbms_output.put_line( 'Use Resource Estimates : ' || i.use_resource_estimates );
end if;
if i.mean_job_duration is not null then
dbms_output.put_line( 'Mean Job Duration : ' || i.mean_job_duration );
end if;
if i.mean_job_cpu is not null then
dbms_output.put_line( 'Mean Job Cpu : ' || i.mean_job_cpu );
end if;
if i.mean_job_attempts is not null then
dbms_output.put_line( 'Mean Job Attempts : ' || i.mean_job_attempts );
end if;
if i.mean_incoming_tasks_7_days is not null then
dbms_output.put_line( 'Mean Incoming Tasks 7 Days : ' || i.mean_incoming_tasks_7_days );
end if;
if i.mean_incoming_tasks_30_days is not null then
dbms_output.put_line( 'Mean Incoming Tasks 30 Days : ' || i.mean_incoming_tasks_30_days );
end if;
if i.total_cpu_last_7_days is not null then
dbms_output.put_line( 'Total Cpu Last 7 Days : ' || i.total_cpu_last_7_days );
end if;
if i.total_cpu_last_30_days is not null then
dbms_output.put_line( 'Total Cpu Last 30 Days : ' || i.total_cpu_last_30_days );
end if;
if i.max_duration_last_7_days is not null then
dbms_output.put_line( 'Max Duration Last 7 Days : ' || i.max_duration_last_7_days );
end if;
if i.max_duration_last_30_days is not null then
dbms_output.put_line( 'Max Duration Last 30 Days : ' || i.max_duration_last_30_days );
end if;
if i.window_duration_last_7_days is not null then
dbms_output.put_line( 'Window Duration Last 7 Days : ' || i.window_duration_last_7_days );
end if;
if i.window_duration_last_30_days is not null then
dbms_output.put_line( 'Window Duration Last 30 Days : ' || i.window_duration_last_30_days );
end if;
dbms_output.put_line( chr(10) );
ln_counter := ln_counter + 1;
end loop;
if ln_counter = 0 then
dbms_output.put_line( chr(10) || 'No records found' || chr(10) );
else
dbms_output.put_line( 'Found ' || ln_counter || ' record(s)' );
end if;
end;
/
prompt
prompt
prompt
prompt
declare
lt_dba_objects dba_objects%rowtype;
cursor csr_main_cursor is select * from DBA_AUTOTASK_CLIENT_JOB order by client_name;
ln_counter number := 0;
begin
dbms_output.put_line( 'Details from SYS.DBA_AUTOTASK_CLIENT_JOB' );
dbms_output.put_line( 'Displays information about currently running Scheduler jobs created for automated maintenance tasks.' );
dbms_output.put_line( 'It provides information about some objects targeted by those jobs, as well as some additional statistics' );
dbms_output.put_line( 'from previous instantiations of the same task. Some of this additional data is taken from generic Scheduler views.' );
for i in csr_main_cursor loop
dbms_output.put_line( rpad( '-', 80, '-' ) );
if i.client_name is not null then
dbms_output.put_line( 'Client Name : ' || i.client_name );
end if;
if i.job_name is not null then
dbms_output.put_line( 'Job Name : ' || i.job_name );
end if;
if i.job_scheduler_status is not null then
dbms_output.put_line( 'Job Scheduler Status : ' || i.job_scheduler_status );
end if;
if i.task_name is not null then
dbms_output.put_line( 'Task Name : ' || i.task_name );
end if;
if i.task_target_type is not null then
dbms_output.put_line( 'Task Target Type : ' || i.task_target_type );
end if;
if i.task_target_name is not null then
dbms_output.put_line( 'Task Target Name : ' || i.task_target_name );
end if;
if i.task_priority is not null then
dbms_output.put_line( 'Task Priority : ' || i.task_priority );
end if;
if i.task_operation is not null then
dbms_output.put_line( 'Task Operation : ' || i.task_operation );
end if;
dbms_output.put_line( chr(10) );
ln_counter := ln_counter + 1;
end loop;
if ln_counter = 0 then
dbms_output.put_line( chr(10) || 'No records found' || chr(10) );
else
dbms_output.put_line( 'Found ' || ln_counter || ' record(s)' );
end if;
end;
/
prompt
prompt
prompt
prompt
declare
lt_dba_objects dba_objects%rowtype;
cursor csr_main_cursor is
select * from DBA_AUTOTASK_OPERATION order by client_name;
ln_counter number := 0;
begin
dbms_output.put_line( 'Details from SYS.DBA_AUTOTASK_OPERATION' );
dbms_output.put_line( 'Displays all automated maintenance task operations for each client.' );
for i in csr_main_cursor loop
dbms_output.put_line( rpad( '-', 80, '-' ) );
if i.client_name is not null then
dbms_output.put_line( 'Client Name : ' || i.client_name );
end if;
if i.operation_name is not null then
dbms_output.put_line( 'Operation Name : ' || i.operation_name );
end if;
if i.operation_tag is not null then
dbms_output.put_line( 'Operation Tag : ' || i.operation_tag );
end if;
if i.priority_override is not null then
dbms_output.put_line( 'Priority Override : ' || i.priority_override );
end if;
if i.attributes is not null then
dbms_output.put_line( 'Attributes : ' || i.attributes );
end if;
if i.use_resource_estimates is not null then
dbms_output.put_line( 'Use Resource Estimates : ' || i.use_resource_estimates );
end if;
if i.status is not null then
dbms_output.put_line( 'Status : ' || i.status );
end if;
dbms_output.put_line( chr(10) );
ln_counter := ln_counter + 1;
end loop;
if ln_counter = 0 then
dbms_output.put_line( 'No records found' );
else
dbms_output.put_line( 'Found ' || ln_counter || ' record(s)' );
end if;
end;
/
prompt
prompt
prompt
prompt
declare
lt_dba_objects dba_objects%rowtype;
cursor csr_main_cursor is
select * from DBA_AUTOTASK_JOB_HISTORY
where window_start_time > sysdate - '&&x_days'
order by window_start_time;
ln_counter number := 0;
begin
dbms_output.put_line( 'Details from SYS.DBA_AUTOTASK_JOB_HISTORY' );
dbms_output.put_line( ' ' );
dbms_output.put_line( 'Dsplays the history of automated maintenance task job runs. Jobs are added to this view after they finish executing' );
dbms_output.put_line( ' ' );
for i in csr_main_cursor loop
dbms_output.put_line( rpad( '-', 80, '-' ) );
if i.client_name is not null then
dbms_output.put_line( 'Client Name : ' || i.client_name );
end if;
if i.window_name is not null then
dbms_output.put_line( 'Window Name : ' || i.window_name );
end if;
if i.window_start_time is not null then
dbms_output.put_line( 'Window Start Time : ' || i.window_start_time );
end if;
if i.window_duration is not null then
dbms_output.put_line( 'Window Duration : ' || i.window_duration );
end if;
if i.job_name is not null then
dbms_output.put_line( 'Job Name : ' || i.job_name );
end if;
if i.job_status is not null then
dbms_output.put_line( 'Job Status : ' || i.job_status );
end if;
if i.job_start_time is not null then
dbms_output.put_line( 'Job Start Time : ' || i.job_start_time );
end if;
if i.job_duration is not null then
dbms_output.put_line( 'Job Duration : ' || i.job_duration );
end if;
if i.job_error is not null then
dbms_output.put_line( 'Job Error : ' || i.job_error );
end if;
if i.job_info is not null then
dbms_output.put_line( 'Job Info : ' || i.job_info );
end if;
dbms_output.put_line( chr(10) );
ln_counter := ln_counter + 1;
end loop;
if ln_counter = 0 then
dbms_output.put_line( 'No records found' );
else
dbms_output.put_line( 'Found ' || ln_counter || ' record(s)' );
end if;
end;
/
prompt
prompt
prompt
prompt
prompt DBA_AUTOTASK_CLIENT_HISTORY
prompt
prompt Displays per-window history of job execution counts for each automated maintenance task. This information is viewable in the Job History page of Enterprise Manager.
prompt
col "Client Name" for a31
col "Window Name" for a16
col "Jobs Created" for 999999
col "Jobs Started" for 999999
col "Jobs Completed" for 999999
col "Window Start Time" for a27
col "Window Duration" for a27
col "Window End Time" for a27
select client_name "Client Name",
window_name "Window Name",
window_start_time "Window Start Time",
window_duration "Window Duration",
jobs_created "Jobs Created",
jobs_started "Jobs Started",
jobs_completed "Jobs Completed",
window_end_time "Window End Time"
from DBA_AUTOTASK_CLIENT_HISTORY
where window_start_time > sysdate - '&&x_days'
order by window_start_time;
prompt
prompt
prompt
prompt
prompt Other views:
prompt
prompt DBA_AUTOTASK_WINDOW_HISTORY
prompt Displays historical information for automated maintenance task windows.
prompt select * from DBA_AUTOTASK_WINDOW_HISTORY order by window_start_time
prompt /
prompt
prompt DBA_AUTOTASK_SCHEDULE
prompt Displays the schedule of maintenance windows for the next 32 days for each client
prompt select * from DBA_AUTOTASK_SCHEDULE order by start_time
prompt /
prompt
prompt DBA_AUTOTASK_WINDOW_CLIENTS
prompt Displays the windows that belong to MAINTENANCE_WINDOW_GROUP, along with the Enabled or Disabled status for the window for each maintenance task. Primarily used by Enterprise Manager.
prompt select * from DBA_AUTOTASK_WINDOW_CLIENTS order by window_next_time
prompt /
prompt
set feed on