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,
FROM dba_advisor_objects o,
  dba_advisor_findings f,
  dba_advisor_recommendations r
WHERE o.task_id IN
  FROM dba_advisor_tasks
  WHERE task_name=dummy;

------------------------------ ---------- ---------- ----------
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


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:


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

No comments: