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