Re: Export Error, Exp-0056 , Ora-1422, Ora-6512

On Mar 30, 5:54 pm, "kishanthak..._at_gmail.com" <kishanthak..._at_gmail.com> wrote:
> Dear friends,
>
> I will be highly obliged if any one can help me on this error , which
> I M getting while trying to backup user wise data from oracle9i on
> Redhat Linux ES rel. 4.
>
> While taking EXPORT backup on Oracle9i Enterprise Edition Release
> 9.2.0.4.0
> I M getting following error message
>
> . exporting triggers
> 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 55
> 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 55
> ORA-06512: at line 1
> EXP-00000: Export terminated unsuccessfully
>
> I M taking backup with following command.
>
> exp system/<password> file=<filename> log=<logfilename>
> owner=<username>
>
> we have around 15 oracle users. All other export is terminated without
> error/warnings. Except only one user.
>
> Once again I request to help me.
>
> Thanx in Advance
>
> Kishan Thakker

This is a known issue in 9.2.0.4, fixed in 9.2.0.5. Apply the latest available patchset (9.2.0.8) to fix it. If you can't patch, there's a workaround: make sure the schema being exported doesn't have triggers with the same name as other objects. You can use the following query to identify such triggers and rename them:

select object_type

      ,owner||'.'||object_name
  from dba_objects
 where (owner,object_name) in

    (select o.owner, o.object_name from dba_objects o, dba_triggers t

      where o.object_name = t.trigger_name
      group by o.owner,o.object_name having count(*) > 1);

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Sat Mar 31 2007 - 02:49:55 CDT