Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Tuesday, January 27, 2009

Query to check temp tablespace usage

This query is courtesy www.dbspecialists.com from their excellent article here.

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;

2 comments:

Anonymous said...

how to find sessions using much of temp table space. i get temp tablespace full alert and wanna check which sessions are consuming much of the temp tablespace,

regards
Muneer
muneer.dba@gmail.com

Deep said...

Hi i have an question i need to findout last login time of a database user the problem is the oracle audit was disabled i enabled the audit but it show the information only about the current transaction and not the past is there any table of view in oracle which stores the information about the user log in without audit enabled