can a select block a truncate (ZT)
http://freekdhooge.wordpress.com/2007/12/25/can-a-select-block-a-truncate/
Can a select block a truncate ?
Filed under: Uncategorized — dhoogfr @ 14:41
Recently a discussion was ongoing on the oracle-L list about the question if a select could block a truncate or not.
The first reaction that I had was that, when a select would be active reading from a table, the truncate would fail with the message: “resource busy and acquire with NOWAIT specified”. My reasoning behind this was that a truncate is a ddl statement, which generates no undo for the “deleted” records. So to guarantee that the select would be read consistent, Oracle would have to fail the truncate.
But when discussing this at the office, my colleague Geert claimed that a select placed no protection on the structure of a table and that it would be the select statement that would fail (with the message that the object does no longer exists).
At these moments, there is only 1 thing that you can realy do, and that is to test it. So I did and I found out that is not so strait forward.
Here we go:
In a first session, I create a table with a size that will guarantee me a full table scan time that is big enough to be able to run some statements in a second session.
(The oracle db is running inside a vmware instance on my laptop, so disk access is not so fast as on a real server).
After the table has been created I run a select statement:
After I created the table, I started a second session (as a different user), in which I will check if there are anly locks held by my first session after which I will truncate the table.
Here you can see the query that will be used to check for locks (modified script from Tim Gorman)
I then start the select in the first session and directly thereafter run the locks2 query in the second session, followed by the truncate statement.
You can use the timestamps to see the order of the statements:
Session A:
10:50:46 fdh@LOKI> select count(*) from test;COUNT(*) ----------52000Elapsed: 00:00:16.53 10:51:11 fdh@LOKI>Session B:
10:50:42 sys@LOKI> @locks2no rows selectedElapsed: 00:00:00.03 10:51:00 sys@LOKI> truncate table fdh.test;Table truncated.Elapsed: 00:00:02.53 10:51:09 sys@LOKI>Ok, this shows that both the select as the truncate succeeded and that the truncate finished before the select did.
I then ran the test a second time, now leaving less time between the start of the select and the truncate.
Session A:
10:51:12 fdh@LOKI> insert into test select 'x' from all_objects a, all_objects b where rownum commit;Commit complete.Elapsed: 00:00:00.00 10:53:30 fdh@LOKI> select count(*) from test; select count(*) from test* ERROR at line 1: ORA-08103: object no longer existsElapsed: 00:00:03.46 10:53:43 fdh@LOKI>Session B:
10:53:41 sys@LOKI> truncate table fdh.test;Table truncated.Elapsed: 00:00:01.03 10:53:43 sys@LOKI>As you can see, the select statement this time failed with the message “ORA-08103: object no longer exists”.
When searching on metalink for this I found a note (170185.1), explaining that this error may occur on the next block read after a truncate, because the truncate increases the dataobj# in obj$ by 1.
note: you will only see this when the truncated table contained data
1 point for Geert
However, I wanted to test this “may occur on the next block read” some further, so I set up a test in which I used a ref cursor to open a query on a table, and then truncate this table from a second session.
Session A:
C:\>sqlplus fdh/fdh@loki.ragna.vmSQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 22 17:22:04 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning and Data Mining optionsfdh@LOKI> drop table test;Table dropped.fdh@LOKI> create table test (veld1 char(2000), veld2 char(2000), veld3 char(2000), veld4 char(2000)) tablespace test pctfree 0;Table created.fdh@LOKI> insert into test select 'x', 'x', 'x', 'x' from all_objects where rownum commit;Commit complete.fdh@LOKI> set serveroutput on fdh@LOKI> set arraysize 1 fdh@LOKI> var x refcursor fdh@LOKI> BEGIN23 open :x for select veld1 from test;45 END;6 /PL/SQL procedure successfully completed.Session B:
sys@LOKI> truncate table fdh.test;Table truncated.Session A:
fdh@LOKI> DECLARE23 l_veld1 char(2000);4 l_cnt number(3,0) := 0;56 BEGIN78 BEGIN910 loop1112 fetch :x into l_veld1;13 exit when :x%NOTFOUND;14 l_cnt := l_cnt + 1;1516 end loop;1718 EXCEPTION19 when others then20 dbms_output.put_line(SQLERRM);2122 END;2324 dbms_output.put_line('fetched rows: ' || l_cnt);2526 END;27 / ORA-08103: object no longer exists fetched rows: 0PL/SQL procedure successfully completed.As you can see, running the truncate between the parsing of the query, and the actual fetching results in failure of the fetch.
Time for the second test, in which I will first fetch a single record and then run the truncate.
Session A:
fdh@LOKI> drop table test;Table dropped.fdh@LOKI> fdh@LOKI> create table test (veld1 char(2000)) tablespace test pctfree 0;Table created.fdh@LOKI> fdh@LOKI> insert into test select 'x' from all_objects where rownum fdh@LOKI> commit;Commit complete.fdh@LOKI> set serveroutput on fdh@LOKI> -- set the arraysize to 1 to prevent oracle from "prefetching" records fdh@LOKI> set arraysize 1 fdh@LOKI> var x refcursor fdh@LOKI> fdh@LOKI> BEGIN23 open :x for select veld1 from test;45 END;6 /PL/SQL procedure successfully completed.fdh@LOKI> fdh@LOKI> DECLARE23 l_veld1 char(2000);45 BEGIN67 fetch :x into l_veld1;8 dbms_output.put_line('veld1: ' || trim(l_veld1));910 END;11 / veld1: xPL/SQL procedure successfully completed.Session B:
sys@LOKI> truncate table fdh.test;Table truncated.Session A:
fdh@LOKI> DECLARE23 l_veld1 char(2000);4 l_cnt number(3,0) := 0;56 BEGIN78 BEGIN910 loop1112 fetch :x into l_veld1;13 exit when :x%NOTFOUND;14 l_cnt := l_cnt + 1;1516 end loop;1718 EXCEPTION19 when others then20 dbms_output.put_line(SQLERRM);2122 END;2324 dbms_output.put_line('fetched rows: ' || l_cnt);2526 END;27 / ORA-08103: object no longer exists fetched rows: 315PL/SQL procedure successfully completed.This time I did not get the exception immediate. Instead I was able to fetch 315 records after the table had been truncated, before getting the error.
This number was consisted over several test runs. Also when I changed the record size by adding aditional columns to the table, the number of rows that would be fetched before the error occurred, decreased. When I ran the test with 2 char(2000) columns, I could fetch 152 records before the error would appear and with 4 columns, 78 records.
According to the metalink note, the error would occur on the next block read. With the query I ran, oracle uses a full table scan to get the records. My db_file_multiblock_read_count is 16, so I would expect oracle to perform 1 fetch (arraysize is set to 1) and load 16 blocks into the db cache. The block_size for the holding tablespace is 8KB, so for a table with a single char(2000) column, I would have 4 records per block.
Given this information I expected that the fetch would fail after 63 records (4 * 16 – 1 record from the initial fetch before the truncate). But the test shows a consistent number of 315 records, not 63. A level 8 trace file showed that I was actually using a mbrc of 8, not 16, so I would expect the error to appear even sooner.
After getting these results I suspected oracle of cleaning up some of the db blocks (much like oracle cleans the transaction id from the db blocks after a commit). But a test with dumping db blocks (using alter system dump datafile … block ..) to check the dataobj# showed that only the first block (the header block) was updated with the new dataobj# and that the rest of the blocks would be left unchanged.
At this point I have no real explanation as for why the error occurs at this number of records fetched.
I suspect oracle of storing the locations of the blocks it has to scan during a full table in a kind of array with a limited length. After this array has been processed, Oracle would use the dataobj# value (and other keys) to get the next batch of block locations. At that moment the “object no longer exists” error would be thrown as the referenced dataobj# value no longer exists (it has been increased by 1).
But at this moment this is just pure speculation.
One thing that did became clear to me is that a truncate should not be regarded as just a cheap way to delete all the records from a table. When other sessions (end users) are reading from this table, they risk getting an error… .
Oh yeah: the truncate would get the “resource busy and acquire with NOWAIT specified” if there other transactions (updates, deletes, inserts or ddl statements), which will hold TM or DDL locks) are busy on the table you wanted to truncate.
轉載于:https://www.cnblogs.com/cqubityj/archive/2013/03/19/2969381.html
總結
以上是生活随笔為你收集整理的can a select block a truncate (ZT)的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 首记，一种新的企业信息化平台开发方案——
- 下一篇： 查询所有的存储过程哪些中包含某个字符串