Menu
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)?
654
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.
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.
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.
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).
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>>$$' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
