The difference between USERS and LOB_TABLESPACE tablespaces

book

Article ID: 159996

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

What are the differences between the USERS and LOB_TABLESPACES, and what do they contain?

Resolution

There are two Oracle tablespaces used to contain the Data Loss Prevention (DLP) schema in the database: USERS and LOB_TABLESPACE.  Normally, a tablespace is a logical object where all of the bits and pieces of a data schema live: e.g., tables, indexes, views, stored procedures and more.

DLP uses its two tablespaces to house the DLP schema in the following manner: Almost EVERYTHING is stored in the USERS tablespace except specific columns that are of the LOB (Large Objects) type. Large objects come in two types BLOB (Binary Large Object) or CLOB (Character Large Object) datatypes.  Due to the role that the LOB columns play in storing “oversized” data, LOB_TABLESPACE is typically going to be the tablespace that grows the fastest.  Because USERS has everything else but doesn’t store the unknowably large information, it will typically grow the second fastest. 

Here’s the sticking point between the two:  The way LOBs have been set up to store, if the record is stored in the LOB column is <=4k, then that data is stored “in-line”, which is to say within the table column as a “normal” column record.  If the record is >4k, it is stored “out of line” in what is called a “lob segment”.   It’s the lob segments that consume big gulps of space in the LOB_TABLESPACE.

 A customer who has millions of incidents that are all small, like IM violations, in this rare situation USERS tablespace may grow faster than LOB_TABLESPACE. 

USERS is most certainly expected to grow, just not as fast as LOB_TABLESPACE.