Quick Question for the Experts: How to check the size of the Temp tablespace Oracle

[milanbenes]

this one works...

SQL> SELECT A.tablespace_name tablespace, D.mb_total,
2 SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
3 D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
4 FROM v$sort_segment A,
5 (
6 SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
7 FROM v$tablespace B, v$tempfile C
8 WHERE B.ts#= C.ts#
9 GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
10 11 12 GROUP by A.tablespace_name, D.mb_total;



Milan.

[TheFaQQer]

this one works...

SQL> SELECT A.tablespace_name tablespace, D.mb_total,
2 SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
3 D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
4 FROM v$sort_segment A,
5 (
6 SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
7 FROM v$tablespace B, v$tempfile C
8 WHERE B.ts#= C.ts#
9 GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
10 11 12 GROUP by A.tablespace_name, D.mb_total;



Milan.

Doesn't work on a cut and paste

[milanbenes]

you'll need to remove the line numbers sorry to break my own rule and get into details...

here it is without line numbers give it a try...

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;


Milan.

[TheFaQQer]

you'll need to remove the line numbers sorry to break my own rule and get into details...

here it is without line numbers give it a try...

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;


Milan.

Also, it gives completely different answers from the query you gave earlier, even allowing for unit conversion between bytes and MB.

SELECT tablespace_name
,      SUM(bytes_used)    used
,      SUM(bytes_free)    free
FROM V$temp_space_header
GROUP BY tablespace_name;

TABLESPACE_NAME          USED       FREE
------------------ ---------- ----------
TEMP               2340683776 1853620224

SELECT tablespace_name
,      SUM(bytes_used/1024/1024)    used_mb
,      SUM(bytes_free/1024/1024)    free_mb
FROM V$temp_space_header
GROUP BY tablespace_name;

TABLESPACE_NAME       USED_MB    FREE_MB
------------------ ---------- ----------
TEMP                  2232.25    1767.75

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;

TABLESPACE         MB_TOTAL    MB_USED    MB_FREE
----------------- --------- ---------- ----------
TEMP                   4000     20.875   3979.125

Looks like the calculation of used space in one of the queries is wrong...

HTH.

[milanbenes]

bugger, just submitted the results

Milan.

[NickFitz]

bugger, just submitted the results

Blame QA.

[milanbenes]

FAQ,

you said...

If you need to convert your answer to MB, then divide the summation by 1024000, if you want it in GB then divide the MB figure by 1024.


shirley shome misstake, shirley I need to divide it by 1024*1024 to get the MB's value ?

Milan in trying to pick up the pieces mode.

[milanbenes]

there's the answer...

SELECT tablespace_name, SUM(bytes_used)/1024/1024, SUM(bytes_free)/1024/1024 FROM V$temp_space_header GROUP BY tablespace_name;


Milan.

[TheFaQQer]

FAQ,

you said...

If you need to convert your answer to MB, then divide the summation by 1024000, if you want it in GB then divide the MB figure by 1024.


shirley shome misstake, shirley I need to divide it by 1024*1024 to get the MB's value ?

Milan in trying to pick up the pieces mode.

I did - but that's not why the answers are so different!

Go with the first one (FROM v$temp_space_header) if you want to know the current used space and how much is free.

Go with

SELECT file_name, tablespace_name, ROUND(bytes/1024/1024) MB
FROM dba_data_files
ORDER BY file_name;

if you want to know the size of the tablespace.

[milanbenes]

our survey said utt utt

your second one gives a list of tablespace_names but doesn't include the TEMP tablespace which is the one I need



you see folks, and here's another lesson in life, if you want a job done properly do it yourself

give the folks here a chance to shine and demonstrate their abilities and what do they do... faq it up

Milan.