Re: TEMPORARY tablespace -- what it really means

Thanks for the correction--I was going to mentione something about that as well.

The only other difference that hasn't been mentioned is how Oracle will manage the TEMPORARY tablespace extents. Extents in a TEMPORARY TS will not be de-allocated when they're finished being used.

Normally, when a sort needs some temporary segments, they're created in that user's temporary tablespace and extents are added until the sort finishes. When it's finished, the segment is dropped and it's up to the SMON process to coalesce the free space left behind.

When the TS is type TEMPORARY, the segment is tagged as "no longer in use" but is not de-allocated. Another sort can re-use that segment and there won't be (as much) need for the dynamic space allocation since the segment is already of reasonable size.

Dan
Oracle7 Certified Professional DBA

mjain_at_my-dejanews.com writes:

>Hi Mohan,

> You cannot create any object in a tablespace which
>is of type TEMPORARY in Oracle7 version 7.3.x.

> Oracle7 will return an error ORA-02195 if you try to
>create a table or anyother object in the tablespace whose
>contents are defined TEMPORARY.

> Oracle7 handles this type of tablespaces for optimizing
>the sorts user during index builds, analyzing tables and
>order by statements.

> This a correction which I just wanted to post and keep
>you posted.

>HTH,
>Manoj Jain
>Certified Oracle DBA

>==================Start of Example=======

>SQL> conn system/manager_at_orcl
>Connected.

>SQL> create tablespace temptest temporary datafile 'E:
>\DB\DATABASE\temptsd1.dat' size 1M;

>Tablespace created.

>SQL> select * from dba_tablespaces;

>TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
>MAX_EXTENTS PCT_INCREASE
>------------------------------ -------------- ----------- -----------
>----------- ------------
>STATUS CONTENTS
>--------- ---------
>SYSTEM 10240 10240 1
>121 50
>ONLINE PERMANENT

>USER_DATA 10240 10240 1
>121 50
>ONLINE PERMANENT

>ROLLBACK_DATA 10240 10240 1
>121 50
>ONLINE PERMANENT

>TEMPORARY_DATA 10240 10240 1
>121 50
>ONLINE PERMANENT

>BKS_TEST 10240 10240 1
>121 50
>ONLINE PERMANENT

>TEMPTEST 10240 10240 1
>121 50
>ONLINE TEMPORARY

>6 rows selected.

>SQL> create table testtempts_table ( name varchar(25)) tablespace temptest;
>create table testtempts_table ( name varchar(25)) tablespace temptest
>*
>ERROR at line 1:
>ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace

>SQL>

>============End of Example=================

>In article <6kv114$ql3$1_at_nnrp1.dejanews.com>,
> cmohan_at_iname.com wrote:
>>
>> If you had done any kind of programming where you had to write a sort
>routine,
>> you will relate to this much easier. Any SQL that requries a SORT operation
>> uses the TEMPORARY tablespace to store intermediate results of the sort.
>Some
>> SQL / constructs that may use the TEMPORARY tablespace are ORDER BY, CREATE
>> INDEX, UNION etc.
>> In fact, there is nothing that will stop you from creating a table in
>> TEMPORARY tablespace, as well as a non TEMPORARY tablespace may be assigned
>as
>> a staging area for SORTs, though any DBA would shoot that idea down. The
>> classic abuse of SYSTEM tablespace is to let it be the TEMPORARY tablespace
>> for a user.
>>
>> Good luck,
>> CM
>>
>> In article <6kt96a$ji7$2_at_gte1.gte.net>,
>> johnvue_at_gte.net wrote:
>> >
>> > I'm trying to understand more thoroughly the effect of designating a
>> > tablespace as TEMPORARY. Besides the surface level knowledge that you
>> > can't put permanent objects in TEMPORARY tablespaces what else does
>> > Oracle internally do for you?
>> >
>> > I came across this statement:
>> >
>> > "Oracle 7.3 introduced a new temporary tablespace type especially
>> > designed to tune Oracle sorting. It achieves this by eliminating
>> > serialization of space management."
>> >
>> > What does paragraph mean?
>> >
>>
>> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
>> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>>

>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Tue Jun 02 1998 - 08:01:02 CDT