It started with a ticket similar to "we get an ORA-08176 during a select, please fix the database."
After some questions it refined to "we do a CREATE TABLE x AS SELECT in one session, and a SELECT * FROM X@db_link throws the ORA-08176.
That's enough for me to do a test-case, but I needed to change the isolation level to produce the error:
| DB1 | DB2 |
| create table obj_foo as select * from dba_objects; | |
| set transaction isolation level SERIALIZABLE ; | |
| select count(*) from obj_foo@DB2; COUNT(*) ---------- 98982 | |
| drop table obj_foo; | |
| create table obj_foo as select * from dba_objects; | |
| select count(*) from obj_foo@DB2; select count(*) from obj_foo@DB2 * ERROR at line 1: ORA-08176: consistent read failure; rollback data not available ORA-02063: preceding line from DB2 |
Just in case someone asks about
set transaction read only;
It also fails, but the error is slightly different in this case:
select count(*) from obj_foo@ROLIT01
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
ORA-02063: preceding line from ROLIT01
