今天做测试的时候,对一个表收集统计信息,遇到如下错误:
SQL> exec dbms_stats.gather_table_stats(user,'TEST');
BEGIN dbms_stats.gather_table_stats(user,'TEST'); END;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.STANDARD", line 264
ORA-06512: at line 1
SQL> exec dbms_stats.gather_table_stats('SCOTT','TEST');
BEGIN dbms_stats.gather_table_stats('SCOTT','TEST'); END;
*
ERROR at line 1:
ORA-20000: Cannot parse for clause: FOR ALL COLUMNS SIZE 1
ORA-06512: at "SYS.DBMS_STATS", line 9375
ORA-06512: at "SYS.DBMS_STATS", line 9389
ORA-06512: at line 1
开始以为是包有问题,重新跑了一遍catproc.sql,还是不行。到网上查了一下,发现一篇yangtingkun的文章,有如下描述:
由于调用者权限的存储过程在执行时才去真正的访问表,如果用户添加了新增对象,而这个新增对象又和存储过程中需要访问的某个对象(一般是PUBLIC同义词)重名,那么对于调用者权限存储过程,由于不是存储过程的拥有者对象发生了改变,因此不会影响到存储过程的状态,但是在新建对象用户下执行存储过程时就可能发生错误。
接着他举了个例子,创建了一个名为dual的同义词,这样dbms_stats.gather_table_stats在执行时,引用了错误的dual表,导致错误的产生。
看到这里突然想起前几天做测试时,对dual表插入了几条记录,极有可能就是这个原因导致错误的发生。于是重建了dual表,之后再次执行dbms_stat.gather_table_stats过程,可以正常执行了。
可以进行如下测试模拟这个过程:
SQL> insert into dual values('x');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from dual;
COUNT(*)
----------
2
SQL> exec dbms_stats.gather_table_stats(user,'TEST');
BEGIN dbms_stats.gather_table_stats(user,'TEST'); END;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.STANDARD", line 264
ORA-06512: at line 1
SQL> exec dbms_stats.gather_table_stats('SCOTT','TEST');
BEGIN dbms_stats.gather_table_stats('SCOTT','TEST'); END;
*
ERROR at line 1:
ORA-20000: Cannot parse for clause: FOR ALL COLUMNS SIZE 1
ORA-06512: at "SYS.DBMS_STATS", line 9375
ORA-06512: at "SYS.DBMS_STATS", line 9389
ORA-06512: at line 1
SQL> delete from dual where dummy='x';
1 row deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'TEST');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SCOTT','TEST');
PL/SQL procedure successfully completed.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-440545/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-440545/