LOBSEGMENT objects filling up my tablespace in Oracle
I'm using a Oracle 10gR2 DB, and was taking a look in one of my tablespaces when I noticed something odd.
Apart from my table objects, I found a bunch of "LOBSEGMENT" types if quite big sizes - one of then with 17gb.
I'm no expert in Oracle, and would not know in which situations these are generated. There are some tables with LOB objects in this TS, but how could I figure which belongs to what?
This TS is supposed to keep our large tables, so it has configured EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M. That could be another issue, apart from that large object there are dozens of other ones, each of then with at least 64m.
The objects I do not recognise are of two kinds: LOBSEGMENT (named like "SYS_LOB0000187667C00006$$"), and LOBINDEX(named ali "SYS_IL0000187607C00006$$").
One source of this problem I could think of is the use of views. We use a bunch of views joining several massive tables from this TS with no special configuration. Could it be a problem?
thanks!
Answer
The LOBSEGMENT stores the LOB data and the LOBINDEX allows the DB to find and assemble the pieces of the LOB which are at different places in the tablespace.
Check the answer of a similar post to find the table and column that consumes space.
Taken from the other post:
SELECT owner, table_name, column_name
FROM dba_lobs
WHERE segment_name = 'SYS_LOB<<identifier>>$$'