Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to show TEMP tablespace usage?

Quanwen ZhaoNov 12 2019 — edited Nov 17 2019

Hello my guys and fellow DBAs ,

So far I know there have some related views of TEMP tablesapce existed on Oracle Database such as v$temp_extent_pool, v$temp_space_header, DBA_TEMP_FREE_SPACE, dba_temp_files, v$tempfile, v$sort_usage, v$tempseg_usage (9i onwards), v$sort_segment.

The following are my query steps,

SET LINESIZE 200

SET PAGESIZE 200

COLUMN table_name FORMAT a25

SELECT table_name

FROM dictionary

WHERE table_name LIKE '%V$TEMP%'

ORDER BY 1

/

TABLE_NAME

-------------------------

GV$TEMPFILE

GV$TEMPORARY_LOBS

GV$TEMPSEG_USAGE

GV$TEMPSTAT

GV$TEMP_CACHE_TRANSFER

GV$TEMP_EXTENT_MAP

GV$TEMP_EXTENT_POOL

GV$TEMP_PING

GV$TEMP_SPACE_HEADER

V$TEMPFILE

V$TEMPORARY_LOBS

V$TEMPSEG_USAGE

V$TEMPSTAT

V$TEMP_CACHE_TRANSFER

V$TEMP_EXTENT_MAP

V$TEMP_EXTENT_POOL

V$TEMP_PING

V$TEMP_SPACE_HEADER

18 rows selected.

SET LINESIZE 200

SET PAGESIZE 200

COLUMN table_name FORMAT a25

SELECT table_name

FROM dictionary

WHERE table_name LIKE '%DBA_TEMP%'

ORDER BY 1

/

TABLE_NAME

-------------------------

DBA_TEMPLATE_REFGROUPS

DBA_TEMPLATE_TARGETS

DBA_TEMP_FILES

DBA_TEMP_FREE_SPACE

SET LINESIZE 200

SET PAGESIZE 200

COLUMN table_name FORMAT a25

SELECT table_name

FROM dictionary

WHERE table_name LIKE '%V$SORT%'

ORDER BY 1

/

TABLE_NAME

-------------------------

GV$SORT_SEGMENT

GV$SORT_USAGE

V$SORT_SEGMENT

V$SORT_USAGE

Which one view or more than one (joint Multi-views query) can identify/show my TEMP tablespace usage?

One more thing I found this fantastic SQL script specially showing Oracle Database Free space report from AskTom website. Based on Tom's those two SQL scripts now I just formatted and aligned them (by the way converted Kb into Mb).

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

/

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','*',' ')

         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

/

Why isn't dba_free_space union all view DBA_TEMP_FREE_SPACE on inline view "a" (as you can see on inline view "b" it is dba_data_files union all dba_temp_files)?

Very appreciated if any help, thanks in advance!

Best Regards

Quanwen Zhao

This post has been answered by dvohra21 on Nov 12 2019
Jump to Answer
Comments
Post Details
Added on Nov 12 2019
8 comments
35,579 views