[20180403] No output problem accessing dba_autotask_task. txt

[20180403] No output problem accessing dba_autotask_task. txt

--//Link Http://www.itpub.net/thread-1911421-1-1.html's discussion, did not notice the original post is 2015.
--//if query select Task_name,client_name,status,current_job_name from Dba_autotask_task, no output,
--//must be a base table Sys.ket$_client_tasks no records.
--//through the tests to discover some previously unnoticed problems.

1. Environment:
[Email protected]> @ &r/ver1
Port_string VERSION BANNER
------------------------------ -------------- ------------------------------------------------------------------- -------------
X86_64/linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

[Email protected]> Select COUNT (*) from SYS. Ket$_client_tasks;
COUNT (*)
----------
3

[Email protected]> select task_name,client_name,status,current_job_name from Dba_autotask_task;
Task_name client_name STATUS Current_job_name
----------------------- ------------------------------- -------- -----------------
Auto_sql_tuning_prog SQL TUNING Advisor ENABLED
Auto_space_advisor_prog Auto Space Advisor ENABLED
Gather_stats_prog Auto Optimizer stats collection ENABLED


2. Test delete the base table Ket$_client_tasks content.
--//do a backup first. Do not test this in a production system:
[Email protected]> CREATE TABLE TT as SELECT * from Ket$_client_tasks;
Table created.

[Email protected]> Delete from ket$_client_tasks;
3 rows deleted.

[Email protected]> commit;
Commit complete.

[Email protected]> select task_name,client_name,status,current_job_name from Dba_autotask_task;
No rows selected

--//can find no output now.
[Email protected]> insert INTO SYS. Ket$_client_tasks select * from TT;
3 rows created.

[Email protected]> commit;
Commit complete.

[Email protected]> select task_name,client_name,status,current_job_name from Dba_autotask_task;
Task_name client_name STATUS Current_job_name
----------------------- ------------------------------- -------- ----------------
Auto_sql_tuning_prog SQL TUNING Advisor ENABLED
Auto_space_advisor_prog Auto Space Advisor ENABLED
Gather_stats_prog Auto Optimizer stats collection ENABLED

3. Is it a man-made deletion? In order to repeat the test, I used the original cold backup to overwrite the database.
--//found after restarting the database:
[Email protected]> Select COUNT (*) from SYS. Ket$_client_tasks;
COUNT (*)
----------
0

--//reproduced the author's question. I was trying to find the problem through Logminer because I didn't see the deletion of the sys.ket$_client_tasks by opening the attached log.
--//but after a "long" analysis, I found that setting the parameter job_queue_processes=0, using cold backup to start again, SYS. Ket$_client_tasks information is not deleted.
--//, which is a job or scheduler, deletes the sys.ket$_client_tasks information.

--//I looked at the database, found that job_name= ' Ora$autotask_clean ' is the most likely, its execution script is as follows;

BEGIN
SYS. Dbms_scheduler. Create_job
(
job_name = ' SYS. Ora$autotask_clean '
, Schedule_name = ' SYS. Daily_purge_schedule '
, program_name = ' SYS. Ora$age_autotask_data '
, comments = ' Delete obsolete autotask repository data '
);
SYS. Dbms_scheduler. Set_attribute
(name = = ' SYS. Ora$autotask_clean '
, attribute = ' restartable '
, value = FALSE);
SYS. Dbms_scheduler. Set_attribute
(name = = ' SYS. Ora$autotask_clean '
, attribute = ' Logging_level '
, value = = SYS. Dbms_scheduler. Logging_off);
SYS. Dbms_scheduler. Set_attribute_null
(name = = ' SYS. Ora$autotask_clean '
, attribute = ' max_failures ');
SYS. Dbms_scheduler. Set_attribute_null
(name = = ' SYS. Ora$autotask_clean '
, attribute = ' max_runs ');
SYS. Dbms_scheduler. Set_attribute
(name = = ' SYS. Ora$autotask_clean '
, attribute = ' stop_on_window_close '
, value = FALSE);
SYS. Dbms_scheduler. Set_attribute
(name = = ' SYS. Ora$autotask_clean '
, attribute = ' job_priority '
, value = 3);
SYS. Dbms_scheduler. Set_attribute_null
(name = = ' SYS. Ora$autotask_clean '
, attribute = ' schedule_limit ');
SYS. Dbms_scheduler. Set_attribute
(name = = ' SYS. Ora$autotask_clean '
, attribute = ' Auto_drop '
, value = FALSE);

SYS. Dbms_scheduler. ENABLE
(name = = ' SYS. Ora$autotask_clean ');
END;
/

--//can determine that the calling program is program_name = = ' SYS. Ora$age_autotask_data '. Look at what Sys.ora$age_autotask_data does:

BEGIN
SYS. Dbms_scheduler. Create_program
(
program_name = ' SYS. Ora$age_autotask_data '
, Program_type = ' stored_procedure '
, program_action = ' dbms_autotask_prvt.age '
, number_of_arguments = 0
, Enabled = FALSE
, comments = ' Deletes obsolete autotask repository data '
);

SYS. Dbms_scheduler. ENABLE
(name = = ' SYS. Ora$age_autotask_data ');
END;
/

--//is the manual execution of Dbms_autotask_prvt.age will clear sys.ket$_client_tasks, see if it is correct.

@ &r/10046on 12
exec dbms_autotask_prvt.age;
@ &r/10046off

--//Check the trace file to discover:
=====================
Parsing in CURSOR #140216792384000 len=86 dep=1 uid=0 oct=7 lid=0 tim=1522658288077950 hv=4100613328 ad= ' 7bedf198 ' sqlid= ' DF4JNQ7U6NT6H '
DELETE from Ket$_client_tasks WHERE Curr_win_start < (systimestamp-interval "day)
END of STMT
PARSE #140216792384000: c=25996,e=26168,p=0,cr=206,cu=0,mis=1,r=0,dep=1,og=1,plh=690981981,tim=1522658288077949
EXEC #140216792384000: c=0,e=109,p=0,cr=6,cu=0,mis=0,r=0,dep=1,og=1,plh=690981981,tim=1522658288078164
STAT #140216792384000 id=1 cnt=0 pid=0 pos=1 obj=0 op= ' DELETE ket$_client_tasks (cr=6 pr=0 pw=0 time=55 us) '
STAT #140216792384000 id=2 cnt=0 pid=1 pos=1 obj=6275 op= ' TABLE ACCESS full ket$_client_tasks (cr=6 pr=0 pw=0 time=49 US C ost=3 size=35 card=1) '
CLOSE #140216792384000: c=0,e=2,dep=1,type=3,tim=1522658288078288
=====================

--//can find the start call job_name= ' Ora$autotask_clean '. But delete the conditional where Curr_win_start < (Systimestamp-interval ' Day).
--//This also verifies that I use cold backup to start after why Ket$_client_tasks no information, the information has been deleted.

4. Continue the Analysis:
BEGIN
Dbms_auto_task_admin.disable (
Client_name = ' Auto Space advisor ',
Operation = NULL,
Window_name = NULL);
END;
/

[Email protected]> select task_name,client_name,status,current_job_name from Dba_autotask_task;
No rows selected

--//still no information. Insert base table ket$_client_tasks. I followed dbms_auto_task_admin.disable carefully and found that the base table that was actually modified was:

[Email protected]> Select client_id, operation_id, STATUS, ATTRIBUTES, priority_override from Ket$_client_config;
client_id operation_id STATUS ATTRIBUTES priority_override
---------- ------------ ---------- ---------- -----------------
0 0 2) 0 0
4 0 2) 5 0
5 0 1) 5 0
6 0 2) 5 0
1 0 2) 1 0
2 0 2) 20 0
3 0 2) 34 0
7 rows selected.
--//client_id=5,status=1 represents disable. Then I found that I query the wrong view, which is actually a query:
[Email protected]> column ATTRIBUTES format A56
[Email protected]> Select Client_name, status,attributes,window_group from Dba_autotask_client;
Client_name STATUS ATTRIBUTES Window_group
------------------------------- -------- -------------------------------------------------------- ---------------
Auto Optimizer stats collection ENABLED on by DEFAULT, VOLATILE, SAFE to KILL Ora$at_wgrp_os
Auto Space advisor DISABLED on by DEFAULT, VOLATILE, SAFE to KILL Ora$at_wgrp_sa
SQL Tuning Advisor ENABLED ONCE PER WINDOW, on by DEFAULT, VOLATILE, SAFE to KILL ora$at_wgrp_sq

--//oracle is very easy to confuse on the 2 view dba_autotask_task,dba_autotask_client.
--//Finally, post an explanation of these 2 tables on the Oracle website:

Dba_autotask_task:displays information about current and past automated maintenance tasks.
Dba_autotask_client:displays statistical data for each automated maintenance task over 7-day and 30-day periods.

--//As for Dba_autotask_task and so on 10 o'clock Scheduler run will be recorded.
--//the next day:

[Email protected]> Select COUNT (*) from SYS. Ket$_client_tasks;
COUNT (*)
----------
3

[Email protected]> select task_name,client_name,status,current_job_name,last_good_date from Dba_autotask_task;
Task_name client_name STATUS current_job_name last_good_date
----------------------- ------------------------------- -------- ---------------- ---------------------------------
Auto_sql_tuning_prog SQL TUNING Advisor ENABLED 2018-04-02 22:00:05.625135 +08:00
Auto_space_advisor_prog Auto Space advisor ENABLED 2018-04-02 22:00:06.527905 +08:00
Gather_stats_prog Auto Optimizer stats collection ENABLED 2018-04-02 22:00:17.128253 +08:00

--//in short, these 2 views dba_autotask_task,dba_autotask_client very easy to confuse.

[20180403] No output problem accessing dba_autotask_task. txt