Very helpful information
May 1, 2008 - 12pm Central time zone
Reviewer: jiang huang zheng from China
That is very useful and Clear my doubts. Transaction can see its change! Thank you!
why this transaction cant read its change?
May 10, 2008 - 2pm Central time zone
Reviewer: jiang huang zheng from China
Hello Tom
from sqlplus:
SQL> insert /*+append */ into qq_i (x) select object_id from user_objects;
156 rows created.
SQL> select * from qq_i;
select * from qq_i
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
I think that transaction always reads its change,but above doesnt, I think it must be related to
the direct insert mode,but I cant figure out the technical reason, why is that?
am I still in a transaction before I commit or rollback?
Followup May 12, 2008 - 1pm Central time zone:
direct path mode loads like that have to be committed prior to reading them, yes, they are "special", they are used for loading - not really for any transactional type applications, they must be used with care (and understanding how they work - they never reuse space for example)
APPEND must be followed by a commit
May 11, 2008 - 12am Central time zone
Reviewer: Hemant K Chitale from Singapore
The APPEND hint is a direct path insert.
It doesn't update existing table blocks but uses new
blocks above the high water mark.
It requires a COMMIT.
Thanks
May 16, 2008 - 5am Central time zone
Reviewer: jiang huang zheng from China
Thank you and it clears my doubts.!
Why does this work then?
May 18, 2008 - 8am Central time zone
Reviewer: Max
insert /*+append */ into qq_i (x) values( 42 );
... without raising ORA-12838?
Followup May 19, 2008 - 4pm Central time zone:
because /*+ APPEND */ only works with insert SELECT, never with values - fortunately!!!!
If you understand what append does, you would know why using it with a single row insert would be "not smart" (it never reuses existing allocate space, you would allocate new space above the high water mark and put one row per block!!!!!!! it would be about as wasteful as you can get)
ops$tkyte%ORA10GR2> drop table t;
Table dropped.
ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> begin
2 for i in 1 .. 1000
3 loop
4 insert /*+ append */ into t select 1 from dual;
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec show_space( 'T' )
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 1,000
Total Blocks............................ 1,024
Total Bytes............................. 8,388,608
Total MBytes............................ 8
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 22,025
Last Used Block......................... 128
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;
Table dropped.
ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> begin
2 for i in 1 .. 1000
3 loop
4 insert /*+ append */ into t values ( 1 );
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec show_space( 'T' )
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 4
Full Blocks ..................... 1
Total Blocks............................ 8
Total Bytes............................. 65,536
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 16,169
Last Used Block......................... 8
PL/SQL procedure successfully completed.
|