Re: TEMPORARY tablespace problem -- newbie problems

Oooops.

If your temporary tablespace is properly declared as 'TEMPORARY' tablespace, then you may have problems.

Briefly, users *can* do sorts and so forth, swapping out to permanent tablespace. When this happens, each person's sorts acquire extents as required, and at the end of the sort, those extents are dropped. It's horribly intensive, knackers performance, potentially causes fragmentation left, right and centre, and if you genuinely allow users to swap down to permanent tablespace, you deserve to be shot!!! (Just joking)

When users are doing sorts that swap out to temporary tablespace, the *first* sort acquires extents, and all subsequent sorts (even by different users) *re-use* those extents, assuming the first sort has finished. No extents are ever dropped (basically, the whole tablespace become one giant temporary segment) until you do a shutdown: at which the *entire* tablespace is emptied, and the whole process starts over.

In short, if the tablespace is not properly "TEMPORARY" tablespace, you will have excessive extent allocation, and that could well be the source of your problem.

To check: do a SELECT * from DBA_TABLESPACES, and check the "contents" column. It will either report 'TEMPORARY" or "PERMANENT". If what you *call* temporary tablespace is actually listed as temporary, then issue the command "ALTER TABLESPACE x TEMPORARY" to turn it into properly temporary tablespace.

One other tip: SELECT * FROM DBA_USERS where TEMPORARY TABLESPACE='SYSTEM' will give you a list of every User who *hasn't* been assigned proper temporary tablespace: they are merrily causing their sorts to walk all over your data dictionary (which of course is permanent tablespace of limited size). Yuk. Anyone listed in that report should be dealt with as follows: ALTER USER x TEMPORARY TABLESPACE y

Hope that helps
HJR "Rob Diaz" <rdiaz_at_ebudgets.com> wrote in message news:rww15.10246$pd4.229690_at_news-east.usenetserver.com...
> Hi. I am new to Oracle and am having a problem with the Temporary
> tablespace. It seems that Oracle is never reusing any of the space it
> allocates for a process. The reason I say this is that we keep running
 out
> of space on the tablespace (if we allow unlimited extents, it fills the
> disk). Some of the queries we run involve long, complicated joins on
> millions of records, which should (and does) allocate a large temporary
> space.
>
> Anyway, the users keep getting locked up due to the lack of temporary
 space.
> Is there some setting I have to use to tell oracle to free up the space
 when
> it finishes the query? I have tried using the "alter tablespace temporary
> coalesce" command and it does not seem to help.
>
> My assumption was that the temporary tablespace was, in fact, temporary
 and
> that when a query or process completed it would release any objects that
> Oracle created, thereby freeing the space for use by another process. Is
> this wrong?
>
> Any help would be appreciated, as I am being hammered by a large client on
> this.
>
> Thanks.
>
> Rob
>
>
Received on Wed Jun 14 2000 - 00:00:00 CDT