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

  • From: "Schauss, Peter (ESS)" <peter.schauss@xxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 14 Sep 2011 18:44:37 +0000

After I posted this I did a quick google search and found something that 
appears to answer my question:

Peter Schauss

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Schauss, Peter (ESS)
Sent: Wednesday, September 14, 2011 10:11 AM
To: oracle-l@xxxxxxxxxxxxx
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 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?

Peter Schauss


Other related posts: