You can identify the tablespaces that are empty with this: select tablespace_name from dba_tablespaces where tablespace_name not in (select tablespace_name from dba_segments) ; And with that list, drop the selected tablespaces with drop tablespace <tablespace name> including contents and data files;

Only top voted, non community-wiki answers of a minimum length are eligible