Understanding Lob segments (SYS_LOB) in oracle?

oracle blob
MOZILLA picture MOZILLA · Feb 10, 2009 · Viewed 64.4k times · Source

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

Justin Cave picture Justin Cave · Feb 10, 2009

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 view 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_LOBidentifier$$.

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 view, i.e.

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

Related questions

How do I get textual contents from BLOB in Oracle SQL

I am trying to see from an SQL console what is inside an Oracle BLOB. I know it contains a somewhat large body of text and I want to just see the text, but the following query only indicates that …

Read More
dbms_lob.getlength() vs. length() to find blob size in oracle

I'm getting the same results from select length(column_name) from table as select dbms_lob.getlength(column_name) from table However, the answers to this question seem to favor using dbms_lob.getlength(). Is there any benefit to using …

Read More
Difference between CLOB and BLOB from DB2 and Oracle Perspective?

I have been pretty much fascinated by these two data types. According to Oracle Docs, they are presented as follows : BLOB : Variable-length binary large object string that can be up to 2GB (2,147,483,647) long. Primarily intended to hold non-traditional data, such …

Read More