10 Steps to reclaim the space in oracle -
1. CREATE TABLE USER.USER_TB AS SELECT * FROM ...
2. Now check the size of that created table -
SQL> SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE OWNER='USER' AND SEGMENT_NAME='USER_TB';
SUM(BYTES)
----------
13631488
3. Now check the tablespace size which using that segment -
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
USER_TABLESPACE 14 .875 6 94
4. Now delete the records from the table -
SQL> delete from USER.USER_tb;
91108 rows deleted.
5. Again check the size of that created table on which we have deleted the records -
Note - As we can see there is not space released after to delete the records..
SQL> SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE OWNER='USER' AND SEGMENT_NAME='USER_TB';
SUM(BYTES)
----------
13631488
6. Again check the tablespace size which using that segment -
Note - As we can see there is not space released after to delete the records..
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
USER_TABLESPACE 14 .875 6 94
7. Now to shrink the table but before to that we need to enable row movement else it will throws an error -
SQL> ALTER TABLE USER.USER_TB ENABLE ROW MOVEMENT;
Table altered.
8. Now space reclaim -
SQL> ALTER TABLE USER.USER_TB SHRINK SPACE CASCADE;
Table altered.
9. Now check the size of that created table on which we have deleted the records -
Note - It is showing size has reclaimed..
SQL> SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE OWNER='USER' AND SEGMENT_NAME='USER_TB';
SUM(BYTES)
----------
65536
10. Now check the tablespace size which using that segment -
Note - It is showing size has reclaimned..
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
USER_TABLESPACE 14 13.8125 99 1
Thanks
DB Tech Solutions
