Forum Stats

  • 3,837,078 Users
  • 2,262,223 Discussions
  • 7,900,199 Comments

Discussions

Check reclaimable space

ProDBA
ProDBA Member Posts: 120 Blue Ribbon

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.

Answers