Re: Table fragmentation

ibm_97_at_yahoo.com wrote:
> In 9.2.0.5, how can I detect if a table has blocks fragmentation or
> not? If it is, is exp/imp the only way to fix it?
>
> Thanks a lot!

You need to define 'blocks fragmentation'. My take on this would mean sparsely populated or non-populated blocks below the high water mark. You would need to use dbms_rowid to find the populated blocks:

select b.blk_nr, b.row_ct
from
(select dbms_rowid.rowid_block_number(rowid) blk_nr, count(*) row_ct from <your table here>
group by dbms_rowid.rowid_block_number(rowid)) b order by b.blk_nr;

This provides a fairly accurate representation of the placement of your data; blocks with low numbers relative to the average would be, I think, what you are looking for with resepct to block fragmentation.

There are ways to 'cure' this: one is using exp/imp to rebuild your table. Another is using 'create table ... as select ...' syntax to save your data into a temporary structure. Once saved you could truncate the source table and insert the saved data into your now 'clean' structure. This will reload your table, starting with the initial extent. Since this is essentially a bulk insert blocks will be populated until filled before new blocks are used, placing an incompletely filled block (should there be one) at the end of the data. This, of course, won't work if you have enabled foreign keys referencing data in the table in need of attention. They can be disabled, once you know what they are:

set termout off verify off feedback off head off pagesize 0 select 'alter table '||c.table_name||' disable constraint '|| c.constraint_name||';'
from user_constraints c, user_cons_Columns t where c.constraint_type = 'R'
and t.constraint_name = c.r_constraint_name and t.table_name = upper('&&1')

spool disable_&&1..sql
/
spool off

select 'alter table '||c.table_name||' enable constraint '|| c.constraint_name||';'
from user_constraints c, user_cons_Columns t where c.constraint_type = 'R'
and t.constraint_name = c.r_constraint_name and t.table_name = upper('&&1')

spool enable_&&1..sql
/
spool off
set termout on feedback on head on verify on pagesize 60

Save this as constraints_off_on.sql (or some other name of your choosing). After running this scrpt you should heve two additional scripts, one to disable the foreign key constraints against the problem table, one to enable them. It should then be a simple matter to:

@constraints_off_on mytable

create table mytable_save as select * from mytable;

@disable_mytable

truncate table mytable;

insert into mytable select * from mytable_save;

@enable_mytable

drop table mytable_save;

Presuming you have no errors from the enable script you should have successfully eliiminated, or at least minimised, your internal block fragmentation. Of course you could use exp/imp, or you could do every related table to your source table with he same approach ('create table ... as select ... ; truncate table ...; ...' until all child tables and the parent table have been truncated, then populate the tables in reverse order, parent first, children last). I've used this before and it appears to work well. Ultimately it's your choice to make. My two cents.

David Fitzjarrell Received on Wed Jan 12 2005 - 18:00:17 CST