Shrink Option for Oracle Database

If you delete some records in a SAP database table , it is a good option to make a “Shrink” operation on database level.You can do this online and easly with Oracle 11G like shown below…

Go to OS level and open Sqlplus editor ;

For an example ; I have deleted some records in table “SXMSCLUP”

- First check the size of the table before the operation

SQL> select segment_name,segment_type,bytes/1024/1024 MB
  2   from dba_segments
  3   where  segment_name=’SXMSCLUP’;

SEGMENT_NAME                  SEGMENT_TYPE               MB
——————–                      ——————                     ———-
SXMSCLUP                               TABLE                                   34017

- Now we can use the “row movement” and “shrink space” commands

SQL> alter table sapsr3.SXMSCLUP enable row movement;

Table altered.

SQL> alter table sapsr3.SXMSCLUP shrink space;

Table altered.

- Let’s check the table size again

SQL> select segment_name,segment_type,bytes/1024/1024 MB
  2   from dba_segments
  3   where  segment_name=’SXMSCLUP’;

SEGMENT_NAME                  SEGMENT_TYPE               MB
——————–                      ——————                     ———-
SXMSCLUP                               TABLE                                   20843

Leave a Reply


− 4 = five

Blogroll