Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Understanding Lob segments (SYS_LOB) in oracle?

Tags:

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)?

like image 654
MOZILLA Avatar asked Feb 10 '09 08:02

MOZILLA


People also ask

What are LOB segments in Oracle?

LOB Capacity: With Oracle8 and Oracle8i, LOBs can store up to 4GB of data. This doubles the 2GB of data that LONG and LONG RAW data types could store. Number of LOB columns in a table: An Oracle8, Oracle8i, or Oracle9i table can have multiple LOB columns. Each LOB column in the same table can be of a different type.

How do you purge a LOB segment in Oracle?

DROP TABLE lob_tab PURGE; CREATE TABLE lob_tab ( id NUMBER, data CLOB ) LOB(data) STORE AS BASICFILE (DISABLE STORAGE IN ROW); INSERT INTO lob_tab VALUES (1, 'ONE'); COMMIT; We can see both shrink commands complete without errors. Now recreate the table using a securefile LOB column.

Where are LOBs stored in Oracle?

Only the LOB locator is stored in the table column; BLOB and CLOB data can be stored in separate tablespaces and BFILE data is stored as an external file. For inline LOBs, the database stores LOBs that are less than approximately 4000 bytes of data in the table column.

What are LOB indexes?

A LOB is simply a pointer. It points to an index. the index points to the chunks that make up the LOB. Hence when you create a LOB, you will always get a lob index created (to find the chunks for the lob fast) and a segment that holds the lob data (chunks).


1 Answers

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>>$$' 
like image 178
Justin Cave Avatar answered Sep 19 '22 01:09

Justin Cave