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
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.
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.
Last edited by adewri; 04-16-2003 at 11:03 AM.
Amar "There is a difference between knowing the path and walking the path."
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.)
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
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.
Amar "There is a difference between knowing the path and walking the path."
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 ?
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!
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Reply With Quote
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 .
Bookmarks