RE: Truncating a table while it is being accessed by a query

If the application had a select cursor open on the table then I would that the select should have taken a lock that prevented the DDL (truncate) from running. At least in my opinion that is how it should work.

However, it the application fetched and stored the rowid then the truncate would have just taken place between selects and that is an application design issue.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Schauss, Peter (ESS) Sent: Wednesday, September 14, 2011 10:11 AM To: oracle-l_at_freelists.org
Subject: Truncating a table while it is being accessed by a query

This is a follow-up to a question that I posted some time ago.

The environment was Oracle 8.1.7.4 running on Solaris.

• We had a data warehouse with an ETL process which ran every hour and took long enough that it ran constantly.
• Some of the ETL steps were truncating tables.
• Reports were failing intermittently with Ora-1410 (invalid rowid).

Through analysis of trace files I was able to demonstrate that the Ora-1410 errors only occurred when one of the tables being accessed by the report query was truncated while the query was running.

Now my question is: Shouldn't Oracle be preventing the truncate operation while another process was querying the table? If not, isn't this a bug?

Thanks,
Peter Schauss

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l