We have deleted records from big tables in database. But each table still holds the same blocks and extents. It means they have not released the space logically. How to use this space.

Let us see the steps.

Example: I have created a table TEST_TABLE from emp and inserted many records to occupy many database blocks and extents.

1- Create a table TEST_TABLE

SQL> create table TEST_TABLE as select * from emp;

Table created.

Inserted many records into table TEST_TABLE

SQL> insert into TEST_TABLE select * from TEST_TABLE;
14 rows created.
SQL>/
28 rows created.
SQL> /
…..
SQL> /
1835008 rows deleted.
SQL> commit;
commit complete.

1- Check how many Blocks are being used by table TEST_TABLE

SQL> select blocks from user_segments where segment_name=’TEST_TABLE’;
BLOCKS
———-
11264

2- Check how many extents are being used by table TEST_TABLE

SQL> SELECT count(*) from user_extents where segment_name=’TEST_TABLE’;
COUNT(*)
———-
82

3- Deleted records from table TEST_TABLE.

SQL> delete from TEST_TABLE;
1835008 rows deleted.
commited
SQL> commit;
Commit complete.

4- Verify how many blocks occupied by TEST_TABLE table, result is same.

SQL> select blocks from user_segments where segment_name=’TEST_TABLE’;
BLOCKS
———-
11264

5- Verify how many extents occupied by TEST_TABLE table, result is same.

SQL> SELECT count(*) from user_extents where segment_name=’TEST_TABLE’;
COUNT(*)
———-
82

6- To make a space from the deleted records extents and blocks steps are

7- Enable the table row movement

SQL> alter table TEST_TABLE enable row movement;
Table altered.

8- Shrink space for table TEST_TABLE

SQL> alter table TEST_TABLE shrink space;
Table altered.

9- Verify again how many extents are occupied by TEST_TABLE table, result are changed to correct and achievable.

SQL> SELECT count(*) from user_extents where segment_name=’TEST_TABLE’;
COUNT(*)
———-
1

13 – And verify how many blocks are still occupied by table TEST_TABLE, result are correct.

SQL> select blocks from user_segments where segment_name=’TEST_TABLE’;
BLOCKS
———-
8

SQL>