Printable View
You start a session as schema_owner_A and start a long-running query against Table A.
In the meantime, you start a second session as schema_owner_A and truncate Table A.
What happens to the first session??
I'm having interesting results with this, so am open to discussion.
It will read all the records if the extents are not overwritten by oracle..
Abhay.
You are pretty likely to get an error raised by the selecting query, I'd say. Something funky like ORA-08103: object no longer exists
wouldnt this be candidate if object was dropped?..Quote:
Originally posted by slimdave
Something funky like ORA-08103: object no longer exists
you may get errors like "invalid rowid"
Not everything in life is logical ... http://metalink.oracle.com/metalink/...&p_id=170185.1Quote:
Originally posted by abhaysk
wouldnt this be candidate if object was dropped?..
you may get errors like "invalid rowid"
oh ya i have seen that.. but its history ( 8.x ).. i assumed 9.x..Quote:
Originally posted by slimdave
Not everything in life is logical ... http://metalink.oracle.com/metalink/...&p_id=170185.1
Abhay.
It's in 10g also .. I don't think this is a bug, it's a "feature". There's certainly no guarantee that Oracle can get a consistent view of a table when it is deleted part way through the scan. It's logical that there would be an error of some type. As the note says, if you want the select to work, then use delete .
Its also logical that there can be no error also.. providied all the extents which are marked free after truncate are not over written.. IMHO even after truncate, data is returned with no errors.. and if lets assume is a table scan operation u can see some thing like "Stale Object" getting scanned after truncate (in place of operation table scan)..
Abhay.
Not only do I think you will get a "ORA-08103: object no longer exists" (because slimdave is a pretty smart guy), I know you will (because I tested it).
Since a truncate doesn't create redo, how is Oracle going to maintain a read consistant view?
I agree with Jeff who has already found the answer.
And of course SlimDave ;)
You know what that is? That's Lesson One in the School of Hard Knocks -- "Test Stuff".Quote:
Originally posted by marist89
Not only do I think you will get a "ORA-08103: object no longer exists" (because slimdave is a pretty smart guy), I know you will (because I tested it).
:)
What does redo have to do with a consistent view?Quote:
Originally posted by gandolf989
Since a truncate doesn't create redo, how is Oracle going to maintain a read consistant view?
Sorry, I should have said rollback.
Talking of read/write consistency, here's a kick a$s thread from asktom. Awesome stuff really.
http://asktom.oracle.com/pls/ask/f?p...20191884590175
Quote:
Originally posted by marist89
Not only do I think you will get a "ORA-08103: object no longer exists" (because slimdave is a pretty smart guy), I know you will (because I tested it).
So you think i didnt test what i said??...
if you do, then here is the test for you..
Code:
DBA> set sqlprompt session1>
session1>select OWNER, SEGMENT_NAME, SEGMENT_TYPE, BLOCKS, EXTENTS
2 from dba_segments where segment_name = 'REPORTING_PRODUCT_HIER_TEST';
OWNER SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
--------------- ------------------------------ ------------------ -------------------- --------------------
WWCIW_DBA REPORTING_PRODUCT_HIER_TEST TABLE 64000 10
session1>ed
Wrote file afiedt.buf
1* select /*+ full(x) */ count(*) from reporting_product_hier_test x
session1>/
COUNT(*)
--------------------
1058418
session1>/
COUNT(*)
--------------------
0
==========================================================================
DBA> set sqlprompt session2>
session2>select * from v$session_longops where time_remaining > 0;
no rows selected
session2>/
SID SERIAL# OPNAME TARGET SOFAR TOTALWORK UNITS START_TIM LAST_UPDA TIME_REMAINING ELAPSED_SECONDS
------ --------- -------------- -------------------------------------- -------- ----------- -------- --------- --------- -------------------- --------------------
34 3446 Table Scan WWCIW_DBA.REPORTING_PRODUCT_HIER_TEST 3132 60224 Blocks 04-AUG-04 04-AUG-04 128 7
session2>truncate table reporting_product_hier_test;
Table truncated.
session2>select * from v$session_longops where time_remaining > 0;
SID SERIAL# OPNAME TARGET SOFAR TOTALWORK UNITS START_TIM LAST_UPDA TIME_REMAINING ELAPSED_SECONDS
------ --------- -------------- -------------------------------------- -------- ----------- -------- --------- --------- -------------------- --------------------
34 3446 Table Scan WWCIW_DBA.REPORTING_PRODUCT_HIER_TEST 20704 60224 Blocks 04-AUG-04 04-AUG-04 63 33
session2>/
no rows selected
Had the extents of the truncated table been overwritten then you would have got invalid rowid.. or may be object no longer exist as well.. but to me getting an error invalid rowid seems more logical and i was able to reproduce that as well..
Not only the Count(*) on truncated table returns data but it may be any thing even data from joins with other tables and so .. unless the extents are overwritten..
Abhay.
Quote:
Originally posted by gandolf989
Since a truncate doesn't create redo, how is Oracle going to maintain a read consistant view?
I agree with Jeff who has already found the answer.
And of course SlimDave ;)
Ohh ya you can get read consistent view, not of table but of sys objects which are modified during any DDL and as long as oracle can get that consistent view of the dirty blocks of SEG$, UET$ and so.. you can be sure of getting data from the table .. provided the extents which are marked free after truncate will not be allocated to any other segment.. in other words those extents should not be overwritten..Quote:
Originally posted by gandolf989
Sorry, I should have said rollback.
Abhay.
That's a very specific and technical hypothesis, but as far as I can see, that's all it is -- i can find nothing in the documentation or on metalink to support it. Jeff and I both saw the same phenomenon when testing the scenario.Quote:
Originally posted by abhaysk
Ohh ya you can get read consistent view, not of table but of sys objects which are modified during any DDL and as long as oracle can get that consistent view of the dirty blocks of SEG$, UET$ and so.. you can be sure of getting data from the table .. provided the extents which are marked free after truncate will not be allocated to any other segment.. in other words those extents should not be overwritten..
Abhay.
As the metalink note says, the "object no longer exists" error is raised because the data object id changes. And if your answer to the original question is "it depends on whether the extents are over-written or not", then that's not an answer that a developer can use -- they should expect an error, even if they do sometimes get lucky.
Thanks for the discussion guys. The problem appears intermittent, i.e., sometimes it fails, sometimes it doesn't. Additionally.....
SELECT DATAOBJ# FROM OBJ$
WHERE NAME = 'PHIL';
DATAOBJ#
---------
96907
1 row selected.
TRUNCATE TABLE COGOWN.PHIL;
Table truncated.
SELECT DATAOBJ# FROM OBJ$
WHERE NAME = 'PHIL';
DATAOBJ#
---------
96908
1 row selected.
-----------------------------------------------------------
I read the metalink note but why does the object id change for the truncate command?
Also from the note - "this ORA-08103 may surface on the next block read" - So, the 'may' bit justifies the intermittent nature of the fault, but I'm not sure what the note means here. Anyone help me here please..........
When you run a query on a table that is about to get truncated, whether or not you get data or an error should depend on whether or not your query has already gotten what it needs from a table. i.e. if you run a query that require a sort at the end and the table gets truncated while the query is in the sort phaze, will you still get an error. With all of the examples that people gave, how do we know that the test are comparable. One query might be doing a full table scan while the table is getting truncated another might be going through 50 nested loops and a hash join while the table is being truncated.
IMHO, those two scenarios will yeild different results. Maybe Jeff will run the test and prove me wrong, but the whole conversaiton is silly. If there is a table that people are querying, then it must be needed by someone. Whether by delete or truncate, why are you removing data that is being used? :confused:
Note that it's the data object id that changes, not the object id. I expect there is some reason for it, but it's just something that happens when you truncate (or maybe just DDL) a table.Quote:
Originally posted by Horace
I read the metalink note but why does the object id change for the truncate command?
Also from the note - "this ORA-08103 may surface on the next block read" - So, the 'may' bit justifies the intermittent nature of the fault, but I'm not sure what the note means here. Anyone help me here please..........
The note means "if you have the possibility that your application will truncate a table and try to read it at the same time, then you might get an error. If it is important that you don't get an error then use delete instead of truncate."
Hi Again,
Gandolf - the conversation is not necessarily 'silly'. It was an anomaly I discovered, and here we are discussing why it occurs. I never said it was affecting a production environment.
Here's a very interesting thread from Ask Tom:
http://asktom.oracle.com/pls/ask/f?p...A:839412906735
So, I was surprised to see that a 'truncate' actually drops the object, then recreates it. I was under the impression that 'truncate' just moves the highwater back down to zero??
Yes, surprising too that the dependant objects don't become invalid.
Looks like Oracle would compile such objects ..Quote:
Originally posted by DaPi
Yes, surprising too that the dependant objects don't become invalid.
I don't think that is meant literally (especially where the "reuse storage" clause is invoked)-- Oracle would potentially have to recreate a lot of other stuff as well. I asked a follow on question to get clarification.Quote:
Originally posted by Horace
Here's a very interesting thread from Ask Tom:
http://asktom.oracle.com/pls/ask/f?p...A:839412906735
So, I was surprised to see that a 'truncate' actually drops the object, then recreates it. I was under the impression that 'truncate' just moves the highwater back down to zero??
Thanks Dave,
Followed your follow up!!
Looks like drop and re-creating. See the below test.
My last OBJECT_ID was 6117 and many truncates untill 6123. Then I re-created another table which took object_id as 6124 next to 6123 means OBJECT_ID and DATA_OBJECT_ID is using the same sequence and is not different. Something really new!Code:SQL> select object_id,data_object_id from user_objects where object_name='X';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
6117 6121
SQL> truncate table x;
Table truncated.
SQL> select object_id,data_object_id from user_objects where object_name='X';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
6117 6122
SQL> create table c ( a number);
Table created.
SQL> select object_id,data_object_id from user_objects where object_name='C';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
6123 6123
SQL> truncate table x;
Table truncated.
SQL> select object_id,data_object_id from user_objects where object_name='X';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
6117 6124