Ora Indexes
main monitor ddl objects dml stato e configurazione rman & dpump pl/sql cluster ASM
main config start-shut process memory monitor diag
user rman & dpump pl/sql cluster ASM

Matching LOB Indexes and Segments

COL owner         FORMAT A16  HEADING "Owner"
COL TABLE_NAME    FORMAT A24  HEADING "Table|Name"
COL column_name   FORMAT A24 HEADING "Column|Name"
COL segment_name  FORMAT A26 HEADING "Segment Name"
COL segment_type  FORMAT A10 HEADING "Segment|Type"
COL MB                    HEADING "Segment|MB"
 
SELECT   l.owner
,        l.table_name
,        l.column_name
,        s.segment_name
,        s.segment_type
,        s.bytes/1024/1024 MB
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;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License