Toad World® Forums

Administrative/Tablespaces display indicates objects in Tablespaces


#1

Hello,

We are using TOAD 9.5 displaying tablespaces of a 11.1.0.7 database.
And, we are looking to drop empty tablespaces after the OATM migration during this initial test run.
In several tablespaces(one is GLD for example) TOAD shows space utilized(solid color) by objects.
When I use SQL select count(*) from dba_segments where tablespace_name = ‘GLD’, the query returns a count of zero. When I select ‘objects’ when GLD is highlighted there is no data displayed. So, what should I believe, the tablespaces that have no segment count can be dropped? or Is TOAD seeing something that I am not recognizing?
Thanks in advance,
George


#2

If it’s a temporary tablespace , you aren’t going to find anything
in dba_segments for it. You have to look in v$temp_extent_pool.

For permanent datafiles , the query we use joins dba_free_space and
dba_datafiles . I’m not sure offhand why these numbers could differ. If
the amount in use is small, it could just represent some overhead used by the
tablespace .

If you attempt to drop the tablespace WITHOUT the ‘cascade’ option,
Oracle won’t let you drop the tablespace if there are any objects left in
it.

-John


#3

Thanks, John