| Reg. Truncate [message #59255] |
Thu, 06 November 2003 07:17  |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hi,
i have a large table (millions of rows).
In one session i started one sql statment which selects all the records.
In another session,after 1 or 2 mins, i truncated the same table.
What happens to the output of the first session's select statement?
Regards,
Giridhar Kodakalla
|
|
|
|
| Re: Reg. Truncate [message #59265 is a reply to message #59255] |
Thu, 06 November 2003 22:51  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
Well this is really an interesting situation and according to me it will show u whole data without any interruption but next time u will not be able to access the table.
The above is on the basis of ORACLE CONSISTENCY definition.
|
|
|
|
| Re: Reg. Truncate [message #59266 is a reply to message #59265] |
Thu, 06 November 2003 23:32 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hi,
Thanks for your reply. For ORACLE READ CONSISTENCY, it reads data from rollback segments to construct the data prior to any DML operations. But the truncate wont write anything into rollback segments. Hence i wonder if i should get the data without any error or whether i should get 1555 error.
Regards,
Giridhar
|
|
|
|
| Re: Reg. Truncate [message #59267 is a reply to message #59265] |
Fri, 07 November 2003 00:04 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
when i tried once i got the output.
when i try again i got the following error:
08103, 00000, "object no longer exists"
// *Cause: the object has been deleted by another user since the operation
// began
Giridhar Kodakalla
|
|
|
|
| Re: Reg. Truncate [message #59270 is a reply to message #59266] |
Fri, 07 November 2003 09:23 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Reposting ...
--------------------
Its true that the Truncate doesnt generate undo entries and hence your query is not really reconstructing the read consistent image from the rollback segments, but from the table itself. Since truncate resets the HWM and doesnt touch the data itself, and since your Query in the 1st session knows the HWM of the table at the START of the query, it reads the data blocks as if nothing has happened . I believe there are some additional internal locks that will ensure that.
So you dont get ORA-1555 error. It should be interesting to test Truncate table drop storage; and try to use that space before the query completes. Either your extent allocation in the 2nd session should wait becos of the internal locks to preserve statement level read consistency, or your query in the 1st session should fail with some other errors.
-Thiru
|
|
|
|
| Re: Reg. Truncate [message #59271 is a reply to message #59267] |
Fri, 07 November 2003 09:24 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
What version of Oracle are you using ? Its not even a DROP Table operation and so wonder why is it complaining about 'object no longer exists' !
Did you check Metalink for any bugs or explanations ?
I just tried with 9.2.0.3 and had no such errors with different isolation levels.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
