Re: Changing the status of temporary tablespace to permanent

"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3B172DE0.72E03B1D_at_exesolutions.com...
> Dark Lord wrote:
>
> > Hi,
> > Quick basic question, if such a thing exists :) Suppose I change the
> > status of my temporary tablespace ( temp) to permanent. What are the
 likely
> > results of my action ? Thanks
> >
> > --
> > Silent Running
>
> It will fill up with "temp" tables and crash.
>
> Daniel A. Morgan
>

Not quite! There is a misunderstanding here of "temporary tablespaces for user" and "tablespace of temporary contents" concepts.

Temporary tablespaces never get filled with "temp" tables but with "temp" segments. These segments are automatically cleared when not needed (e.g. sort operations completed) and are made available for reuse by other operations/sessions. These segments do not correspond to any tables (unless global temporary tables). So you will not get any crashes because you changed the tablespace status from temp to permanent. As applies to any tablespace, you will get crashes when there is not enough space left in the tablespace for more allocations.

When a tablespace is marked as "temporary" it means that only segments of temporary nature can be created. Only Oracle server itself can create such segments for its own tasks. If you try to create a normal table in such a tablespace you get error "ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace". Therefore tablespaces marked as "temporary" are guaranteed to contain nothing but temporary segments and this improves performance.

On the other hand, tablespaces of type permanent CAN hold temporary segments. (Have you noticed what is the temporary tablespace for SYS and SYSTEM users immediately after database creation? It's the SYSTEM tablespace!). This is so, because it has always been so since early Oracle versions. Oracle 7.3 introduced the feature of marking tablespaces as temporary to help DBAs enforce the rule "dont create objects in tablespaces defined as temporary for users".

So, the result of changing the temporary tablespace from temporary status to permanent will be:
1. you will be able to create permanent segments in this tablespace (e.g. tables, indexes, etc.)
2. you will experience performance degradation

Regards,
Giorgos. Received on Fri Jun 01 2001 - 11:28:26 CDT