Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    70

    Unanswered: Dropping UNDO Tablespace

    I wanted to drop the existing Undo tablespace (undoTbs1), so I did the following:

    1) I created a new undo tablespace 'undoTbs2'
    2) Alter System SET Undo_Tablespace=undoTbs2

    Then I tried the following :

    DROP TABLESPACE undoTbs1
    this did not work
    ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

    I tried this also
    DROP TABLESPACE undoTbs2 INCLUDING CONTENTS
    this also did not work same error.

    Can anyone help me in accomplishing this.

    Thanks in advance
    qAnand

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    qAnand,

    The problem is that there is someone that still has a transaction with active undo segments. You could....

    1) wait until all pending transactions using the old undo tablespace have commited or rolled back... you're the DBA(!)... surely we don't have to wait on the user, or

    2) kill the offending session(!), or

    3) restart your instance.

    Afterwards, you should be able to drop the old undo tablespace.

    JoeB

  3. #3
    Join Date
    Mar 2004
    Posts
    21
    You should be able to 'offline' the tablespace in question (Alter tablespace undoTbs1 offline). This will allow any open transactions to complete at which point the tablespace will be taken offline (this may take some time depending on what type of transactions you have going). You should then be able to drop it. I had this problem a while back with rollback segments on an older version database and it took me a week to get all the old rollback offline so they could be dropped...just a lot of long running transactions and a very active database.

  4. #4
    Join Date
    Oct 2003
    Posts
    3

    Dropping UNDO tablespace

    Check your alert log file and if you getting "Undo Tablespace X moved to Pending Switch-Out state." error message than you need to find the pending transactions which blocking you to drop UNDO tablespace. Use query below to find the pending offline transaction.

    SELECT
    a.usn,
    a.name,
    b.status,
    c.tablespace_name,
    d.addr,
    e.sid,
    e.serial#,
    e.username,
    e.program,
    e.machine,
    e.osuser
    FROM
    v$rollname a,
    v$rollstat b,
    dba_rollback_segs c,
    v$transaction d,
    v$session e
    WHERE
    a.usn=b.usn AND
    a.name=c.segment_name AND
    a.usn=d.xidusn AND
    d.addr=e.taddr AND
    b.status='PENDING OFFLINE';

    You can either kill the transaction or call the user to commit his/her transaction. After this you should be able to drop UNDO tablespace.

    Check metalink note 341372.1 for more info.

Posting Permissions

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