Re: Temporary Tablespaces

From: Roy Varghese <rvarghese_at_ibm.net>
Date: 1997/11/28
Message-ID: <347f4f5e.0@news1.ibm.net>

You can change a tablespace between TEMPORARY and PERMANENT online. However remember that temporary tablespace should not be used for storing objects that you may require after a restart ( permanent objects).
Quote from Oracle Magazine, archived Tip Of the Week by Oracle Worldwide Support

PROBLEM DESCRIPTION
ORA-1575 reported in alert.log, usually when system is heavily loaded or when large operations are in progress. Can occassionally be accompanied by process state dumps:
01575, 00000, "timeout waiting for space management resource" // *Cause: failed to acquire necessary resource to do

space management.
// *Action: Retry the operation.
PROBLEM EXPLANATION
ORA-1575 usually signals that Oracle has timed out waiting to aquire the Space Transaction enqueue lock (ST enqueue). In general, the ORA-1575 is just a symptom of a wider performance issue. Some operations will help cause this, however if we have a heavily loaded database, poor system performance can exacerbate this making it occur more frequently.
POSSIBLE CAUSES
There can be multiple reasons why ORA-1575 occurs:

High contention for the space management (ST) enqueue is a legitimate reason for ORA-1575.

In an Parallel Server environment there may be deadlocks or locks lost by the DLM.

Could be caused by a heavy load, especially during very large deletes, parallel create index or parallel loads involving large amounts

     of data.
     Trying to get space while some other process is holding the ST enqueue,
will result in this error before it is possible to see whether
     any space is available. For example, SMON may be coalescing space.
     A lot of on-disk sorting could cause contention for ST enqueue.
SOLVING ORA-1575
     Solution ID             : 2057767.7
     For Problem             : 1009748.7
     Affected Platforms      : Generic: not platform specific
     Affected Products       : Oracle7 Server
     Affected Components     : RDBMS V07.01.03.XX

There are two main steps towards solving ORA-1575: 1.Tune Oracle. In most situations, good database tuning causes the problem to go away. See the reference to PR entry 1010963.6 for

instructions and hints on optimizing Oracle's performance. 2.If the problem persists, you may have to consider upgrading your hardware to cope with the heavy contention for system

resources.
Remember, ORA-1575 is much more of a symptom than a problem itself. The internal timeout for SMON waiting on the ST enqueue was not long enough. In release 7.2.2, SMON has been modified to wait for 5 minutes before it times out (bug 209328). Previously, since this error was not excluded from default error processing, we would wake up every second and retry, resulting in lots of ORA-1575s being recorded in the alert log.
It is also possible to legitimately receive this error if the system is under very heavy load, especially if the CPU utilization is close to 100%, we could very well be timed out. Also, if parallel option is not involved, this is probably a straightforward waiting for resources under heavy load. In this case, instance/latch tuning will help reduce the occurences of this error.
A transaction that receives an ORA-1575 will be rolled back. This works in favour of performance since it prevents the system being deadlocked in this situation.

3) Sure you can add any number of TEMP tablespaces. The tablespace to be used for sorting is specified in the CREATE USER .. TEMPORARY TABLESPACE clause and not as a system-wide default. It can be different for different users.

krishnanand_at_hotmail.com wrote in message <880520370.7185_at_dejanews.com>...
>Hi,
>
>
> A few questions on TEMP tablespaces: ( Oracle 7.3.2.1 )
>
> (1) The DBA_TABLESPACES has a column called CONTENTS - in our default
>
> settings it shows PERMANENT for all the tablespaces. It has 2 optins
>
> PERMANENT & TEMPORARY. Can I change this TYPE from PERMANENT to
>
> TEMPORARY when the Database is UP & RUNNNING. Do I need to take any
>
> SPECIAL steps/precautions. Please HELP.
>
>
> (2) In the ALERT_SID.LOG , I keep seeing the message
>
> " ORA-01575: timeout waiting for space management resource "
>
> What should I do ?
>
>
> (3) Can I add another TEMP tablespace - sounds STUPID but is it
>possible?
>
> In other words can I make Oracle to do sorting in some tablespace
>
> other than (TEMP - created at installation ) ?
>
>
> Please reply by EMail also.
>
> Thanx.
>
>
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Fri Nov 28 1997 - 00:00:00 CST