Home > oracle > PCTVERSION or RETENTION?

PCTVERSION or RETENTION?

September 19th, 2007
Goto comments Leave a comment

LOB Segment在 AUTO UNDO下有两种方式来处理old version，一种是按照PCTVERSION，保存PCTVERSION%的old version。
还有一种是RETENTION，这个和undo_retention一样，是保存这一时间段内的old version。

在dba_lobs中，很难判断lob segment究竟是采取了哪种方式，PCTVERSION列和RETENTION列都有值，我们需要查询lob$表，才能确认

  1. SQL> CREATE TABLE lobpctversion
  2.   2  (LOBLOC blob,id NUMBER)
  3.   3  LOB ( lobLoc ) STORE AS
  4.   4  (TABLESPACE data01
  5.   5  STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) pctversion 5);
  6.  
  7. Table created.
  8.  
  9. SQL> CREATE TABLE lobretention
  10.   2  (LOBLOC
  11.   3  blob,id NUMBER)
  12.   4  LOB ( lobLoc ) STORE AS
  13.   5  (TABLESPACE data01 STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) retention);
  14.  
  15. Table created.
  16.  
  17. SQL> select table_name,segment_name,pctversion,retention from dba_lobs where table_name in ('LOBPCTVERSION','LOBRETENTION');
  18.  
  19. TABLE_NAME                     SEGMENT_NAME                   PCTVERSION  RETENTION
  20. ------------------------------ ------------------------------ ---------- ----------
  21. LOBPCTVERSION                  SYS_LOB0000018653C00001$$               5      10800
  22. LOBRETENTION                   SYS_LOB0000018656C00001$$              10      10800
  23.  
  24. SQL> select bitand(flags,32) from sys.lob$ where OBJ#= (select OBJECT_ID from dba_objects where
  25.   2  OWNER='XFAN' and OBJECT_NAME='LOBPCTVERSION');
  26.  
  27. BITAND(FLAGS,32)
  28. ----------------
  29.                0              --- 0 代表采用PCTVERSION方式
  30.  
  31. SQL> select bitand(flags,32) from sys.lob$ where OBJ#= (select OBJECT_ID from dba_objects where
  32.   2  OWNER='XFAN' and OBJECT_NAME='LOBRETENTION');
  33.  
  34. BITAND(FLAGS,32)
  35. ----------------
  36.               32             --- 32代表采用RETENTION方式

Eagle Fan oracle