Reclaiming Unused Space

At the time of table creation there is no extend allocate for this table, when table data are increasing/inserting new extend are allocating for this table and extend are not leave until table are :

1. drop
2. Truncate
3. Move table to same/another tablespace.

After deleting data from table space are not release, because when we delete data from the table block will go onto the freelist for that table only, for that another table/extend are not able to use this free blocks and space are not usable by another table/extends.

Now question is how we can Reclaim this free space ?

1. Move Tablespace.
2. Export and import table by using data pump.
3. Redefine table using DBMS_REDEFINITION
4. Create new table, drop existing table, rename new table [Must create existing index in new table].
5. Drop table [If there is no data and table are not required in future ].
6. Truncate table [if Table data are not required].

SQL> CREATE TABLESPACE TBS_DATA DATAFILE
  2    '/u01/app/oracle/oradata/orcln1/tbs_data01.dbf' SIZE 500M AUTOEXTEND OFF;

Tablespace created.

SQL> SELECT A.TABLESPACE_NAME,
  2         (SELECT NVL(SUM (BYTES) / 1024,0)
  3            FROM DBA_FREE_SPACE
  4           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  5            FREE_SPACE,
  6         (SELECT NVL(SUM (BYTES) / 1024,0)
  7            FROM DBA_EXTENTS
  8           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  9            USED_SPACE,
 10         (SELECT COUNT (*)
 11            FROM DBA_EXTENTS
 12           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
 13            TOTAL_EXTENTS
 14    FROM DBA_TABLESPACES A
 15   WHERE TABLESPACE_NAME = 'TBS_DATA';

TABLESPACE_NAME                FREE_SPACE USED_SPACE TOTAL_EXTENTS
------------------------------ ---------- ---------- -------------
TBS_DATA                           510976          0             0

SQL>
--- There is no extends in this tablespace, so total space of this tablespace are free.
SQL> CREATE TABLE TEST_RECLAIM_SPACE
  2  (SERIAL_NO NUMBER,
  3   DATA1     VARCHAR2(4000),
  4   DATA2     VARCHAR2(4000),
  5   DATA3     VARCHAR2(4000)
  6  ) TABLESPACE TBS_DATA;

Table created.

SQL> INSERT /*+append*/ INTO TEST_RECLAIM_SPACE
  2  SELECT SERIAL_NO, DATA||DATA||DATA||DATA||DATA||DATA DATA1, DATA||DATA||DATA||DATA||DATA||DATA DATA2,
  3   DATA||DATA||DATA||DATA||DATA||DATA DATA3
  4  FROM(
  5  SELECT LEVEL SERIAL_NO, 'CREATE TABLE TEST_RECLAIM_SPACE
  6  (SERIAL_NO NUMBER,
  7   DATA1     VARCHAR2(4000),
  8   DATA2     VARCHAR2(4000),
  9   DATA3     VARCHAR2(4000)
 10  ) TABLESPACE TBS_DATA
 11  ' DATA
 12  FROM DUAL
 13  CONNECT BY LEVEL <= 100000);

100000 rows created.

SQL>  SELECT A.TABLESPACE_NAME,
  2          (SELECT NVL(SUM (BYTES) / 1024,0)
  3             FROM DBA_FREE_SPACE
  4            WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  5             FREE_SPACE,
  6          (SELECT NVL(SUM (BYTES) / 1024,0)
  7             FROM DBA_EXTENTS
  8            WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  9             USED_SPACE,
 10          (SELECT COUNT (*)
 11             FROM DBA_EXTENTS
 12            WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
 13             TOTAL_EXTENTS
 14     FROM DBA_TABLESPACES A
 15    WHERE TABLESPACE_NAME = 'TBS_DATA';

TABLESPACE_NAME                FREE_SPACE USED_SPACE TOTAL_EXTENTS
------------------------------ ---------- ---------- -------------
TBS_DATA                           101376     409600           121

SQL> DELETE FROM TEST_RECLAIM_SPACE;

100000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM TEST_RECLAIM_SPACE;

  COUNT(*)
----------
         0

SQL>

SQL> SELECT A.TABLESPACE_NAME,
  2         (SELECT NVL(SUM (BYTES) / 1024,0)
  3            FROM DBA_FREE_SPACE
  4           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  5            FREE_SPACE,
  6         (SELECT NVL(SUM (BYTES) / 1024,0)
  7            FROM DBA_EXTENTS
  8           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  9            USED_SPACE,
 10         (SELECT COUNT (*)
 11            FROM DBA_EXTENTS
 12           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
 13            TOTAL_EXTENTS
 14    FROM DBA_TABLESPACES A
 15   WHERE TABLESPACE_NAME = 'TBS_DATA';

TABLESPACE_NAME                FREE_SPACE USED_SPACE TOTAL_EXTENTS
------------------------------ ---------- ---------- -------------
TBS_DATA                           101376     409600           121

SQL> ALTER TABLE TEST_RECLAIM_SPACE MOVE;

Table altered.

SQL> SELECT A.TABLESPACE_NAME,
  2         (SELECT NVL(SUM (BYTES) / 1024,0)
  3            FROM DBA_FREE_SPACE
  4           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  5            FREE_SPACE,
  6         (SELECT NVL(SUM (BYTES) / 1024,0)
  7            FROM DBA_EXTENTS
  8           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  9            USED_SPACE,
 10         (SELECT COUNT (*)
 11            FROM DBA_EXTENTS
 12           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
 13            TOTAL_EXTENTS
 14    FROM DBA_TABLESPACES A
 15   WHERE TABLESPACE_NAME = 'TBS_DATA';

TABLESPACE_NAME                FREE_SPACE USED_SPACE TOTAL_EXTENTS
------------------------------ ---------- ---------- -------------
TBS_DATA                           510912         64             1

SQL>