Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Check reclaimable space

Ali Raza MemonJul 24 2022

We would like to know how much space can be reclaimed from a table. Here is the example. The following query show the space occupied by the table:

SELECT * FROM (SELECT segment_name, bytes/1024/1024 size_mb 
FROM dba_segments where owner = 'ADMIN1' and segment_name='T4');  

SEGMENT_NAME			  SIZE_MB
------------------------------ ----------
T4				       12

Table T4 has the following number of rows:

SQL> select count(*) from admin1.T4;
 COUNT(*)
----------
   72374

After deleting all rows, the table it still shows the same size.

SQL> delete admin1.t4;

72374 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from admin1.t4;

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

SQL> SELECT * FROM (SELECT segment_name, bytes/1024/1024 size_mb 
FROM dba_segments where owner = 'ADMIN1' and segment_name='T4');  2  

SEGMENT_NAME			 SIZE_MB
------------------------------ ----------
T4				    12

SQL> 

I know how to reclaim this space. But we need to prepare a report where we need to mention that, for example, X MBs can be re-claimed from table T4.

Comments

Post Details

Added on Jul 24 2022
2 comments
1,183 views