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');
No comments:
Post a Comment