Home > Oracle COREDB > Script – Temporary tablespace usage.

Script – Temporary tablespace usage.

July 23, 2013

We can use below script to find temp tablespace usage in all versions.

– Listing of temp tablespace usage.

SELECT 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 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;

However from 11g onwards, we can use below command directly.

SELECT * FROM dba_temp_free_space;
Advertisements
Categories: Oracle COREDB
%d bloggers like this: