Return
-
Title
Compare error "Error: Oracle Error 936: ORA-00936: missing expression" -
Description
Description 1:
Compare fails as below on a table that has only one column which (in this example) is a LONG datatype:
04/18/06 14:41 Internal error: Error 1 in de_batch_setup, see SID_desvr-889196-*.log [sp_desvr(deq)/889196]
04/18/06 14:41 Error: Oracle Error 936: ORA-00936: missing expression. [sp_desvr(deq)/889196]
04/18/06 14:40 Child process completed
04/18/06 14:40 Notice: Oracle env - SID:/ora/oracle/product/9.2.0 [sp_desvr(pdb)/889196]
04/18/06 14:40 Notice: Opened Compare session log file /data/splex/var_ SID /log/ SID _desvr-889196- [sp_desvr(deq)/889196]
04/18/06 14:40 Process launched: sp_desvr (for SID) [pid = 889196]
04/18/06 14:40 Logging user command: oracle compare table owner.table to owner.table target server_name@o.SID2
Description 2:
Compare *_declt* target log:
-------------------------------------
000: Oracle Error 936: ORA-00936: missing expression
000: Error 936 calling OCIStmtExecute (de_oci_getOneElement,L1143)
000: Error 936 calling SELECT COUNT(*) FROM "OWNER"."TABLE_NAME" WHERE (de_oci_getOneElement,L922)
000: ORA-00001 unique constraint violation occurred
000: Constraint violation avoidance process activated
000: DEBUG: SELECT COUNT(*) FROM DBA_CONS_COLUMNS WHERE CONSTRAINT_NAME='{CONSTRAINT NAME}' (de_table_delete_constraint_row,L4337)
000: Oracle Error 936: ORA-00936: missing expression
000: Error 936 calling OCIStmtExecute (de_oci_getOneElement,L1143)
000: Error 936 calling SELECT COUNT(*) FROM "OWNER"."TABLE_NAME" WHERE (de_oci_getOneElement,L922)
Compare *_desvr* source log:
------------------------------------------
001: Oracle Error 1555: ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6$" too small
0 -
Cause
1. Compare does not support a table with only one column if that column is LONG, LOB or NVARCHAR2 datatype.
2. Insufficient undo_retention time in Oracle causing ORA-01555: snapshot too old on source
-
Resolution
Resolution1:Compare will not work on any table that only has one column and if that column happens to be a LONG , LOB or NVARCHAR2 datatype. The reason being that column cannot be a key column and Shareplex uses a key if one exists and tries to use all columns as keys if none exist. In this case it is forced to use the LONG (or LOB or NVARCHAR2 as applicable) column as key and that fails in compare.Resolution 2:Temporarily increase the Oracle undo_retention time until the compare completesResolution 3:Wrong syntax can also result in ORA-0936. The following has an extra 'where' clause which resulted in this error:compare table SIEBEL.SOURCE_TABLE to SIEBEL.TARGET_TABLE target servername@o.targetSID where \"where LAST_UPD between to_date('01-JAN-2002','dd-mon-yyyy') and to_date('31-DEC-2002','dd-mon-yyyy')\""