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 ='<OWNER>' 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');
==================================================================
######TABLE SIZE BEFORE MOVEMENT
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name = 'T217';
SUM(BYTES)/1024/1024
--------------------
1792
#######RECLAIMABLE SPACE BEFORE TABLE MOVEMENT
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size",
round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % "
from dba_tables where table_name ='T1934' AND OWNER LIKE 'DMIN';
OWNER TABLE_NA Fragmented size Actual size ROUND((BLOCKS*8),2)-ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)||'KB' reclaimable space %
-------- --------- ---------------- --------- ---------------- --------------------
DMIN T217 1772976kb 967495.95kb 805480.05kb 35.4309618
===========================================================================================================================================
####Table RE-organization
SQL>alter table aradmin.t222 enable row movement;
SQL>alter table aradmin.t222 move ;
#### Before REBUILDING AL INDEXES CHECK THE STATUS
SQL> select INDEX_NAME, INDEX_TYPE, OWNER, TABLE_OWNER, status from dba_indexes where TABLE_NAME = 'T217';
INDEX_NAME INDEX_TYPE TABLE_OWNER STATUS
------------------------------ --------------------------- ------------------------------ --------
IT217 NORMAL DMIN VALID
I217_536870919_1 NORMAL DMIN VALID
I217_536871159_1 NORMAL DMIN VALID
I217_536870913_1 NORMAL DMIN VALID
I217_60000001_1 NORMAL DMIN VALID
I217_7_1 NORMAL DMIN VALID
I217_2_1 NORMAL DMIN VALID
I217_536871222_1 NORMAL DMIN VALID
SYS_IL0001084818C00213$$ LOB DMIN VALID
SELECT INDEX_OWNER, INDEX_NAME, COLUMN_NAME, TABLE_OWNER FROM DBA_IND_COLUMNS WHERE TABLE_NAME = 'T217';
INDEX_OWNER INDEX_NAME COLUMN_NAME TABLE_OWNER
------------------------------ ------------------------------ ------------------------- ------------------------------
DMIN I217_536870919_1 C536870919 DMIN
DMIN I217_536870919_1 C4 DMIN
DMIN I217_536871159_1 C536871159 DMIN
DMIN I217_536870913_1 C536870913 DMIN
DMIN I217_536870913_1 C60000001 DMIN
DMIN I217_60000001_1 C60000001 DMIN
DMIN I217_60000001_1 C536870919 DMIN
DMIN I217_7_1 C7 DMIN
DMIN I217_2_1 C2 DMIN
DMIN I217_536871222_1 C536871222 DMIN
DMIN IT217 C1 DMIN
11 rows selected.
###### reBUILD INDEXES AFTER TABLE MOVEMENT.
alter index DMIN.IT217 rebuild online;
alter index DMIN.I217_536870919_1 rebuild online;
alter index DMIN.I217_536871159_1 rebuild online;
#### aFTER REBUILDING AL INDEXES AGAIN CHECK THE STATUS
SQL> select INDEX_NAME, INDEX_TYPE, OWNER, TABLE_OWNER, status from dba_indexes where TABLE_NAME = 'T217';
####################GATHER TABLE STATS#################
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'ARADMIN', tabname=>'T222',
estimate_percent=>100, method_opt=>'FOR ALL COLUMNS SIZE AUTO',block_sample =>TRUE, granularity=>'ALL', cascade=>true);
######CHECK TABLE SIZE AND RECLAIMABLE SPACE BY EXECUTING THE FIRST TWO QUERIES.
Wish after all these activities, table shud not use more space...
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size",round((num_rows*avg_row_len/1024),2)||'kb' "Actual size",
round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10
"reclaimable space % " from dba_tables where OWNER='DMIN';
======================================================================
select owner,table_name,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"
,((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from
dba_tables where owner ='DMIN' and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)> 100 order by 6 desc;
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 ='<OWNER>' 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');
==================================================================
######TABLE SIZE BEFORE MOVEMENT
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name = 'T217';
SUM(BYTES)/1024/1024
--------------------
1792
#######RECLAIMABLE SPACE BEFORE TABLE MOVEMENT
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size",
round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % "
from dba_tables where table_name ='T1934' AND OWNER LIKE 'DMIN';
OWNER TABLE_NA Fragmented size Actual size ROUND((BLOCKS*8),2)-ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)||'KB' reclaimable space %
-------- --------- ---------------- --------- ---------------- --------------------
DMIN T217 1772976kb 967495.95kb 805480.05kb 35.4309618
===========================================================================================================================================
####Table RE-organization
SQL>alter table aradmin.t222 enable row movement;
SQL>alter table aradmin.t222 move ;
#### Before REBUILDING AL INDEXES CHECK THE STATUS
SQL> select INDEX_NAME, INDEX_TYPE, OWNER, TABLE_OWNER, status from dba_indexes where TABLE_NAME = 'T217';
INDEX_NAME INDEX_TYPE TABLE_OWNER STATUS
------------------------------ --------------------------- ------------------------------ --------
IT217 NORMAL DMIN VALID
I217_536870919_1 NORMAL DMIN VALID
I217_536871159_1 NORMAL DMIN VALID
I217_536870913_1 NORMAL DMIN VALID
I217_60000001_1 NORMAL DMIN VALID
I217_7_1 NORMAL DMIN VALID
I217_2_1 NORMAL DMIN VALID
I217_536871222_1 NORMAL DMIN VALID
SYS_IL0001084818C00213$$ LOB DMIN VALID
SELECT INDEX_OWNER, INDEX_NAME, COLUMN_NAME, TABLE_OWNER FROM DBA_IND_COLUMNS WHERE TABLE_NAME = 'T217';
INDEX_OWNER INDEX_NAME COLUMN_NAME TABLE_OWNER
------------------------------ ------------------------------ ------------------------- ------------------------------
DMIN I217_536870919_1 C536870919 DMIN
DMIN I217_536870919_1 C4 DMIN
DMIN I217_536871159_1 C536871159 DMIN
DMIN I217_536870913_1 C536870913 DMIN
DMIN I217_536870913_1 C60000001 DMIN
DMIN I217_60000001_1 C60000001 DMIN
DMIN I217_60000001_1 C536870919 DMIN
DMIN I217_7_1 C7 DMIN
DMIN I217_2_1 C2 DMIN
DMIN I217_536871222_1 C536871222 DMIN
DMIN IT217 C1 DMIN
11 rows selected.
###### reBUILD INDEXES AFTER TABLE MOVEMENT.
alter index DMIN.IT217 rebuild online;
alter index DMIN.I217_536870919_1 rebuild online;
alter index DMIN.I217_536871159_1 rebuild online;
#### aFTER REBUILDING AL INDEXES AGAIN CHECK THE STATUS
SQL> select INDEX_NAME, INDEX_TYPE, OWNER, TABLE_OWNER, status from dba_indexes where TABLE_NAME = 'T217';
####################GATHER TABLE STATS#################
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'ARADMIN', tabname=>'T222',
estimate_percent=>100, method_opt=>'FOR ALL COLUMNS SIZE AUTO',block_sample =>TRUE, granularity=>'ALL', cascade=>true);
######CHECK TABLE SIZE AND RECLAIMABLE SPACE BY EXECUTING THE FIRST TWO QUERIES.
Wish after all these activities, table shud not use more space...
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size",round((num_rows*avg_row_len/1024),2)||'kb' "Actual size",
round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10
"reclaimable space % " from dba_tables where OWNER='DMIN';
======================================================================
select owner,table_name,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"
,((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from
dba_tables where owner ='DMIN' and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)> 100 order by 6 desc;
No comments:
Post a Comment