Oracle Tips and Tricks — David Fitzjarrell

August 5, 2008

I Want To Be Alone

Filed under: General — dfitzjarrell @ 16:44

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!

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: