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.