Table Fragmentation
907748Dec 28 2011 — edited Dec 31 2011Hello.
While investigating space related issues on one of our databases, I learnt (using the below SQL script) that due to high DMLs the physical size of many of the tables in the database is much higher than the actual size.
set lines 1000 pages 1000
select table_name,
round((blocks*8)/1024,2) "Physical Size (MB)",
round((num_rows*avg_row_len/1024/1024),2) "Actual Size (MB)",
(round((blocks*8)/1024,2)-round((num_rows*avg_row_len/1024/1024),2)) "Wasted Space (MB)"
from dba_tables
and (round((blocks*8)/1024,2)-round((num_rows*avg_row_len/1024/1024),2)) > 20
order by (round((blocks*8)/1024,2)-round((num_rows*avg_row_len/1024/1024),2)) desc;
Most of the tables which were outputted by the above query are having LONG (RAW) columns.
I have tried Export/Import, ALTER TABLE <..> MOVE, and CREATE TABLE AS SELECT options to reduce fragmentation more than once, and this is what I learnt:
1. The tables having LONG(RAW) columns could not be 'de-fragmented' even after Exp/imp. Note: I have not tried using the CREATE TABLE AS SELECT option yet on these.
2. The tables for which I have used Exp/Imp which does not have LONG(RAW) columns show no improvement! However with CREATE TABLE AS SELECT option reclaims bit of space (approx 10MB).
I am operating Oracle 10.2.0.3 on Solaris 10 sun4u sparc SUNW,Sun-Fire-15000 machine.
Please advice what would be a good option to reclaim the wasted space.
Note: There are cases wherein the physical size of table is 7Gb while actual size is only 200M!!
For any additional information, please let me know.
Thanks,
Suddhasatwa