Re: Slow Query DBA_SEGMENTS

Look at the definitions from the dba_lobs and dba_segments views and you should be able to extract the bits that are relevant to your requirement.

There is no harm in defining your own views on the SYS objects, provided you remember only to grant SELECT on them, and remember to review them every time you upgrade the database.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

JK wrote in message ...


>Hi,

>

>I need to get storage for lob columns and the only way I can think of doing

>this is by joining dba_lobs with dba_segments.

>I am doing this to generate CREATE statements from the Data Dic and also to

>calculate storage requirements.

>The query needs to run on both 8i and 9i.

>I need to do this a lot as I have many lob columns on my DB.

>Is there somthing I can do to improve the performance of this query.  I am

>thinking if  I can somehow avoid DBA_SEGMENTS and hit the SYS tables

>directly.  OR is there some other way altogether. Maybe some sort of hiny

>

>Any ideas would be apreciated.

>

>The query looks something like this.

>

>SELECT  b.BLOCKS BLOCKS

>, a.TABLE_NAME TABLE_NAME

>, a.COLUMN_NAME COLUMN_NAME

>, a.OWNER OWNER

>, b.BYTES BYTES

>, b.EXTENTS EXTENTS

>, b.RELATIVE_FNO RELATIVE_FNO

>, a.SEGMENT_NAME SEGMENT_NAME

>, a.INDEX_NAME INDEX_NAME

>, a.CHUNK CHUNK

>, a.PCTVERSION PCTVERSION

>, a.IN_ROW IN_ROW

>, b.OWNER SEGMENT_OWNER

>, b.PARTITION_NAME PARTITION_NAME

>, b.SEGMENT_TYPE SEGMENT_TYPE

>, b.TABLESPACE_NAME TABLESPACE_NAME

>, b.HEADER_FILE HEADER_FILE

>, b.HEADER_BLOCK HEADER_BLOCK

>, b.FREELIST_GROUPS FREELIST_GROUPS

>, b.BUFFER_POOL BUFFER_POOL

>, a.LOGGING LOGGING

>, b.INITIAL_EXTENT INITIAL_EXTENT

>, b.NEXT_EXTENT NEXT_EXTENT

>, b.MIN_EXTENTS MIN_EXTENTS

>, b.MAX_EXTENTS MAX_EXTENTS

>, b.PCT_INCREASE PCT_INCREASE

>, b.FREELISTS FREELISTS

>, a.CACHE CACHE from dba_lobs a, dba_segments b

>WHERE A.SEGMENT_NAME = B.SEGMENT_NAME (+) AND a.OWNER =  ?  and

a.TABLE_NAME


>=  ?  and a.COLUMN_NAME =  ?  order by OWNER, TABLE_NAME, COLUMN_NAME

>

>

>

>