Sunday, February 6, 2011

ORA-02063 remote db error and how to fix it

ORA-02063: preceding stringstring from stringstring
Cause: an Oracle error was received from a remote database link.
Action: refer to the preceding error message(s)

ORA-02063 usually occurs when you run a transaction that tries to query or update a remote database table.
This ORA-02063 error is always proceeded  by  other ORA error messages , which are from the remote database and are root cause for the transaction failure.

The format of the message is as follows:
ORA-02063: preceding line from

Example:

ORA-00942: table or view does not exist
ORA-02063: preceding line from TDB21.WORLD@BILLOP

Solution:


Fixing ORA-02063 errors involves  analyzing the remote database errors associated with the ORA-02063  and fixing those errors.

The following  are couple of examples with fixes:

1. In teh following example error occured in Oralce 11.1.0.7
SQL> select *
2 from products_fact@exceldb1;
from products_fact@exceldb1
*
ERROR at line 2:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][ODBC Excel Driver]Optional feature not implemented
ORA-02063: preceding 2 lines from EXCELDB1

SQL>

The above error was fixed by adding parameter  HS_FDS_SUPPORT_STATISTICS=FALSE   in the initilization init.ora file in  11.1.0.7.

2. In the following example, local db is Oracle 9i  and remote db is Oracle 10g.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-02063: preceding line from ABC

In this case TEMP space resource issue was in remote db and  by adding more TEMP or  by tuning the sql statement  this error is resolved.