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

[bobhope]

DOH!

Been thinking about 6502 vs Z80 today... slipping back into my 8-bit ways

Depends on whether you were a posh kid (BBC Model B) or chav scum (ZX Spectrum)

[milanbenes]

and the moral of the story is folks, as ever in life, if you want a job done properly do it yourself,

here's the answer...

SQL> SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name;

Milan.

[Pondlife]

You see, THIS is why they end up working lunchtimes.

[milanbenes]

alright alright less of it

that SQL is all well and god but how do I get the results in gb's instead of bytes ?

TIA

Milan.

[Pondlife]

http://www.dbspecialists.com/present...emp_space.html

Code is in the linky

HTH

[milanbenes]

very nice, thanks Dad.

Milan.

[TheFaQQer]

Hmmmm.

You said

anyone happen to know the sql for oracle 10 to check the size of the temp tablespace ? and more importantly to return a figure in mb's or gb's which is understandable for dunces like me and not these damn extents and blocks and bytes.

TIA

Milan.

We said

SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_temp_files
ORDER BY file_name;

which tells you the size of the temporary tablespace in megabytes.

Your code of

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

gives the size of total bytes used, and the total bytes that are free - the sum of which answers your original question "to check the size of the temp tablespace ?" If you meant "how many blocks are free and used in the temporary tablespace?" then maybe you'd have got a different answer to the one I gave yesterday.

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.


You're welcome.

[milanbenes]

Mr FAQqer,

many thanks for your reply, your initial code didn't work on a cut and paste.

In the high flying world in which I work for blue chip clients I need immiediate answers with the correct information and not misleading best guesses

try to learn from this experience I am sure your approach and career will benefit

all the best

Milan.

[TheFaQQer]

Mr FAQqer,

many thanks for your reply, your initial code didn't work on a cut and paste.

In the high flying world in which I work for blue chip clients I need immiediate answers with the correct information and not misleading best guesses

try to learn from this experience I am sure your approach and career will benefit

all the best

Milan.

Works for me, with no problems - since it came from my SQL session, I expected it to, though.

Maybe your blue chip clients need to employ someone who knows this stuff, rather than someone who can't even find it on Google, or who can spell immediate correctly.

[milanbenes]

nah you see, blue chip clients need doers and not people who get hung up and slowed down by small details

there you see, second lesson of the day

I should start charging for this advice

Milan.