Matching LOB Indexes and Segments
I enjoyed Tom Kyte’s example of how you find matching a LOB Index to Segment (Expert Oracle Database Architecture, pp. 542). I’ve noticed variations of it posted in various locations. While it works well for sample scheme that have only one LOB, the following works for any number of LOBs in any schema. This simplifies working with system- and user-defined segment names. The first CASE statement ensures that joins between user-named segment names are possible. The second CASE statement ensures two things: (a) Joins between system generated segment names don’t throw an error when matching unrelated system generated return values found in the DBA_SEGEMENTS view; and (b) Joins between named segments are possible and don’t throw an error.
COL owner FORMAT A5 HEADING "Owner" COL TABLE_NAME FORMAT A5 HEADING "Table|Name" COL column_name FORMAT A10 HEADING "Column|Name" COL segment_name FORMAT A26 HEADING "Segment Name" COL segment_type FORMAT A10 HEADING "Segment|Type" COL bytes HEADING "Segment|Bytes" SELECT l.owner , l.TABLE_NAME , l.column_name , s.segment_name , s.segment_type , s.bytes FROM dba_lobs l , dba_segments s WHERE REGEXP_SUBSTR(l.segment_name,'([[:alnum:]]|[[:punct:]])+' , CASE WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0 THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) ELSE 1 END) = REGEXP_SUBSTR(s.segment_name,'([[:alnum:]]|[[:punct:]])+' , CASE WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0 THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) ELSE 1 END) AND l.TABLE_NAME = UPPER('&table_name') AND l.owner = UPPER('&owner') ORDER BY l.column_name, s.segment_name; |
It produces the following type of output:
Table Column Segment Segment Name Name Segment Name Type Bytes ------ ---------- ------------------------- ---------- --------- PLSQL ITEM_BLOB SYS_IL0000074435C00007$$ LOBINDEX 65,536 PLSQL ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT 2,097,152 PLSQL ITEM_DESC SYS_IL0000074435C00006$$ LOBINDEX 65,536 PLSQL ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 720,896 |
This should help you monitor growth of LOBs in your database. The maximum CHUNK size is 32.768. If you elect a value greater than that you raise an ORA-22851 error.
Subscribe via RSS
Hi Michael!
I really appreciate your effort to share your experience and kwnoledge with other Oracle enthusiats. With this and others articles incl. “Cleaning up wasted LOB space” you helped me a lot.
Thanks and keep up the good work!
Best regards
Dejan
Dejan Topalović
9 Dec 08 at 4:02 pm
Thanks for tips on LOBs. Here is another approach to getting the size of the lob and lobindex. I think the dba_lobs table is new on Oracle 10.
Mike Madland
17 Mar 09 at 4:08 pm
This is quite up-to-date information. I think I’ll share it on Delicious.
Liza
24 Apr 09 at 5:06 am
[...] should first check space, by using the query provided earlier in my blog to compare LOB indexes and segments. [...]
How to cleanup wasted LOB space
13 Nov 10 at 8:19 am
Unfortunately the second query not only is a Cartesian join but it’s incorrect too.
Alex
13 Jul 11 at 8:14 pm
Alex,
I’m not sure why you think it’s a Cartesian join or incorrect, but it’s neither. Here’s the output from DBMS_XPLAN on it:
maclochlainn
13 Jul 11 at 10:24 pm
I found that the best way to reclaim all the space of a LOB (and to rebuild its LOBINDEX) is to move it to a new tablespace:
ALTER TABLE o.t move LOB (blobcol) STORE AS (TABLESPACE ts);
Very simple, fast and efficient
Eric Bergeron
28 Feb 12 at 12:58 pm