"Ben" <balvey@comcast.net> wrote in message
news:1144078276.420335.13990@i39g2000cwa.googlegro ups.com...
> Jonathan,
> I've been running your see_space.sql script and before I run it on my
> prod schema, I was wondering how big of an impact it has on the system.
> Should I wait until off hours to run it?
> Thanks
> Ben
>

I've just run a quick test on 9.2.
The procedure seems to walk the freelist
one block at a time. So if you think you
have lots of objects with very large freelists,
then you might want to wait for a quiet time
when the I/O load is low.

Alternative strategy:
select segment_name, owner, header_file, header_block
from dba_segments;

For each interesting segment

alter system dump datafile {header_file} {header_block};

then look at the trace file, which will have a section like:

Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 511
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x0240041a ext#: 3 blk#: 17 ext size: 128
#blocks in seg. hdr's freelists: 400
#blocks below: 400
mapblk 0x00000000 offset: 3
Unlocked
Map Header:: next 0x00000000 #extents: 4 obj#: 42458 flag: 0x40000000
Extent Map
-----------------------------------------------------------------

The line you want is:
#blocks in seg. hdr's freelists: 400

The presence of this line is what made me think
that the dbms_space call no longer walked the
list.



--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html