difference between LOB segement size and DBMS_LOB.GETLENGTH
697500Jun 15 2009 — edited Jun 17 2009SQL> select bytes/1024/1024 from dba_segments where segment_name='SYS_LOB0000130003C00019$$';
BYTES/1024/1024
---------------
14772
SQL> select TABLE_NAME,COLUMN_NAME from dba_lobs where SEGMENT_NAME='SYS_LOB0000130003C00019$$';
TABLE_NAME
------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------------------------------------------------------------------------------------
TBL
C1
SQL> select sum(DBMS_LOB.GETLENGTH(C1))/1024/1024 from TBL;
SUM(DBMS_LOB.GETLENGTH(C1)
------------------------------
30.0376911
why is there a discrepancy between the two sizes (14GB and 30MB).
Here are the storage characteristics from the TBL ddl for the C1 LOB column:
TABLESPACE TBLSPC ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))