confused about temp tablespace in local extent

[mike2000]

I am a bit confused about the temp tablespace in local extent.

below is the script of my temp tablespace in local extent:

CREATE TEMPORARY TABLESPACE TEMP_01
TEMPFILE 'D:\ORADATA\HUOXYD01\TEMP_01.DBF' SIZE 61440K AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K

/


ok what happened if the user need more than 60M??? can we set the autoextend on for TEMP tablespace??? It seem like we can't b/c I tried the below scripts and it still set autoextend of after I run the script below:


ALTER DATABASE TEMPFILE 'D:\ORADATA\HUOXYD01\TEMP_01.DBF' AUTOEXTEND ON NEXT 10240K MAXSIZE 2048000K
/


So if I can't set the autoextend on, then I have to allocate a certain space for temp tablespaces??? which mean that I have to use a lot of spaces for TEMP tablespaces????

Please correct me if I am wrong and give me some advises

[marist89]

SQL> create temporary tablespace t1
2 tempfile 'e:\oracle\oradata\nt817\t101.dbf'
3 size 10M autoextend on next 10M maxsize 40M
4 extent management local uniform size 1M;

Tablespace created.

[adewri]

BTW its not a good idea to have temporary tablespace with uniform sized extents. Let it be system managed (autoallocate) LMT.

[marist89]

Originally posted by adewri
BTW its not a good idea to have temporary tablespace with extent management local uniform size. Let it be system managed LMT.

Why?

[adewri]

Because if i have a Temporary LMT with uniform size say 10M. Then even for a small sort operation requiring say 64k of space an extent of 10M will be allocated.

Where as in system managed LMT the first few extents allocated would of 64k, then 1M, then 8M and so on. So for a small sort of 64k only one extent of 64k will be allocated instead on 10M as in case of uniform sized Temporary LMT. And of course for larger sorts the extent size will grow accordingly from 64k to 1M to 8M and so on.

[DaPi]

Hi Amar,

The recommendation I'm working to is to have the uniform extent size equal to the SORT_AREA_SIZE - so your example will not arise. (I think that makes sense.)

[adewri]

Hi Dapi,

But in 9i where i'm using workarea_size_policy=auto and setting a value of pga_aggregate_target. Then the sort_area_size wont come into picture as the amount of space to be allocated would be controlled by ORACLE automatically depending upon the value of pga_aggregate_target and the amount of memory required for sorting.

Cheers...

PS: Advices are welcome as i'm still testing on these grounds.

[sreddy]

Originally posted by adewri
Where as in system managed LMT the first few extents allocated would of 64k, then 1M, then 8M and so on. So for a small sort of 64k only one extent of 64k will be allocated instead on 10M as in case of uniform sized Temporary LMT. And of course for larger sorts the extent size will grow accordingly from 64k to 1M to 8M and so on.

Did you test this behaviour or you are talking on theoritical/documentation grounds ?

[DaPi]

Originally posted by adewri
But in 9i ....

Ah! Some of us are still carrying a 7.3.4 CD in our pockets as a good luck talisman - just in case 8i doesn't deliver!

[sreddy]

Originally posted by DaPi
Ah! Some of us are still carrying a 7.3.4 CD

I would say 7.1 not even stable 7.3.4. Just saw somebody here today morning... who lost last night sleep with some issues .