Oracle 12c (12.1.0.1) SecureFile LOB segment size growing larger than expected and exhausts tablespa
Hi Oracle Experts,
In Oracle 12c (12.1.0.1 + RHEL6 environment), observe that rotating log table (max 10k rows) with SecureFile LOB column has a LOB segment growing larger than expected: LOB segment size is much greater (+GB larger) than the sum of LOB column length using dbms_lob.getlength.
LOB ddl:
LOB ("LOGOBJ") STORE AS SECUREFILE (
TABLESPACE "LOG" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
.
LOB retention_type is default
LOB retention_type is null
segment_size_blocks:1944600
segment_size_bytes :15930163200
used_blocks :1995