在oracle的官方文檔上有這麼一段話來解釋pctversion
PCTVERSION integer
Specify the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.
You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode. PCTVERSION is the default in manual undo mode. RETENTION is the default in automatic undo mode.
由於lob segment採用了自己特殊的一致性讀的實現，不是使用undo tablespace來保留前映象，而是當發生update時在lob segment內分配一個chunk去插入一條新的記錄。這樣的話，如果一條記錄修改了多次，那麼它就存在多個版本，對於很大的lob對象來說，這是十分浪費空間的。所以oracle需要有一個辦法來控制這個保留前映象的空間的大小，pctversion就是為了實現這個功能的。pctversion是一個百分比的值，就是當前所有lob空間中用來存放前映象的百分比，如果前映象的空間大於pctversion了，那麼oracle將會重用這些前映象空間而不去擴充。
對比一下不同pctversion對空間佔用的影響
SQL 10G>select TABLE_NAME,SEGMENT_NAME,CHUNK,PCTVERSION,RETENTION from user_lobs where segment_name=’TEXT_LOB’;
TABLE_NAME SEGMENT_NAME CHUNK PCTVERSION RETENTION
—————————— —————————— ———- ———- ———-
TESTLOB TEXT_LOB 8192 1
SQL 10G>truncate table testlob;
Table truncated.
SQL 10G>select segment_name,bytes/1024/1024||’M’ from user_segments where segment_name=’TEXT_LOB’;
SEGMENT_NAME BYTES/1024/1024||’M’
——————————————————————————— —————————————–
TEXT_LOB .0625M
SQL 10G>insert into testlob values(1,rpad(’a',4000));
1 row created.
SQL 10G>commit;
Commit complete.
SQL 10G>select dbms_lob.getlength(text) from testlob;
DBMS_LOB.GETLENGTH(TEXT)
————————
4000
SQL 10G>begin
2 for i in 1..10 loop
3 update testlob set text=text||text;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL 10G>select segment_name,bytes/1024/1024||’M’ from user_segments where segment_name=’TEXT_LOB’;
SEGMENT_NAME BYTES/1024/1024||’M’
——————————————————————————— —————————————–
TEXT_LOB 13M
SQL 10G>truncate table testlob;
Table truncated.
SQL 10G>alter table testlob modify lob(text) (pctversion 99);
Table altered.
SQL 10G>insert into testlob values(1,rpad(’a',4000));
1 row created.
SQL 10G>commit;
Commit complete.
SQL 10G>select segment_name,bytes/1024/1024||’M’ from user_segments where segment_name=’TEXT_LOB’;
SEGMENT_NAME BYTES/1024/1024||’M’
——————————————————————————— —————————————–
TEXT_LOB .0625M
SQL 10G>begin
2 for i in 1..10 loop
3 update testlob set text=text||text;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL 10G>select segment_name,bytes/1024/1024||’M’ from user_segments where segment_name=’TEXT_LOB’;
SEGMENT_NAME BYTES/1024/1024||’M’
——————————————————————————— —————————————–
TEXT_LOB 17M
SQL 10G>select dbms_lob.getlength(text) from testlob;
DBMS_LOB.GETLENGTH(TEXT)
————————
4096000
