Free Temporary tablespace ?(URGENT)
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Free Temporary tablespace ?(URGENT)

  1. #1
    Join Date
    Jun 2001
    Posts
    103
    Hi all.
    How I can free segments in temporary tablespace?(No shutdown DB way)
    When temporary segments deleted?
    Best regards.

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    You can make temp tablespace offline, then delete. However, you make sure there are no sort operations or other operations involving temp tablespace are taking place in the database at that time.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Jun 2001
    Posts
    103
    Hi Raminder.
    "you make sure there are no sort operations or other operations involving temp tablespace are taking place in the database at that time. "
    How ? with V$sort_usage ? or another way ?




  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Just do
    'Alter tablespace temp_ts offline;'

    if ther are any active operations going on in the tablespace, it will give an error.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  5. #5
    Join Date
    Jun 2001
    Posts
    103
    Thank you Raminder.
    How many temp tablespace needed(or is better) for one DB with about 200 concurrent user?(one,two or more ?).
    If i have more than one temp tablespace , what is distribution of temporary extents allocation?
    Sorry for my english!!
    Best Regards.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you can have one tablespace and several datafiles so the point is not how many tablespace you should have, is how well are they distributed physically, tablespace is something logical, transparent to users, applications. Having more than one may provide more availability just in case one gets corrupted at physical level

    Why would you want to free up the extents in a temporary tablespace?

  7. #7
    Join Date
    Jun 2001
    Posts
    103
    My temporary tablespace (with 2 datafile) is about 4GB , and I want reduce size of this temporary tablespace by deleting one datafile(or reducing size of two datafile) because Hard disk is near full.
    If I have 4 temp tablespace and 2000 user then I can assign each temp tablespace for 500 user.(alter user temporary tablespace temp1 ; ....). Is it correct?
    Sorry for my english!!!
    Best regards.

  8. #8
    Join Date
    Jun 2001
    Posts
    103
    Hi ALL.
    I want know finally :
    1) Why oracle doesn't deallocate automatically(periodically) unused temporary extents in one temporary tablespace ?
    2) for one server that can't shutdown , what is best way for temporary extents deallocations?
    Best Regards.


  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    My temporary tablespace (with 2 datafile) is about 4GB
    Have you created your temporary tablespace with the TEMPFILE option? If yes, I do not see the point of having 2 datafiles. If no, I suggest you create them as TEMPFILEs.


  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Originally posted by mahoori
    Hi ALL.
    I want know finally :
    1) Why oracle doesn't deallocate automatically(periodically) unused temporary extents in one temporary tablespace ?
    2) for one server that can't shutdown , what is best way for temporary extents deallocations?
    Best Regards.

    1) To improve performance, extents are used over and over again, if SMON has to deallocate then that is extra work

    2) I dont think there is a way to force SMON to free the extents, you would have to create a new temp tablespapce assign to users and drop the old one

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