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

[milanbenes]

I give up with google when looking for oracle help, there is so much useless nonsense and there and so little useful facts

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.

[NickFitz]

I give up with google when looking for oracle help, there is so much useless nonsense and there and so little useful facts

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.

bytes/1024 = mb's

HTH.

[milanbenes]

yeah wonderful, now I just need the rest of the sql

Milan.

[King Cnvt]

I think it was better all round when you stuck to tape swapping.

HTH

[sasguru]

Milan, you imbecile:

1) Post in the correct forum, TECHNICAL
2) SQL is for dummies, even you must be able to do it.

HTH

[TheFaQQer]

List tablespaces, their files, allocated space, free space, and next free extent:

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;

List datafiles, tablespace names, and size in MB

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

HTH.

[MrRobin]

bytes/1024 = mb's

HTH.

kb's!!!!! Sheesh!

[TheFaQQer]

List tablespaces, their files, allocated space, free space, and next free extent:

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;

List datafiles, tablespace names, and size in MB

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

HTH.

Doh - you need to look in DBA_TEMP_FILES for the temporary tablespace, not in dba_data_files.

So

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

should do it for you. (or UNION the two together to get all the data files)

[NickFitz]

kb's!!!!! Sheesh!

DOH!

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

[milanbenes]

jeeeeez lads,

no wonder you're all on noddy rates if you can't give a quick straight answer to a simple question like that

funny how noone knows how to find out something so simple as the size of the TEMP tablespace in Oracle

when I get the answer I'll add it to my cv :-)

Milan,