Tablespace Free Space

Toad for Oracle
1

Hi

Having just installed Toad 12 a colleague noticed that the free space displayed in Schema Browser (Tablespace Tab) is incorrect. Upon investigation of the script used to collect the amount of free space we found that Toad now uses DBA_LMT_FREE_SPACE when Toad 11 used to get the data from DBA_FREE_SPACE. The problem is that DBA_LMT_FREE_SPACE regards space used by objects in the recycle bin as being used space whereas DBA_FREE_SPACE regards objects in the recycle bin as being free space.

This is confusing as using Toad 12 we see that a tablespace is full and in need of additional space when in reality there is plenty of free space as Oracle will reuse the space taken by the objects in the recycle bin.

Is there a way of getting Toad 12 to report as Toad 11 used to?

Cheers

Peter

2

Hi Peter,

In Toad 12.10, we switched to DBA_LMT_FREE_SPACE because we had reports from several users that DBA_FREE_SPACE was painfully slow. I figured that even though it does not include recyclebin objects, the difference in result would be relatively small and the improved time to the SB would be welcomed. If you need a more precise measurement, here are a few options, perhaps in the order of difficulty…

  • Purge the recycle bin, at least for the tablespace in question.

  • Pull down the Toad 12.11 beta and use that instead. In the beta, the SB Tablepsace filter option “Include usage info” has been changed from a checkbox to a dropdown that allows you to exclude usage, or include it, specifying to use either DBA_FREE_SPACE or DBA_LMT_FREE_SPACE. Database -> Administer -> Tablespaces also has an option to specify which view to get free space info from.

  • Use Spool SQL to grab the query that we run, paste it into the Editor, and swap the subquery that includes DBA_LMT_FREE_SPACE with the following, then just run it in the Editor.
    (select tablespace_name, sum(bytes) bytes_free
    from DBA_FREE_SPACE
    group by tablespace_name) b

  • Create a role for your users that does not include DBA_LMT_FREE_SPACE and grant that instead of SELECT_CATALOG_ROLE or DBA or whatever privilege you are using. Toad will revert to DBA_FREE_SPACE if you can’t select from both DBA_LMT_FREE_SPACE and either SYS.TS$ or V$TABLESPACE.
    Sorry for the inconvenience. As you see, we’ve taken care of it in the current beta and for Toad 12.11.

-John