Example for Table Reorg and Reclaim the Fragmented Space Using Shrink Space Command

advertisements

_____________________________________________________________________________________________________________________

Advantages of this method:

        - Syntax: alter table shrink space

        - Most recommended method

        - Applicable for the databases 10g and above

        - No downtime required

        - None of the depended objects will get invalidated.

        - Least time consuming method

Example
//Create a test table and insert testing data
SQL> create table objlist as select * from all_objects;
Table created.
SQL> INSERT INTO OBJLIST (SELECT * FROM OBJLIST);
67815 rows created.
SQL> /
135630 rows created.
SQL> /
271260 rows created.
SQL> /
542520 rows created.
SQL> /
1085040 rows created.
SQL> INSERT INTO OBJLIST (SELECT * FROM OBJLIST);
2170080 rows created.
SQL> COMMIT;
 
Commit complete.
 
//Checking the space occupied by the table
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='OBJLIST';
SUM(BYTES)/1024/1024
--------------------
                 488
 
//Checking the free space for the tablespace USERS where the table belongs
SQL> SELECT  sum(bytes)/1024/1024 , tablespace_name from DBA_FREE_SPACE WHERE TABLESPACE_NAME='USERS' GROUP BY tablespace_name;
SUM(BYTES)/1024/1024 TABLESPACE_NAME
------------------- ------------------------------
             460.625 USERS
//Checking the total number of records
SQL> SELECT COUNT(*) FROM OBJLIST;
  COUNT(*)
---------
   4340160
//Deleting the half of the records and this makes 50% fragmentation in the table
SQL> DELETE FROM OBJLIST WHERE ROWNUM<2170080 o:p="">

 
2170079 rows deleted.
 
SQL> COMMIT;
 
Commit complete.
// 488Mb total occupied size for the table even after deleting the records
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='OBJLIST';
 
SUM(BYTES)/1024/1024
--------------------
                488

/Free space also remains the same in the USERS tablespace

SQL> SELECT  sum(bytes)/1024/1024 , tablespace_name from DBA_FREE_SPACE WHERE TABLESPACE_NAME='USERS' GROUP BY tablespace_name;

SUM(BYTES)/1024/1024 TABLESPACE_NAME
-------------------- ------------------------------
             460.625 USERS
 
//Analyzing the table statistics to update the optimizer statistics
 
SQL>  ANALYZE TABLE OBJLIST COMPUTE STATISTICS;
Table analyzed.
 
col owner for a20
col segment_name for a20
col SizeMBS for 999999.99
col wastedMBS for 999999.99
select      ds.owner,
      ds.segment_name,
      round(ds.bytes/1024/1024,0) SIzeMBS,
      round((ds.bytes-(dt.num_rows*dt.avg_row_len) )/1024/1024,0) WASTEDMBS
from dba_segments ds, dba_tables dt
where       ds.owner=dt.owner
and   ds.segment_name = dt.table_name
and   ds.segment_type='TABLE'
and   ds.segment_name='&Table_name'
group by ds.owner, ds.segment_name, round(ds.bytes/1024/1024,0) ,round((ds.bytes-(dt.num_rows*dt.avg_row_len))/1024/1024,0)
having      round(bytes/1024/1024,0) >100
order by round(bytes/1024/1024,0) desc ;
 
// fragmented space or reclaimable space is about 281 Mb

OWNER                SEGMENT_NAME            SIZEMBS  WASTEDMBS

-------------------- -------------------- ---------- ----------
STHOMAS              OBJLIST                  488.00     281.00
 
//Enabling the row movement for table shrinking
SQL> ALTER TABLE OBJLIST ENABLE ROW MOVEMENT;
 
Table altered.

SQL> ALTER TABLE OBJLIST SHRINK SPACE;
 
Table altered.

col owner for a20
col segment_name for a20
col SizeMBS for 999999.99
col wastedMBS for 999999.99
select      ds.owner,
      ds.segment_name,
      round(ds.bytes/1024/1024,0) SIzeMBS,
      round((ds.bytes-(dt.num_rows*dt.avg_row_len) )/1024/1024,0) WASTEDMBS
from dba_segments ds, dba_tables dt
where       ds.owner=dt.owner
and   ds.segment_name = dt.table_name
and   ds.segment_type='TABLE'
and   ds.segment_name='&Table_name'
group by ds.owner, ds.segment_name, round(ds.bytes/1024/1024,0) ,round((ds.bytes-(dt.num_rows*dt.avg_row_len))/1024/1024,0)
having      round(bytes/1024/1024,0) >100
order by round(bytes/1024/1024,0) desc ;
 
// After shrinking the value reduced to 35 which is normal
OWNER                SEGMENT_NAME            SIZEMBS  WASTEDMBS
-------------------- -------------------- ---------- ----------
STHOMAS              OBJLIST                  242.00      35.00
 
//The total free space for the USERs tablespace increased to 706 from 460.625(246MB reclaimed)

SQL> SELECT  sum(bytes)/1024/1024 , tablespace_name from DBA_FREE_SPACE WHERE TABLESPACE_NAME='USERS' GROUP BY tablespace_name;

 

SUM(BYTES)/1024/1024 TABLESPACE_NAME

-------------------- ------------------------------
            706.4375 USERS

_____________________________________________________________________________________________________________________

Website Stats