Database Administration (MOSC)

MOSC Banner

Oracle 12c (12.1.0.1) SecureFile LOB segment size growing larger than expected and exhausts tablespa

edited Mar 26, 2015 10:56AM in Database Administration (MOSC) 3 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center