RE: Truncating a table while it is being accessed by a query
Date: Wed, 14 Sep 2011 18:44:37 +0000
Message-ID: <8AE45871F749FC4CBBE053CF2F8A493C0A76106E_at_XMBVAG74.northgrum.com>
After I posted this I did a quick google search and found something that appears to answer my question:
http://freekdhooge.wordpress.com/2007/12/25/can-a-select-block-a-truncate/
Peter Schauss
-----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: EXT :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-lReceived on Wed Sep 14 2011 - 13:44:37 CDT