Beside routine administration and performance tuning, space management is also a major topic for DBA to handle. Usually, space is sometimes stressed by database growth.
In the process of segment consolidation, we may leave some tablespace empty. Therefore, we should drop those unused tablespaces and their data files to reclaim the precious space.
Here is the query that we identify empty tablespaces.
In the above statement, we exclude native, temporary and non-empty tablespaces.
Let's see an example.
SQL> select name tablespace_name from v$tablespace where ts# > 6 and name not in (select distinct tablespace_name from dba_segments) and name not in (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY') order by 1;
Next, let's see how we reclaim unused space, there're 2 options we can choose: