Montag, 30. Mai 2016

ORA-08176 with DB-link, create table and isolation level SERIALIZABLE

Today I had some fun identifying how a ORA-08176 can happen.
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:

create table obj_foo as select * from dba_objects;
set transaction isolation level SERIALIZABLE ;
select count(*) from obj_foo@DB2;

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