Could you please help me to check how much freespace

[mm8294]

is available in the TEMP tablespace? I used to check DBA_FREE_SPACE, but in Oracle 8i, I could not find the TEMP tablespace in this view.

 

[lfoata]

Yoiu do not have to have a TEMP tablespace. The temporary tablespace could be any tablespace. A good practice is to use TEMP, but it does not seem to be the case for your instance.
You can find out what the temprorary tablespace is by querying the all_users table and look at the temporary tablespace used. Too bad I.T. is not cash business

Luc Foata
Unix sysadmin, Oracle DBA

 

[jcote]

First question you should answer is, do you have a tablespace named 'TEMP'. Check in the dba_tablespaces view.
If you do have one named 'TEMP', and you do not see it in the dba_free_space, it might be because not a single byte of free space is available in it, that is that the extents in that tablespace occupies all the space in the tablespace.

If your TEMP tablespace is defined as "temporary", the extent allocated for session use are not de-allocated at the end of the transaction using it. That might be why you don't see any free space in the tablespace, even though the instance might be working properly.

 

[carp]

This should work, regardless of which tablespace your temporary segments are in:

SELECT t.tablespace_name, t.available, u.used, t.available-u.used left
FROM
(SELECT tablespace_name, sum(bytes) available
FROM dba_data_files
GROUP by tablespace_name) t,
(SELECT tablespace_name, bytes used
FROM dba_segments WHERE segment_type = 'TEMPORARY') u,
WHERE t.tablespace_name = u.tablespace_name;

 

[kindus]

Few things
1) To check which tablespace is temporary, query dba_tablespaces, not in other dictionary tables as those may be give partial result which may be undesirable.

select tablespace_name from dba_tablespaces where contents='TEMPORARY';

2) Carp's script above will work except in case your tablespace is using locally managed where datafile information of locally managed tablespaces will not be available in dba_data_files, rather it will be available in v$tempfile.

hope it helps

 

[carp]

Yipes! Completely forgot about LMTs! Good catch!
I'd thought about putting the check for contents="TEMPORARY", but I usually don't put in this proviso. If somebody is generating temporary segments where they aren't supposed to be (e.g., SYSTEM), I want to know about it.

 

[mm8294]

Thank you very much for everybody's time and effort. Carp's script works very well.
I did not reply early because I was out of town for a couple of days.

Thank you very much again.

 

[jcote]

I think there is a little bug in carp's SQL, but I might be wrong. The SQL takes for granted that all the tablespaces where there is a temporary segment in it is used exclusively for temporary segments. In every well set up instance, that should be the case. But If only one user is badly defined (with a tablespace other than the temp for his temporary purposes), then, the "available" column doesn't represent the real value of the available space for the temp segments, since it does not account for the index or table segments already defined in the tablespace used by that "badly defined" user.

Please correct me if I am wrong...

 

[carp]

You are correct that the available amount may be misleading, since tables, indexes, etc in a PERMANENT tablespace will also be taking up space.
If you look at Kindus' post and my response, you will see that we have addressed this situation as well as how to eliminate it. However, I DO make the assumption that a competent DBA will know which tablespaces are TEMPORARY and any PERMANENT tablespaces that show up will be immediately recognized.

Also, finding a temporary segment within a PERMANENT tablespace is a coincidence anyway, since temporary segments in a PERMANENT tablespace are dropped when sessions finish using them. Consequently, they will only show up if you run this query while some other session is doing a sort with a temp. seg in a PERMANENT tablespace.

So to your point - no, it's not a bug. While it takes for granted it will only return information on TEMPORARY tablespace, the omission of constraining to contents="TEMPORARY" is intentional. Not only does it run faster (since you don't have to join on DBA_TABLESPACES), but it will also flag any users that are ill-defined.

 

[carp]

Correction - it won't flag the users; however, it WILL alert the DBA to the fact that at least one user is ill-defined.