天道酬勤

今天在测试环境：Linux + Oracle 9.2.0.5.0帮开发人员把某个用户的所有数据全部EXP时，遇到下面的错误：

EXP-00056: ORACLE error 1422 encountered

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at “XDB.DBMS_XDBUTIL_INT”, line 52

ORA-06512: at line 1

EXP-00056: ORACLE error 1422 encountered

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at “XDB.DBMS_XDBUTIL_INT”, line 52

ORA-06512: at line 1

EXP-00000: Export terminated unsuccessfully

查了metalink解释如下：

This has been logged as bug 2685696. (viewable in Metalink). The issue is that the package body XDB.DBMS_XDBUTIL_INT is doing a SELECT object_type FROM all_objects and is expecting to return only 1 row. However, if you have a table and trigger of other object, named the same, it returns 2 rows, which causes the error.

Currently a patch for the Windows Platform is not available. The workaround is to rename one of the objects. You can also log an iTAR requesting a one-off patch be created for your platform.

原因是由同名的trigger引起的，解决问题的办法有二个：

1.使用下面的SQL语句找出系统中有问题的trigger，然后将其名称修正即可

SELECT object_type,owner||'.'||object_name "OWNER.OBJECT_NAME"
          FROM dba_objects
         WHERE owner||object_name IN
               (SELECT o.owner||object_name FROM dba_objects o, dba_triggers t
                    WHERE object_name = trigger_name
                    GROUP BY o.owner||object_name HAVING COUNT(*) > 1);

2.将系统升级至9.2.0.8.0即可

-The End-