-
Title
Compare fails with "Error ORA-1654 calling OCIStmtExecute" -
Description
While running compare with repair option, you monitor it with "show compare" on source and find that the compare process eventually errored out. On probing further, you find that the source compare log (*devsr* log) does not contain any clue but the target compare log (*declt* log) has the following entries that show some space issues with an INDEX tablespace:
000: COMPARE CURRENT STATUS:Wed Sep 3 19:00:02 2008 (elapsed 1.528705 seconds), got first batch (de_batch_setup,L1109:de_log
_line,L324)
000: SP_DEQ_PART_UPDATE : 0
000: Notice: Object ID of <owner>.<tablename> is 4764993
000: Oracle Error 1654: ORA-01654: unable to extend index <owner>.<indexname>by 128 in tablespace <tablespacename>
000: Error ORA-1654 calling OCIStmtExecute (de_repair_insert,L3428)
000: No further repairs will be applied
000: Error 1 calling de_repair_insert in de_compare_client (1426)
000: Continuing to process so all the out-of-sync rows are reported
000: Error -1 calling de_compare_client (main,L2552)
The Oracle alert log can be examined to further corroborate this fact. -
Cause
There is a space issue with an INDEX tablespace.
-
Resolution
The compare process with repair option may need to do DML on target table to bring it in sync with the source table. If there is a space issue with the affected tablespaces (in our case an INDEX tablespace), then the compare will error out. The resolution to this issue is to add more space to the affected tablespace and then re-run compare as the following explanation for the Oracle error shows:
oerr ora 1654
01654, 00000, "unable to extend index %s.%s by %s in tablespace %s"
// *Cause: Failed to allocate an extent for index segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.