I am receiving the following error when using ORACLE Tunning advisor (i.e through DBMS_SQLTUNE package):-
I have already given the ADVISOR privilege to ADT1_SCHEMA1.
---------------------------------------------------------------------
ORA-13644: The user "ADT1_SCHEMA1" is invalid.
---------------------------------------------------------------------
- Code: Select all
DECLARE
my_task_name VARCHAR2 (30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT :1, 1, 11, POWER(2, 1), b.is_missing, a.*
FROM SWITCH_COMPACT a,
(SELECT CASE
WHEN (src2.CALL_DT IS NULL OR
src2.CALL_TYPE_ID IS NULL) THEN
1
ELSE
0
END is_missing,
src1.CALL_DT,
src1.CALL_TYPE_ID
FROM (WITH X AS (SELECT CALL_DT,
CALL_TYPE_ID,
SUM(REVENUE_TOTAL) AS REVENUE_TOTAL
FROM SWITCH_SUMMARY
WHERE CALL_DT < Sysdate
AND CALL_DT BETWEEN :1 AND :2
GROUP BY CALL_DT, CALL_TYPE_ID)
SELECT *
FROM X) src1
LEFT OUTER JOIN(WITH Y AS (SELECT CALL_DT,
CALL_TYPE_ID,
SUM(REVENUE_TOTAL) AS REVENUE_TOTAL
FROM CDR_SUMMARY
WHERE CALL_DT < Sysdate
AND CALL_DT BETWEEN :3 AND :4
GROUP BY CALL_DT,
CALL_TYPE_ID)
SELECT *
FROM Y) src2
ON (CASE
WHEN src1.CALL_DT BETWEEN
(src2.CALL_DT - (3 / 86400)) AND
(src2.CALL_DT + (3 / 86400)) THEN
src2.CALL_DT
ELSE
src1.CALL_DT
END) = src2.CALL_DT
AND src1.CALL_TYPE_ID = src2.CALL_TYPE_ID
WHERE ABS(((src1.REVENUE_TOTAL -
NVL(src2.REVENUE_TOTAL, 0)) /
DECODE(src1.REVENUE_TOTAL,
0,
1,
src1.REVENUE_TOTAL)) * 100) > 0
) b
WHERE DISC_DT BETWEEN :7 AND :8
AND b.CALL_DT = TRUNC(a.DISC_DT, ''hh'')
AND b. CALL_TYPE_ID = a.CALL_TYPE';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK
( sql_text => my_sqltext,
bind_list => sql_binds (anydata.convertnumber (9)),
user_name => 'adt_schema1',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'rvm_tuning_task',
description => 'Tuning Task'
);
END;
I also googled a lot but unable to find the solution of above problem.
Please assist.
P.S: I am using the following version of ORACLE.
"Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production"
Thanks in advance,
Regards,
Anand Kumar Ojha