Why DDL during SELECT is possible. Or why SELECT does not put a table lock?
This is a question to people who really knows Oracle internals or/and RDB theory.
(The question is relocated from another thread for not hijacking on that's original question.)
Why DDL during SELECT is possible? Or why SELECT does not put TM 2 Row Shared lock on table(s)?
This not locking causes a possibility while one session is running a long select, another can truncate and even drop table(s) that are being read by the first session.
I think, humbly assume, that Oracle does not put TM 2 lock on tables, that are being simply SELECTed, by some significant technical reason. But I do not know this reason. If somebody knows please shed a light.
Also, if you know that this behavior is totally correct from perspective of RDB theory, please explain me.
I'll try to explain my point.
It is a matter of data consistency and integrity that is supported in Oracle everywhere except this place.
a) If one session is reading data from moment T and other session changes data on its way at moment T1, the first session wont read that changed data, it will read the data that was there on moment T.
This is declared as Read Consistency principle.
b) If one session is changing data, and another session tries to truncate or drop table - the second session will fail with error ORA-00054: resource busy and acquire with NOWAIT specified.
This is declared as Data Integrity principle.
c) But why not to follow the above principles in case when one session reads data and another session tries to truncate or drop table (or do other DDL operations)?
It is counter-intuitive. Why not to put TM 2 (SS) lock during SELECT execution that would prevent DDLs on this table?
What I only can assume is that this is only because some technical difficulty or limitation.
What is this limitation or other reason in favor of which Oracle sacrificed consistency in this case?