DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Sunday, October 25, 2015

ORA-01422: exact fetch returns more than requested number of rows" When Running DBMS_SQLTUNE.ACCEPT_SQL_PROFILE


When trying to accept a sql profile, it fails with "ORA-01422: exact fetch returns more than requested number of rows":
execute DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => '<TASK_NAME>',task_owner => '<TASK_OWNER>', replace => TRUE);
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16446
ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 31
ORA-06512: at "SYS.DBMS_SQLTUNE", line 7544
ORA-06512: at "SYS.DBMS_SQLTUNE", line 7568
ORA-06512: at line 1


This problem occurs because there are two or more distinct executions recorded for the same task_id. You can see this with the following select:

SELECT f.execution_name,
  f.finding_id,
  rec_id,
  r.flags
FROM dba_advisor_objects o,
  dba_advisor_findings f,
  dba_advisor_recommendations r
WHERE o.task_id IN
  (SELECT DISTINCT(task_id)
  FROM dba_advisor_tasks
  WHERE task_name=dummy;

EXECUTION_NAME                 FINDING_ID     REC_ID      FLAGS
------------------------------ ---------- ---------- ----------
EXEC_9047                               1          1          1
EXEC_9047                               2          2          4
EXEC_9048                               3          3          1
EXEC_9048                               4          4          4
EXEC_9087                               5          5          1
EXEC_9087                               6          6          4

This root cause of this issue is still being investigated via Bug 16758043 ORA-01422 WHEN EXECUTING DBMS_SQLTUNE.ACCEPT_SQL_PROFILE

Solution:

There is currently no fix for this issue. As a workaround, dropping the tuning task will clear the problem. To drop the tuning task execute:

exec DBMS_SQLTUNE.DROP_TUNING_TASK('<TASK_NAME>');

You will need to recreate and rerun the tuning task afterwards.

No comments: