How to Find LOB Columns in the LOB_TABLESPACE

book

Article ID: 159423

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

The DLP schema is created within two tablespaces in the Oracle database: USERS and LOB_TABLESPACE.

While it is easy to find all of the tables belonging to the schema in the USERS tablespace, no tables are created in the LOB_TABLESPACE making it difficult to describe how the tablespace is used and why it grows as rapidly as it does.

Resolution

The LOB_TABLESPACE is occupied by objects but they aren't tables; they are columns.  Many people, including DBAs get hung-up by the fact that you can create a table in one tablespace with the caveat that one or more of it's columns are in another tablespace.

To find the lob columns that live in the LOB_TABLESPACE, but are associated with tables in another, run the following script:

select column_name, table_name
from user_lobs
where tablespace_name='LOB_TABLESPACE'
/