The difference between USERS and LOB_TABLESPACE tablespaces

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.

 

Additional Information

There are other tablespaces in an Oracle database that are not used directly by DLP:

DRSYS
Contains data for Oracle Text.

SYSTEM
This tablespace is automatically created at database creation. Oracle Database uses it to manage the database, containing objects such as the data dictionary, views, and other administrative information.

SYSAUX
This is an auxiliary tablespace to the SYSTEM tablespace. Every database using Oracle Database 10g release 1 (10.1) or later must have a SYSAUX tablespace.

TEMP
This tablespace stores temporary data generated when processing SQL statements. For example, this tablespace would be used for query sorting.

UNDO
Contains undo tablespaces for each instance that Oracle Database Configuration Assistant creates for automatic undo management

See also: About Tablespaces and Data Files