Oracle Table fragmentation

declare

space_used number ;
space_allocated number ;
chain_pcent number;

begin
for i in (select table_name, owner from dba_tables
where owner='ANUJ'
and table_name not in (select table_name from sys.dba_external_tables
where owner='ANUJ'))

LOOP
DBMS_SPACE.OBJECT_SPACE_USAGE(i.owner,i.table_name,'TABLE','0',space_used,space_allocated,CHAIN_PCENT,'');
dbms_output.put_line(i.table_name|| ',' || round(space_used/1024)||',' || ROUND(space_allocated/1024)||',' || ROUND((1-(space_used/space_allocated))*100));

end loop;
END;

====

to remove fragmentation in table



SQL> create table ANUJ as select * from dba_tables;

Table created.

SQL>
SQL>
SQL>
SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
from user_tables where table_name='ANUJ'; 2

Ever Used Never Used Total rows
---------- ---------- ----------


SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='ANUJ';

Ever Used Never Used Total rows
---------- ---------- ----------
68 4 2003

SQL> delete from test where owner='SYS';

722 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='ANUJ';

Ever Used Never Used Total rows
---------- ---------- ----------
68 4 1281

SQL> select count(*) from test;

COUNT(*)
----------
1281

SQL> alter table test enable row movement;

Table altered.

SQL> alter table test shrink space compact;

Table altered.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='ANUJ';

Ever Used Never Used Total rows
---------- ---------- ----------
68 4 1281


SQL> alter table ANUJ shrink space
2 /

Table altered.

SQL> analyze table ANUJ compute statistics;

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='ANUJ';

Ever Used Never Used Total rows
---------- ---------- ----------
40 8 1281



script to find fragmentation In table

col wastedspace format a10
col blocksize format a10
col avgsize format a10
select table_name,round((blocks*8),2)||' kb' "blocksize",
round((num_rows*avg_row_len/1024),2)||' kb' "avgsize",
round((blocks*8),2) - round((num_rows*avg_row_len/1024),2) ||' kb' "wastedspace"
from user_tables
-- where table_name = 'ANUJ';