TEMP Tablespace
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: TEMP Tablespace

  1. #1
    Join Date
    Oct 2003
    Posts
    312

    TEMP Tablespace

    let's say we have users online using the database and I drop the TEMP tablespaces and recreate it.

    what happened if I dropped the TEMP tablespace while the user online???


    thanks

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    they can't sort
    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
    Oct 2003
    Posts
    312
    ON 9I,

    you can't drop the default temporary tablespace so you have to create another one and issue:

    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE NEW_TEMP;


    ONCE YOU DO THAT, all of the users should be default to the new temp and now you are free to drop the old one.

    IS IT TRUE, Jeff???????

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I wouldn't know the answer to that. I wouldn't ever drop my temporary tablespace.
    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
    Oct 2003
    Posts
    312
    what's the consequnces of droppping the TEMP, Jeff.

    I have tested it and it worked fine; however, I want to be sure that I can get some opinion in here specially someone like you.

    thanks

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by learning_bee
    what's the consequnces of droppping the TEMP, Jeff.
    by this u will suerly not corrupt the DB, but the Q is why?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by learning_bee
    what's the consequnces of droppping the TEMP, Jeff.
    Your users can't sort
    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."

  8. #8
    You can create a new temp tablespace, change the temp tablespace to this, and then try to drop the first, but you'll have to be pacient, it will not happen immediatly unless no operations in course.
    I think I've done this once...
    ovidius over!

  9. #9
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi,

    I was experienced that if u don't check first in dba_segments
    that the temporary tablespace is clean from temporary segments and you drop the tablespace when it contains temporary segments ,your database suppose to crash casuse to SMON crash.

    Be careful!!

    Nir

  10. #10
    Join Date
    Apr 2003
    Posts
    353
    Is it..?

    I took one rollbacksegment with large active transaction,
    to offline, still oracle allowed me ( I think it wrote the
    active "rollback information" to somewhereelse).

    What is wrong in expecting oracle to act same like Rollback
    on Temporary also?

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