Search This Blog

Total Pageviews

Saturday, 19 May 2012

Oracle Temp Tablespace info

Oracle Temp Tablespace info
Oracle Temp Tablespace

Oracle Temp File usage Info  ...





set lines 300
COLUMN mb_total        HEADING 'Total|MB'        FORMAT 99,999
COLUMN mb_used         HEADING 'Used|MB'         FORMAT 99,999
COLUMN mb_free         HEADING 'Free|MB'         FORMAT 99,999
column FILE_NAME       Heading 'File|Name'       FORMAT a30
column tablespace_name Heading 'Tablespace|Name' FORMAT a10
column TABLESPACE      Heading 'Tablespace|Name' FORMAT a10
column MB              Heading 'Allocated|MB'    FORMAT 99,999
SELECT dbatf.FILE_NAME, dbatf.BYTES/1024/1024 MB, A.tablespace_name TABLESPACE, D.mb_total,SUM (A.used_blocks * D.block_size)/1024/1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) /1024/1024 mb_free FROM dba_temp_files dbatf, v$sort_segment A,
(SELECT B.name, C.block_size,  SUM (C.bytes)/1024/ 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size   ) D
WHERE A.tablespace_name = D.name
GROUP BY A.tablespace_name,
D.mb_total, dbatf.FILE_NAME, dbatf.BYTES/1024/1024
/



File                           Allocated Tablespace   Total    Used    Free
Name                                  MB Name            MB      MB      MB
------------------------------ --------- ---------- ------- ------- -------
+DATA/orcl/temp01.dbf                 29 TEMP            29       0      29

1 comment:

Anuj Singh said...

http://anuj-singh.blogspot.co.uk/2012/09/oracle-temp-tablespace-usage-info.html

Oracle DBA

anuj blog Archive