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

  • From: "Powell, Mark" <mark.powell2@xxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 14 Sep 2011 16:48:30 +0100

 
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@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: 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
--
//www.freelists.org/webpage/oracle-l


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


Other related posts: