Re: alter table pctfree question ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 12 Jan 2002 10:31:19 -0000
Message-ID: <1010831368.16185.0.nnrp-07.9e984b29@news.demon.co.uk>

If x and y are not too large, you could
dump all the blocks to a trace file
then grep for "tosp" which would give you one line for the 'total space free' from each block.

Something like:

for r1 in (

        select file_id, block_id, blocks from dba_extents
        where segment_name = '{your table}'
        order by extent_id

) loop

    execute immediate

        'alter system dump datafile ' ||
        r1.file_id || ' block min ' ||
        r1.block_id || ' block max ' ||
        (r1.block_id + r1.blocks -1 )'

    ;
end loop;

should do it.

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

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

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

Author of:
Practical Oracle 8i: Building Efficient Databases


Ted Chyn wrote in message
<44a19320.0201111738.336aa6af_at_posting.google.com>...


>all,

>

>Table A with pctfree 10% with x number of blocks. pctfree was

>altered to 30%. now table contain y number of blocks.

>

>Is there a way finding out there are x number of blocks with pctfree of 10

and


>y-x number of blocks with pctfree of 30 ?

>

>thnx

>ted chyn

Received on Sat Jan 12 2002 - 04:31:19 CST