I Want To Be Alone

It's possible that a tablespace may have but one table or index located therein. Usually it doesn't matter how may tables/indexes are in a tablespace, but somehow you ended up with a vast expanse of storage with only one segment in residence. How on earth can you report which tablespace is the hiding place for this segment?

Oracle provides two views to report where segments are located:

DBA_SEGMENTS
USER_SEGMENTS

Only DBA_SEGMENTS provides the necessary tools to determine which tablespace, if any, houses only one segment, and if you don't have access to that view you can't run the query. The query itself is pretty simple:

select tablespace_name, count(*)
from dba_segments
group by tablespace_name
having count(*) = 1;

Such a query will return any and all tablespaces containing one, and only one, segment. You can't modify the query to include the owner, though, as that may return any number of tablespaces having more than one segment, but only one segment owned by a particular user (and this is why you can't use the USER_SEGMENTS view, as that only reports objects owned by the currently logged on user):

SQL> select tablespace_name, count(*)
  2  from dba_segments
  3  group by tablespace_name
  4  having count(*) = 1
  5  /

no rows selected

SQL> select owner, tablespace_name, count(*)
  2  from dba_segments
  3  group by owner, tablespace_name
  4  having count(*) = 1
  5  /

OWNER                          TABLESPACE_NAME                  COUNT(*)
------------------------------ ------------------------------ ----------
SYS                            TOOLS                                   1

SQL>

Note no tablespaces in this database have only one segment, however the TOOLS tablespace does contain ONE segment owned by SYS. So, as shown, the first query is the only query which returns the desired information.

Now, get going and find those stragglers!