confused about temp tablespace in local extent
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: confused about temp tablespace in local extent

  1. #1
    Join Date
    Sep 2002
    Posts
    411

    confused about temp tablespace in local extent

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    BTW its not a good idea to have temporary tablespace with uniform sized extents. Let it be system managed (autoallocate) LMT.
    Last edited by adewri; 04-16-2003 at 10:42 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.)
    "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

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  8. #8
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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 ?
    Reddy,Sam

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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!
    "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

  10. #10
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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 .
    Reddy,Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width