finding fragmentation at table level and tablespace level in Oracle and steps to remove them

Use this query :-


select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
    round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
    round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE"
    from all_tables  WHERE Owner NOT IN (
       'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
       'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
       'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
       'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
       'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
       'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
       'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

Or This one :- It will collect the data which are having more than 100MB fragmentation.


select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE", round((num_rows*avg_row_len
/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE" from
dba_tables where owner in('a','b','c','d') and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 100 order by 8 desc;


========================================================================

Description

This script lists details of the extents within a tablespace. This will help you determine if a tablespace is fragmented or not. A tablespace with little or no fragmentation the biggest, smallest and average will be similar (e.g. as in a LOCALLY MANAGED tablespace of UNIFORM SIZE)

Parameters

None.

SQL Source


set pages 50

PROMPT
PROMPT Tablespace Freespace Fragmentation Report
PROMPT

column "Blocks" format 999999
column "Free" format 999999
column "Pieces" format 99999
column "Biggest" format 999999
column "Smallest" format 999999
column "Average" format 999999
column "Dead" format 9999
select substr(ts.tablespace_name,1,12) "Tspace",
       tf.blocks "Blocks",
       sum(f.blocks) "Free",
       count(*) "Pieces",
       max(f.blocks) "Biggest",
       min(f.blocks) "Smallest",
       round(avg(f.blocks)) "Average",
       sum(decode(sign(f.blocks-5),-1,f.blocks,0)) "Dead"
from   dba_free_space f,
       dba_data_files tf,
       dba_tablespaces ts
where  ts.tablespace_name=f.tablespace_name
and    ts.tablespace_name = tf.tablespace_name
group by ts.tablespace_name,tf.blocks
/

exit


========================================================================
Description

This script lists details how chained or migrated rows there are within a table. It may help you determine if a table needs to be rebuilt. In order for this script to be effective, you must analyze your tables regularly.

Parameters

None.

SQL Source

CLEAR
SET HEAD ON
SET VERIFY OFF
set pages 100
set lines 79

PROMPT
PROMPT Table Fragmentation Report
PROMPT

col owner form a12
col table_name form a20
col empty_blocks form 999,999 heading "Empty Blks"
col blocks form 999,999 heading "Blks"
col pct form 99

select owner, table_name, num_rows, chain_cnt, (chain_cnt*100/num_rows) pct, empty_blocks, blocks
from dba_tables
where chain_cnt > 0
and owner not in ('SYS','SYSTEM')
/

exit

================================================================

select table_name,round((blocks*8),2) "size (kb)" ,
                            round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
                            (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from user_tab_partitions
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;

===============================================================
steps to remove them :-


If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. To remove this use the following options:

1. Export and import the table (difficult to implement in production environment)
2. Move table in to different or same tablespace (Depends upon the free space available in the tablespace)

Here for the 2nd  option:

1. Collect status of all the indexes on the table.
select index_name,status from user_indexes where table_name like 'table_name';

status may be valid or unusable.

2. Command to move in to new tablespace:
alter table <tb_name> enable row movement;---- Run this command before moving table
alter table table_name move tablespace new_tablespace_name

3. Command to move in to old tablespace
alter table table_name move tablespace old_tablespace_name

If we have free space available  in the tablespace which contain the table. Then we can replace step 2 and 3 by

alter table table_name move ;
alter table <tb_name> disable row movement;---- Run this command after moving table

4. rebuild all indexes
We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.
select index_name from user_indexes where table_name like 'table_name';
alter index index name rebuild online;

5. check status of all the indexes
select index_name,status from user_indexes where table_name like 'table_name';
here value in status field must be valid.

6. Crosscheck space is reclaimed in tablespace

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Above query will show increase in value of Free Size MB field of tablespce which owns fragmented table.

7. Gather table states:
exec dbms_stats.gather_table_stats('schema_name','table_name');