How to Shrink a Table in Oracle Database - With Example

advertisements

_____________________________________________________________________________________________________________________

Database reorg is the major activity for the Oracle DBAs. Oracle provides shrink table feature to reclaim the space within table after doing the deletes. Shrink is accomplished by rearranging or moving the rows between the oracle blocks. To achieve this table shrinking we need to enable the row movement feature which is associated with the table.

Example
//Creating an example table
SQL> create table objlist as select * from  all_objects;

Table created.
SQL> select blocks from dba_tables where table_name='OBJLIST';
    BLOCKS
----------
SQL> ANALYZE TABLE OBJLIST COMPUTE STATISTICS;

Table analyzed.

// 990 blocks occupied by the table
SQL> select blocks , NUM_ROWS from dba_tables where table_name='OBJLIST';
    BLOCKS   NUM_ROWS
---------- ----------
       990      67815
SQL> select tablespace_name from dba_segments where segment_name ='OBJLIST';

TABLESPACE_NAME

------------------------------
USERS
// 688 free blocks in users tablespace
SQL> select sum(blocks) from dba_free_space where tablespace_name='USERS';

SUM(BLOCKS)

-----------
        688
// Deleting the half of the rows.
SQL> DELETE FROM OBJLIST WHERE ROWNUM<40000 o:p="">

39999 rows deleted.

SQL> COMMIT;

Commit complete.
SQL> select blocks, NUM_ROWS from dba_tables where table_name='OBJLIST';
    BLOCKS   NUM_ROWS
---------- ----------
       990      67815

SQL> ANALYZE TABLE OBJLIST COMPUTE STATISTICS;

Table analyzed.

//Still the table is occupying 990 block as the High water mark is not reset.
SQL> select blocks , NUM_ROWS from dba_tables where table_name='OBJLIST';
    BLOCKS   NUM_ROWS
---------- ----------
       990      27816
SQL> select blocks , NUM_ROWS ,ROW_MOVEMENT from dba_tables where table_name='OBJLIST';

    BLOCKS   NUM_ROWS ROW_MOVE
---------- ---------- --------
       990      27816 DISABLED
//Total 8192 KB is occupied by this table.
SQL> select bytes/1024 from dba_segments where segment_name ='OBJLIST';

BYTES/1024
----------
      8192
//Enabling the row movement.
SQL> alter table OBJLIST enable row movement;

Table altered.
//Shrink and reset the High water mark.
SQL> alter table OBJLIST shrink space;

Table altered.

SQL> ANALYZE TABLE OBJLIST COMPUTE STATISTICS;

Table analyzed.

//Table size reduced to 401 blocks from 990.
SQL> select blocks , NUM_ROWS ,ROW_MOVEMENT from dba_tables where table_name='OBJLIST';

    BLOCKS   NUM_ROWS ROW_MOVE

---------- ---------- --------
       401      27816 ENABLED
// The table size reduced to 3392 Kb from 8192
SQL> select bytes/1024 from dba_segments where segment_name ='OBJLIST';

BYTES/1024

----------
      3392
// The total available free blocks also increased in USERS tablespace.
SQL> select sum(blocks) from dba_free_space where tablespace_name='USERS';
SUM(BLOCKS)
-----------
       1288

_____________________________________________________________________________________________________________________

Website Stats