dropping LMT TEMP tablespace taking long time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: dropping LMT TEMP tablespace taking long time

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Angry

    Hi all,

    Oracle 8.1.7.3.0 on Win2000.

    My TEMP tablespace is created as LMT, AUTOEXTEND ON.

    Its size somehow reached to 30GB.

    So, I decided to drop and recreate it.

    I have issued the

    drop tablespace TEMP including contents;

    But it is taking long time to complete the command.

    I heared that if it is a LMT, it drops real quick.

    It also does not show anything in fet$ for TEMP tablespace.

    What should I do?

    Thanks in Adv.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    FYI LMT is locally managed so obviously you cant see anything in data dictionary such as fet$

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    The session that attempted to drop true temporary tablespace with active users waits for uncommited transactions that use the tablespace to finish. Other transactions that wish to use the tablespace will wait to the "drop tablespace" command and then fail.

    Originally posted by samdba
    What should I do?
    You should create another tempfile first:

    alter tablespace TEMP add tempfile '...' ...;

    Then put the old tempfile offline:

    alter database tempfile '...' offline;

    This makes all temporary objects (tables, LOBs, sorts) unavailable. Active users may lose their work so it's good to check if there are any (v$sort_usage, v$temporary_lobs) before offlining the tempfile.
    New temporary segments are created in the new tempfile.

    Then you can try to drop the old tempfile:

    alter database tempfile '...' drop;

    If there are any uncommited transactions in the TEMP tablespace, "ORA-25152: TEMPFILE cannot be dropped at this time" occurs. Wait and try again. After dropping the tempfile you can remove it from disk.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi,

    After I disconnected/killed connected sessions, DROP tablespace TEMP was successful.

    Thanks for your suggestions.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

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