Sunday, February 8, 2009

Bypassing/ignoring or skipping ORA-1555 errors

I was researching a corruption issue using various Metalink link notes and stumbled upon a note/bug which provides a way to ignore or skip ORA-1555 errors for queries. Obviously, this will result in getting inconsistent data/result-set. As of now, I am not sure as to what type situations will have a practical use of this feature. This is available in Oracle 11g 11.1.0.7. This is done using a hint - SCN_ASCENDING.

Example:

In Session 1:

chandra@AEDB01:11g> Select Distinct phone from acs_emp;

PHONE
--------------------
972-500-8999

1 row selected.

In Session 2:

chandra@AEDB01:11g> Select /*+ scn_ascending */ * from acs_emp;

While this session is in progress, I have updated the phone column:

Back in Session 1:

chandra@AEDB01:11g> update acs_emp set phone='888-888-8888';

100000 rows updated.

chandra@AEDB01:11g> commit;

Commit complete.

Back to Session 2 where our select was in progress: You will notice that we got data which was NOT consistent with the data which existed at the point in time when the query was initiated. The SCN got changed in the middle - meaning inconsistent / questionable data!. Without the above mentioned HINT, it wouldn't have been possible to see such inconsistent result set. Hopefully, this hint is a well-intentioned one.

650831 972-500-8999
650831 972-500-8999
650831 972-500-8999
650831 972-500-8999
650831 972-500-8999
650831 972-500-8999
650831 972-500-8999
650831 972-500-8999
650831 972-500-8999
650831 972-500-8999
650831 972-500-8999
652088 888-888-8888
652088 888-888-8888
652088 888-888-8888
652088 888-888-8888
652088 888-888-8888
652088 888-888-8888
652088 888-888-8888
652088 888-888-8888
652088 888-888-8888
652088 888-888-8888

No comments: