Hello,
I am using ORACLE DB 11g R2 & RHEL 5.
My bottom line task is that i want to send AWR & ADDM report by mail to myself & other members of my team every hour for a particular user. I have made a procedure for the same. When i fire that procedure from the sys user then it gets successfully executed & even we receive mails with proper attachments. When i try to run that procedure from the particular user it gives me the following error : -
" ORA-13605: The specified task or object ADDM:1987121644_4118 does not exist for the current user. "
When i explored my procedure i found that the error was in the query which is used to generate the ADDM report. The same query inside a procedure run's successfully with sys user but not in other user. The query is as below :-
select dbms_advisor.get_task_report(task_name,'TEXT','ALL') as ADDM_report
from dba_advisor_tasks
where task_id =
(select max(t.task_id)
from dba_advisor_tasks t, dba_advisor_log l
where t.task_id = l.task_id
and t.advisor_name = 'ADDM'
and l.status = 'COMPLETED')
When i accessed the dba_advisor_tasks view separately then i found that every task name the ower was "SYS" user. So its giving me the error when i fire it from other user. When i execute it from SCOTT I get the following error :-
ORA-13605: The specified task or object ADDM:1987121644_4017 does not exist for the current user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 3189
ORA-06512: at "SYS.DBMS_ADVISOR", line 590
ORA-06512: at line 1
ORA-06512: at "SCOTT.SEND_MAIL_FOR_AWR_ADDM_REPORT", line 72
ORA-06512: at line 2
I tried to use the data dictionary view with "USER_" .... like USER_advisor_tasks, but this returns null so it does not produce any ADDM report.
What should i do to execute my procedure from SCOTT user (Other than SYS user) and it should generate ADDM report.
Thanks in advance.