• Questions
  • Query both DBA_FREE_SPACE and DBA_TEMP_FREE_SPACE on view

Breadcrumb

Announcement

Forty years

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Quanwen.

Asked: November 13, 2019 - 2:03 am UTC

Answered by: Connor McDonald - Last updated: November 14, 2019 - 11:26 pm UTC

Category: Database Administration - Version: 11.2.0.4.0

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Passing parameters

You Asked

Hello Connor & Chris :-),

A few days ago I found this fantastic SQL script checking Tablespace Free Space being written by Tom Kyte - https://asktom.oracle.com/Misc/free.html .

So I spent some time formatting and aligning it (BTW convering Kb into Mb). The following is full code,

set linesize 200
set pagesize 200

column dummy          noprint
column pct_used       format 999.9       heading "%|Used"
column name           format a25         heading "Tablespace Name"
column Mbytes         format 999,999,999 heading "MBytes" 
column Used_Mbytes    format 999,999,999 heading "Used|MBytes"
column Free_Mbytes    format 999,999,999 heading "Free|MBytes"
column Largest_Mbytes format 999,999,999 heading "Largest|MBytes"
column Max_Size       format 999,999,999 heading "MaxPoss|MBytes"
column pct_max_used   format 999.9       heading "%|Max|Used"

break   on  report 
compute sum of Mbytes      on report 
compute sum of Free_Mbytes on report 
compute sum of Used_Mbytes on report 

select ( select decode(extent_management,'LOCAL','*',' ') || 
                decode(segment_space_management,'AUTO','a ','m ')
        from dba_tablespaces
        where tablespace_name = b.tablespace_name
      ) || nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name
      , Mbytes_alloc Mbytes
      , Mbytes_alloc-nvl(Mbytes_free,0) Used_Mbytes
      , nvl(Mbytes_free,0) Free_Mbytes
      , ((Mbytes_alloc-nvl(Mbytes_free,0))/Mbytes_alloc)*100 pct_used
      , nvl(Mbytes_largest,0) Largest_Mbytes
      , nvl(Mbytes_max,Mbytes_alloc) Max_Size
      , decode(Mbytes_max,0,0,(Mbytes_alloc/Mbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024/1024   Mbytes_free
               , max(bytes)/1024/1024 Mbytes_largest
               , tablespace_name
       from  sys.dba_free_space
       group by tablespace_name
     ) a,
     ( select sum(bytes)/1024/1024      Mbytes_alloc
               , sum(maxbytes)/1024/1024 Mbytes_max
               , tablespace_name
       from sys.dba_data_files
       group by tablespace_name
       union all
       select sum(bytes)/1024/1024      Mbytes_alloc
               , sum(maxbytes)/1024/1024 Mbytes_max
               , tablespace_name
       from sys.dba_temp_files
       group by tablespace_name
     ) b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/


As you can see from my code I'm wondering why it isn't using an "union all" clause to do joint query both dba_free_space and dba_temp_free_space on inline view "a" (however, it's using an "union all" to do joint query both dba_data_files and dba_temp_files on inline view "b")?

Best Regards
Quanwen Zhao

and we said...

Typically there isnt a lot of "interest" or "value" in the free space in temporary tablespaces because we don't treat the segments in the same way.

If *I* allocate a large temp segment for a sort, then when I'm done, we need to clean that segment up, we just need to mark it available for anyone else to use.

Hence, it is not uncommon to see a temp tablespace always "full", it is just full of segments that can be re-used by others. If you really wanted to get a picture of temp *currently* in use, you could fold in v$tempseg_usage

and you rated our response

  (3 ratings)

Reviews

Improving Tom's SQL and Writing my own checking TEMP currently used size

November 15, 2019 - 5:11 am UTC

Reviewer: Quanwen Zhao from China

Hey Connor :-),

I'm very appreciated to get your reply.

Afterwards I repeatedly thought Tom Kyte's that SQL script amongst which the following two statements have been caught my eye.

......
, nvl(Mbytes_free,0) Free_Mbytes
......
where a.tablespace_name (+) = b.tablespace_name
......


In other words these Temp tablespaces' info (both Oracle Default's and Business User') haven't been all checked even though they have been really used some temp extents on Production System. Even I think that it should eliminate from "union all ..." to "group by tablespace_name" on inline view "b" on Tom's SQL - not querying view "sys.dba_temp_files". What do you think of it?

Yes, you're right! I should take a look at view "v$tempseg_usage".

This morning I still spent some time finishing writing my own SQL script checking currently used size on TEMP tablespace. It's as follows,

SET LINESIZE 200
SET PAGESIZE 200

COLUMN tablespace FORMAT a25
COLUMN used_mb    FORMAT 999,999,999,999

WITH tbk AS ( SELECT tablespace
                     , SUM(blocks) AS bn
              FROM v$tempseg_usage
              GROUP BY tablespace
              ORDER BY 1
            ),
     tbs AS ( SELECT t.ts#
                     , t.blocksize
                     , tf.block_size
              FROM ts$ t, v$tempfile tf
              WHERE t.ts# = tf.ts#
              ORDER BY 1
            ),
     tn  AS ( SELECT ts#
                     , name
              FROM v$tablespace
              ORDER BY 1
            )
SELECT tbk.tablespace
       , tbs.block_size
       , SUM(tbk.bn) AS blocks
       , (SUM(tbk.bn) * tbs.block_size)/POWER(2,20) AS used_mb
FROM tbk, tn, tbs
WHERE tbk.tablespace = tn.name
AND tn.ts# = tbs.ts#
GROUP BY tbk.tablespace
         , tbs.block_size
ORDER BY 1         
/


Am I right?

One more thing I've known you don't spend all day on AskTOM from recent bulletin board so how could I contact you directly and quickly? and could you follow me (@quanwen_zhao) on Twitter? Because a couple of days ago I once sent a message to you on Twitter, LinkedIn, Email of "asktom_us@oracle.com" and your blog comment area - https://github.com/oracle/oracle-db-examples/blob/master/sql/analytical-sql-examples/analytics-Deleting-Duplicates.sql and https://livesql.oracle.com/apex/livesql/file/content_C1S07CWFH80P0I40EJ5JBC828.html you haven't introduce related analytic function to delete replicates (I feel like missing out something at the end of that SQL).

Best Regards
Quanwen Zhao

Massively partitioned tables

November 15, 2019 - 10:46 am UTC

Reviewer: lh from finland

Hi

We have encountered problems in DW system with massively partitioned tables and free space monitoring scripts.

These scripts took a quite a lot of time to execute.
Service provider had also combined these free space monitoring scripts to other monitoring scripts and this bundle was tried to be executed repeatedly (several times in an hour). This was a real resource consumer.

New service provider is using DBA_TABLESPACE_USAGE_METRICS.
Results are actually not same, but close enough so it can be utilized to monitor free disk space and if objects fit to grow.

lh

Reply to review "Massively partitioned tables" of lh from finland

November 18, 2019 - 9:33 am UTC

Reviewer: Quanwen Zhao from China

Hello lh :-),

It seems like to have some *bug* for view DBA_TABLESPACE_USAGE_METRICS on different oracle version. You should take a look at David Fitzjarrell's article - https://www.databasejournal.com/features/oracle/trouble-with-oracles-dbatablespaceusagemetrics-for-the-undo-tablespace.html It is also why I don't use it to check Free Space of my Tablespaces.

Best Regards
Quanwen Zhao