Re: Free Space within Table or Index

Neil Boemio wrote:
>
> When creating a new table or index and after loading up the data, I'd
> like to be able to tell if I have sized the object too small or much too
> big. Is there any view which will tell me how much free space exists
> within a table or index?
>
> ___________________________________________
> _/_/_/ Neil Boemio _/_/_/
> _/_/_/ nboemio_at_bway.net _/_/_/
> _/_/_/ http://www.bway.net/~nboemio/ _/_/_/

Hi,

this one will give you the Number of used blocks:

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, EXTENTS, BLOCKS, TABLESPACE_NAME
FROM DBA_SEGMENTS s
WHERE ((S.SEGMENT_TYPE = 'TABLE') and (exists (select 'x' from DBA_Tables t

    where t.table_name = s.segment_name
    and t.owner = s.owner
    and t.owner = 'OWNER' )))
OR

   ((S.SEGMENT_TYPE =' INDEX') and (exists (select 'x' from DBA_indexes i

    where i.index_name = s.segment_name
    and i.owner = s.owner
    and i.owner = 'OWNER' )))
OR

   ((S.SEGMENT_TYPE = 'INDEX') and (exists (select 'x' from DBA_rollback_segs r

    where r.segment_name = s.segment_name     and r.owner = s.owner
    and r.owner = 'OWNER')))
ORDER BY 1, 2

-- 
Regards

M.Gresz    :-)