Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Table Fragmentation

907748Dec 28 2011 — edited Dec 31 2011
Hello.
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 28 2012
Added on Dec 28 2011
16 comments
1,781 views