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 practice, we reorganize and consolidate segments to make them smaller and condensed by performing rebuilding indexes and moving tables to one tablespace.
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;
TABLESPACE_NAME
------------------------------
ERPTBS_16
ERPTBS_23
ERPTBS_41
ERPTBS_42
ERPTBS_86
...
Next, let's see how we reclaim unused space, there're 2 options we can choose:
- Dropping empty tablespaces
- Shrinking empty tablespaces
It's clean and nothing is left, but you have to be carefully because it's irreversible.
It's safer and no datafile is deleted.