Tablespace Empty Checking

list up segments and corresponding tablespaces

We usually need to check if tablespaces are empty or not.
That means list segments and corresponding tablespaces.
We can deal this requirement with the following statement.

select * from 
(select tablespace_name from dba_tablespaces ts where tablespace_name like '%PATTERN%') ts
full outer join
(select segment_name, tablespace_name from user_segments sg where tablespace_name like '%PATTERN%') sg
on sg.tablespace_name = ts.tablespace_name

list up all empty tablespaces

We also need list up all the empty tablespaces.
That means list segments and corresponding tablespaces.
We can deal this requirement with the following statement.

select ts.*, sg.tablespace_name from 
(select tablespace_name from dba_tablespaces ts) ts
full outer join
(select segment_name, tablespace_name from dba_segments) sg
on sg.tablespace_name = ts.tablespace_name
where sg.tablespace_name is null and ts.tablespace_name like '%PATTERN%'