• Arun Kumar

Oracle Shrink Tables

Over time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space is referred to as fragmented free space.


Shrink Table


Command to check the size of block:

SELECT BLOCKS FROM DBA_TABLES WHERE TABLE_NAME=EMP;

Make sure you compute statistics before checking the block size:

ANALYZE TABLE EMP COMPUTE STATISTICS;

Based on recommendations, follow below commands

ALTER TABLE SCOTT.EMP ENABLE ROW MOVEMENT;

ALTER TABLE SCOTT.EMP SHRINK SPACE COMPACT;

OR

ALTER TABLE SCOTT.EMP SHRINK SPACE CASCADE; 

(To recover space for the objects and all dependent objects)


Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

SQL Data-Type conversion functions

SQL conversion functions are single row functions designed to alter the nature of the data type of a column value, expression, or literal. TO_CHAR, TO_NUMBER, and TO_DATE are the three most widely use

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback