Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Matching LOB Indexes and Segments

leave a comment »

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:]])+'
WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0
THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1)
END) =
WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0
THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1)
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
Owner Name  Name       Segment Name               Type       Bytes

PLSQL ITEM  ITEM_BLOB  SYS_IL0000074435C00007$$   LOBINDEX        65,536
PLSQL ITEM  ITEM_BLOB  SYS_LOB0000074435C00007$$  LOBSEGMENT   2,097,152
PLSQL ITEM  ITEM_DESC  SYS_IL0000074435C00006$$   LOBINDEX        65,536
PLSQL ITEM  ITEM_DESC  SYS_LOB0000074435C00006$$  LOBSEGMENT     720,896

This should help you monitor growth of LOBs in your database.


Written by maclochlainn

April 17, 2008 at 6:16 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: