Shrink Space in Table in Oracle

You use online segment shrink to reclaim fragmented free space below the high water mark in an Oracle Database segment. The benefits of segment shrink are these:

• Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.
• The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.I am taking a simple example of a table in which there are lot of blocks allocated but in actuall if we check, data is less than the space consumed.[code]</pre>
  19:40:44 NUPNEJA/fsprd/FSPRD1>select owner||’.’||table_name table_name,
  19:41:37 2 blocks,
  19:41:37 3 num_rows,
  19:41:37 4 avg_row_len,
  19:41:37 5 round(((blocks*8/1024)),2) "TOTAL_SIZE",
  19:41:37 6 round((num_rows*avg_row_len/1024/1024),2) "ACTUAL_SIZE",
  19:41:37 7 round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE"

  19:41:37 8 from dba_tables
  19:41:38 9 where owner=’SYSADM’
  19:41:38 10 and blocks > 10*(8*1024)
  19:41:38 11 and table_name=’PSPRCSPARMS’
  19:41:38 12 order
  19:41:38 13 by 7 desc
  19:41:38 14 /

  TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN TOTAL_SIZE(MB) ACTUAL_SIZE(MB)
  —————————— ———- ———- ———– ————– —————
  FRAGMENTED_SPACE(MB)
  ——————–
  SYSADM.PSPRCSPARMS 1421484 85507 344 11,105 28
  11,077

  1 row selected.
  <pre>[/code]

  So if you can see the result of above query, there is lot of space that is unused in the table.

  Now, before we do Shrink of table, we need to enable Row movement of table so the if required rows with in the tables can be moved.
  [code]</pre>
  17:14:11 NUPNEJA/fssup/FSSUP>ALTER TABLE PSPRCSPARMS ENABLE ROW MOVEMENT;

  Table altered.
  <pre>[/code]

  Now we will do shrink of table, in two steps

  1. we will first do Shrink compact —- This will not reset the High watermark for the table and it will also not lock the table for the duration compact shrink command will run. It will require lock on table only at last of this command when this will actually release the space. DML and other operations during this period will be allowed on the table except for a short period when there will be lock on the table.

  2. Now we will run the command to shrink the table which will now not take much of a time to reset the high watermark as space is already been released.
  [code]</pre>
  17:14:34 NUPNEJA/fssup/FSSUP>alter table SYSADM.PSPRCSPARMS shrink space compact;

  Table altered.

  17:30:19 NUPNEJA/fssup/FSSUP>alter table SYSADM.PSPRCSPARMS shrink space;

  Table altered.
  <pre>[/code]

  After doing this, you need to gather the stats on the table so that DBA_TABLES view can be updated (BLOCKS,EMPTY_BLOCKS)
  [code]</pre>
  17:32:26 NUPNEJA/fssup/FSSUP>SELECT BLOCKS FROM DBA_TABLES WHERE TABLE_NAME=UPPER(‘PSPRCSPARMS’);

  BLOCKS
  ———-
  4370

  1 row selected.

  17:32:34 NUPNEJA/fssup/FSSUP>SELECT BLOCKS
  17:32:58 2 FROM DBA_SEGMENTS
  17:32:58 3 WHERE OWNER=UPPER(‘SYSADM’) AND SEGMENT_NAME = UPPER(‘PSPRCSPARMS’);

  BLOCKS
  ———-
  4456

  1 row selected.
  <pre>[/code]

  Disable the row movement.
  [code]</pre>
  17:33:00 NUPNEJA/fssup/FSSUP>alter table PSPRCSPARMS disable row movement;

  Table altered.
  <pre>[/code]

  I also checked for the status of Indexes on the table after this operation which shows index was valid.

  [code]

  17:33:56 NUPNEJA/fssup/FSSUP>/

  INDEX_NAME TABLE_NAME STATUS FUNCIDX_ INDEX_TYPE
  —————————— —————————— ——– ——– ——————–
  PS_PSPRCSPARMS PSPRCSPARMS VALID NORMAL

  1 row selected.
  <pre>[/code]

  Thanks…..