Temp teablespace space usage

All posts relating to Oracle database administration.

Moderator: Tim...

Temp teablespace space usage

Postby Raaj A » Fri Sep 27, 2013 6:51 am

Hi Tim,

I am executing two queries to check space of temp tablespace. Both are returning two different result of same temp tablespace. Please help clarify this.
Query 1
1) SELECT tablespace_name,bytes_used/1024/1024,bytes_free/1024/1024 FROM v$temp_space_header;

Query 2
2) 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;

Result 1: total mb: 3072 used mb:1934 free mb: 1138

Result 2: total mb: 3072 used mb:76 free mb: 2996

Raaj A
Posts: 49
Joined: Wed Mar 07, 2012 7:08 am

Re: Temp teablespace space usage

Postby Tim... » Fri Sep 27, 2013 2:56 pm


Is this 11.2? If so, you can do this:

http://www.oracle-base.com/articles/11g ... -11gr1.php

Then you know it is correct. :)

If it is not 11.2, I would do this:

http://www.oracle-base.com/dba/script.p ... _space.sql

You are using v$sort_segment, like it is the only think in the temp files. Remember, temp is also used to global temporary tables, which might be materialised automatically by the optimizer when using the WITH clause...


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Site Admin
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 1 guest