Printable View
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."