Example for Table Reorg and Reclaim the Fragmented Space Using Table Movement

advertisements

_____________________________________________________________________________________________________________________

        - It is time a consuming method

        - Application down time required as the table will not be available during the time of activity

        - There are chances for the depended objects invalidation. It has to be properly verified after reorg

        - Indexes also needs to be rebuild which requires an extra time apart from the table movement

        - This activity requires extra space on other tablespace to move the table.

Example

//Create a test table and insert testing data
SQL> create table tablemove_expl as select * from all_objects;
Table created.

SQL> INSERT INTO tablemove_expl (SELECT * FROM tablemove_expl);

67816 rows created.
 
SQL> /
 
135632 rows created.
 
SQL> /
 
271264 rows created.
 
SQL> /
 
542528 rows created.
 

SQL> /

 
1085056 rows created.
 
SQL> /
 
2170112 rows created.
 
SQL> commit;
 
Commit complete.
 

// Create a sample procedure

CREATE or replace PROCEDURE proc_tablemove_expl AS

X VARCHAR2(30);

BEGIN

SELECT '1' INTO X FROM tablemove_expl WHERE ROWNUM<2 span="">

END;

/

Procedure created.

// Checking the status of the object

SQL>  SELECT OBJECT_NAME , STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME IN (select name from dba_dependencies

where REFERENCED_NAME='TABLEMOVE_EXPL');

OBJECT_NAME                    STATUS

------------------------------ -------
PROC_TABLEMOVE_EXPL            VALID
 
//Create index on the table
SQL> create index idx_tablemove_expl on tablemove_expl(owner);
 
Index created.
SQL> select status,index_name from dba_indexes where table_name = 'TABLEMOVE_EXPL';

STATUS   INDEX_NAME

-------- ------------------------------
VALID    IDX_TABLEMOVE_EXPL
 
//488 MB occupied by the table
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='TABLEMOVE_EXPL';
 
SUM(BYTES)/1024/1024
--------------------
                 488
 
//USERS tablespace has 380MB space
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
-------------------- ------------------------------
             380.625 USERS
 
//Table has 4340224 records
SQL> SELECT COUNT(*) FROM tablemove_expl;
 
  COUNT(*)
----------
   4340224
 
//Delete half of the records
SQL> DELETE FROM tablemove_expl WHERE ROWNUM<2170080 o:p="">
 
2170079 rows deleted.
 
SQL> COMMIT;
Commit complete.
 
// Still the space consumtion is 488 and there are fragmentaions inside the table segments.
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='TABLEMOVE_EXPL';

SUM(BYTES)/1024/1024

--------------------
                 488
 
//Analyzing the table statistics
SQL> ANALYZE TABLE tablemove_expl COMPUTE STATISTICS;
 
Table analyzed.
 
SQL> col owner for a20
SQL> col segment_name for a20
SQL> col SizeMBS for 999999.99
col wastedMBS for 999999.99
SQL> 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 ;
Enter value for table_name: TABLEMOVE_EXPL
old   9: and    ds.segment_name='&Table_name'
new   9: and    ds.segment_name='TABLEMOVE_EXPL'
 
//283 MB fragmented space which can be reclaimed.
OWNER                SEGMENT_NAME            SIZEMBS  WASTEDMBS
-------------------- -------------------- ---------- ----------
STHOMAS              TABLEMOVE_EXPL           488.00     283.00
 
//Enable the row movement for the table
SQL> ALTER TABLE TABLEMOVE_EXPL ENABLE ROW MOVEMENT;
 
Table altered.
 
// Move the table to TSTLOG tablespace
SQL> ALTER TABLE TABLEMOVE_EXPL MOVE TABLESPACE TSTLOG;
 
Table altered.
 
//Move back the table to the USERS tablespace
SQL> ALTER TABLE TABLEMOVE_EXPL MOVE TABLESPACE USERS;
 
Table altered.
 
SQL> 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 ;
Enter value for table_name: TABLEMOVE_EXPL
old   9: and    ds.segment_name='&Table_name'
new   9: and    ds.segment_name='TABLEMOVE_EXPL'
 
OWNER                SEGMENT_NAME            SIZEMBS  WASTEDMBS
-------------------- -------------------- ---------- ----------
STHOMAS              TABLEMOVE_EXPL           248.00      43.00
 
SQL> select status,index_name from dba_indexes where table_name = 'TABLEMOVE_EXPL';
 
STATUS   INDEX_NAME
-------- ------------------------------
UNUSABLE IDX_TABLEMOVE_EXPL
 
SQL> alter index IDX_TABLEMOVE_EXPL rebuild online;
 
Index Created.
 
// Make sure all the depended objects are in VALID status.
SQL> SELECT OBJECT_NAME , STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME IN (select name from dba_dependencies where

REFERENCED_NAME='TABLEMOVE_EXPL');

 
OBJECT_NAME                    STATUS
------------------------------ -------
PROC_TABLEMOVE_EXPL            VALID

_____________________________________________________________________________________________________________________

Website Stats