Check reclaimable space

ProDBA

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.

Solomon Yakobson

165.3.8 SPACE_USAGE Procedures

In addition you need to check LOB segments if table has LOBs.

SY.

ProDBA

https://community.oracle.com/tech/developers/discussion/4501222/check-reclaimable-space

Answered here: Reorganize storage structure — oracle-tech