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

[Bagpuss]

Sally Ann is the £700 a day SQLguru, come on SA fix it for him

[Andy2]

bunch of geeks
real contractors can't do basic sql

[richard-af]

Data Files:
SELECT file_name "File",
tablespace_name "Tablespace",
bytes "Bytes",
bytes/1024 "KBytes",
(bytes/1024)/1024 "MBytes",
((bytes/1024)/1024)/1024 "GBytes"
FROM dba_DATA_files
ORDER BY tablespace_name,file_name;

Temp Files:
SELECT file_name "File",
tablespace_name "Tablespace",
bytes "Bytes",
bytes/1024 "KBytes",
(bytes/1024)/1024 "MBytes",
((bytes/1024)/1024)/1024 "GBytes"
FROM dba_TEMP_files
ORDER BY tablespace_name,file_name;

An aside: get a DBA.

[milanbenes]

good work Richard, worked first time.

in the .Net area we don't use dba's or the oracle teams infact we stay well away from them because .Net uses databases in a way which goes against the way they have been taught to use databases

Milan.

[richard-af]

good work Richard, worked first time.

in the .Net area we don't use dba's or the oracle teams infact we stay well away from them because .Net uses databases in a way which goes against the way they have been taught to use databases

Milan.

You have at least one Oracle DB you're interested in. Find out NOW who looks after it. Buy 'em a tea and tell them you need their invaluable help, etc. We DBAs are, usually, a lovely, tea-drinking lot, and respond well to freebies and OTT praise.

[TheFaQQer]

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.

Cut and paste error - if I'd copied the answer I gave yesterday (the one where the post began "Doh"), it would have been right.

[TheFaQQer]

Data Files:
SELECT file_name "File",
tablespace_name "Tablespace",
bytes "Bytes",
bytes/1024 "KBytes",
(bytes/1024)/1024 "MBytes",
((bytes/1024)/1024)/1024 "GBytes"
FROM dba_DATA_files
ORDER BY tablespace_name,file_name;

Temp Files:
SELECT file_name "File",
tablespace_name "Tablespace",
bytes "Bytes",
bytes/1024 "KBytes",
(bytes/1024)/1024 "MBytes",
((bytes/1024)/1024)/1024 "GBytes"
FROM dba_TEMP_files
ORDER BY tablespace_name,file_name;

An aside: get a DBA.

good work Richard, worked first time.

Assuming that you only have one datafile for the temporary tablespace, it does. If you have more than one datafile, then you would need to sum them and group by tablespace name, and not select the file name.

[richard-af]

Assuming that you only have one datafile for the temporary tablespace, it does. If you have more than one datafile, then you would need to sum them and group by tablespace name, and not select the file name.

I assumed that it'd all go into a spreadsheet.

[NickFitz]

in the .Net area we don't use dba's or the oracle teams infact we stay well away from them because .Net uses databases in a way which goes against the way they have been taught to use databases

Milan.

.Net does most things in a way that goes against the way that an expert in the field would do it.

ASP.Net is a classic case in point. The only sensible way to use it is to throw most of it away and roll your own. It's not very surprising that MS managed to get the DB side of things completely wrong too.

[Muttley08]

You have at least one Oracle DB you're interested in. Find out NOW who looks after it. Buy 'em a tea and tell them you need their invaluable help, etc. We DBAs are, usually, a lovely, tea-drinking lot, and respond well to freebies and OTT praise.

Richard being a DBA ... explains the dry sense of humour, slightly nontulant air, and amount of time he has to post on here...all makes sense now