Table fragmentation

Example:

Create a table as follows

create table ui as select * from dba_objects

Execute Select

select bytes/(1024*1024), s.* from
dba_segments s
where segment_name ='UI'

Do insert couple of times

insert into UI select * from dba_objects;

Now check again the table size using above sql

Now delete some rows from table

delete from UI where rownum < 100000;

Now, if we recheck the tablesize we notice that there will be no change in table size though we deleted some records...

To reclaim the space we have following options


Fix 1) Use Alter Table Move command & then re-build indexes (if any)

alter table UI move  tablespace USERS

Fix 2)
Export the table data
Truncate the table
Import the table data

Fix 3)
Use create table ui_bkp as select * from ui_bkp and then truncate UI table & then insert data back into UI table by selecting from ui_bkp table

Fix 4) Use DBMS_REDEFINITION Package

SQL to find wasted space in a table:

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 = 'UI'