Understanding Lob segments (SYS_LOB) in oracle?

Question

Most of the tables (if not all) have a "blob" field in it. One of the table that stores the Logs of the user's action has now grown to 8 GB (about 5 million records).

And our DBA has seen that this schema is now eating space exponentially. We investigated a bit and found out that there is one of the tables with (SYS_LOB) which takes around 116GB of 140GB database.

Our DBA has told us that this table is related to the table that saves the logs of user actions (which is 8GB)

Does anyone know what this SYS_LOB tables does? Is the actual blob saved in the table we created OR oracle actually stores these blob this in a different table (if yes, then SYS_LOB would be that table)?

Answer 1

There is no table SYS_LOB in the Oracle database (at least, there is no such table that is part of a basic database install. There is a table DBA_LOBS that displays information about all the LOB columns in the database, but it doesn't actually store any LOB data, just metadata. The actual LOB segments in the database have system-generated names that take the form SYS_LOB*identifier*$$.

My guess is that your DBA has identified a segment named SYS_LOBidentifier$$ that is consuming 116 GB of space. Assuming that is correct, you can find out what column of what table that LOB column maps to using the DBA_LOBS table, i.e.

SELECT owner, table_name, column_name
  FROM dba_lobs
 WHERE segment_name = 'SYS_LOB<<identifier>>$$'