Re: Changing TEMP tablespace from type PERMANENT to type TEMPORARY

There is absolutely, positively, 100% no danger in turning permanent tablespace into temporary. And if you are really concerned about it, create a new tablespace with the temporary attribute, then change each User's temporary tablespace attribute. The change then won't take place until the next time they connect.

The difference between permanent and temporary tablespace (apart from the fact that temporary tablespace can't contain permanent objects) is this:

User A starts a select statement that requires a sort. PGA runs out of memory... so we swap User A's PGA down to the temporary tablespace. If that tablespace is of the permanent type, then User A starts acquiring extents as her PGA keeps filling up and swapping out. When her sort is finished we drop those extents, as being no longer needed.

User B now starts Report 2. Again swaps to disk are required: since all prior extents have just been dropped, User B must acquire brand new ones. (Sit back and make a coffee as UET$ in the data dictionary is hit to death). Oh -and then when User B's report is finished, we drop his extents, and User C must therefore acquire a whole new bunch of extents..... and so on ad infinitum.

Heaps of potential contention on the data dictionary tables governing extent allocation. Drops and creates all over the place, so potential fragmentation issues. Users waiting for extent allocations to finish before their reports can actually be produced. Not pretty.

Proper temporary tablespace is completely different....

User A runs report. PGA runs out of space. Swaps down to disk, extents are allocated. Report finishes. Extents are NOT dropped, merely marked for re-use. User B runs report. PGA runs out of space, swaps down into pre-existing extents. When report finishes, said extents are marked for re-use.

Net result: entire TEMP tablespace eventually likely to be filled with a heap of extents, all of which are re-useable (hence, 100% use of TEMP tablespace as recorded in DBA_FREE_SPACE is not the slightest problem). User A still has to wait for extent allocations. All other Users do not have to wait for extent allocations, and reports are probably produced rather faster than before. No extent dropping, hence zero possibility of fragmentation.

There's also the not inconsequential issue that inserts into permanent tablespace require redo generation, and inserts into temporary tablespace do not.

Net result: no-one in their right minds should ever be swapping down to permanent tablespace. And if you have 8i, no-one in their right minds would do anything other than have locally managed tablespace for the temporary tablespace (makes iniital extent allocation even faster).

Regards
HJR <dmnwork_at_my-deja.com> wrote in message news:95ras0$4j9$1_at_nnrp1.deja.com...
> In article <gmZf6.32121$l57.1407636_at_news000.worldonline.dk>,
> "Gollum" <gollum_nospam_at_worldonline.dk> wrote:
> > No need to bring the database down - just ALTER TABLESPACE TEMP
 TEMPORARY;
> > HTH,
> > Gollum
>
>
> Are you 100% positive this won't effect transactions in progress?
> (peoples' sorts I suppose, but it's an active production database).
>
> I'd read somewhere that the difference between tablespaces of type
> PERMANENT and type TEMPORARY is that a tablespace of type TEMPORARY
> creates one large temporary segment - saving on overhead that would
> otherwise come from building up and tearing down individual temporary
> segments. Is this what's actually going on behind the scenes?
>
>
> Thanks.
>
> - Dana
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Wed Feb 07 2001 - 06:08:59 CST