Deleting UNDOTBS - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 25 of 25

Thread: Deleting UNDOTBS

  1. #21
    Join Date
    Jul 2005
    Posts
    10
    Hi Ken,

    I know restarting and checking the DB is not necessary and those steps should be be skipped by a DBA. But looking at the Oracle DB behaviour and DB administration being not my area of expertise I would like to ensure that after doing some major modification the DB opens properly.

    Secondly, oracle 9i doesnot delete the physical datafiles in windows.


    Well lets get back to the problem. Can anyone tell me - how to drop an undo
    tablespace if there is some active rollback segments in it
    Last edited by TomNJerry; 07-04-2005 at 01:10 AM.

  2. #22
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    select * from v$parameter where name like 'undo%'
    Please post results.

    It'll be retained till undo_retention time has passed and it becomes expired. Your undo_retention is probably a huge number of seconds.

    Here's some good info:
    DROP TABLESPACE undo02 INCLUDING CONTENTS AND DATAFILES;
    The Undo tablespace to be dropped cannot be in use.
    The clause INCLUDING CONTENTS AND DATAFILES causes the contents (segments) and datafiles at the operating system level to be deleted.
    If it is active, you must switch to a new Undo tablespace and drop the old one only after all current transactions are complete.
    The following query will display any active transactions. The PENDING OFFLINE status indicates that the Undo segment within the Undo tablespace has active transactions. There are no active transactions when the query returns no rows.

    SELECT a.name, b.status
    FROM v$rollname a, v$rollstat b
    WHERE a.name IN (SELECT segment_name
    FROM dba_segments
    WHERE tablespace_name = 'UNDO01')
    AND a.usn = b.usn;

    NAME STATUS
    ------------------ ----------------
    _SYSSMU2$ PENDING OFFLINE
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #23
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Oh, and "DROP TABLESPACE undo02 INCLUDING CONTENTS AND DATAFILES;" doesn't delete the datafiles in MS-Windows?

    Humm...

    Here's a good resource:

    Managing Undo Data
    Last edited by KenEwald; 07-05-2005 at 05:14 PM.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #24
    Join Date
    Jul 2005
    Posts
    10
    Ken, I know that one needs to wait for the pending transections before trying to drop a tablespace.
    Also learnt that I should set the undo_retention parameter to very low and wait until those
    transections are over and undo_retention is ignored id the transection is going to take more time than the
    specified.

    After some workaround I found that there are some active segments dated to 12th Dec 2003 in that undo tablespace.
    So I set undo_retention to 5 secs, hanged on for a few minutes and restared the Db. But no outcome! Following
    are the information that you wanted:

    select NAME, TYPE, VALUE, ISDEFAULT,ISSES_MODIFIABLE, ISSYS_MODIFIABLE
    ISMODIFIED,ISADJUSTED,DESCRIPTION from v$parameter where name like 'undo%'


    NAME TYPE VALUE ISDEFAULT ISSES_MODIFIABLE ISMODIFIED ISADJUSTED
    ----------------------------------------------------------------------------------------------------------
    undo_management 2 AUTO FALSE FALSE FALSE FALSE
    undo_tablespace 2 UNDOTBS FALSE FALSE IMMEDIATE FALSE
    undo_suppress_errors 1 FALSE TRUE TRUE IMMEDIATE FALSE
    undo_retention 3 5 FALSE FALSE IMMEDIATE FALSE


    SQL> SELECT a.name, b.status
    2 FROM v$rollname a, v$rollstat b
    3 WHERE a.name IN (SELECT segment_name
    4 FROM dba_segments
    5 WHERE tablespace_name = 'UNDOTBS')
    6 AND a.usn = b.usn;

    NAME STATUS
    ------------------------------ ----------
    _SYSSMU10$ ONLINE
    _SYSSMU6$ ONLINE
    _SYSSMU7$ ONLINE
    _SYSSMU8$ ONLINE
    _SYSSMU9$ ONLINE



    Following are the active segments:


    select SEGMENT_NAME, TABLESPACE_NAME, STATUS, COMMIT_WTIME from DBA_UNDO_EXTENTS where status!='EXPIRED'

    SEGMENT_NAME TABLESPACE_NAME STATUS COMMIT_WTIME
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:39
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:42
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:42
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:45
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:45
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:49
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:49
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:52
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:54
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:54
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:58
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:58
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:00
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:00
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:03
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:03
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:33
    _SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:33



    Following are the no of bytes that is needed:

    1* select SEGMENT_NAME, TABLESPACE_NAME, STATUS, COMMIT_WTIME from DBA_UNDO_EXTENTS where status!=
    SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) As "Bytes"
    2 FROM (SELECT value As UR
    3 FROM v$parameter
    4 WHERE name = 'undo_retention'),
    5 (SELECT (SUM(undoblks)/SUM(((end_time -
    6 begin_time) * 86400))) As UPS
    7 FROM v$undostat),
    8 (SELECT value As DBS
    9 FROM v$parameter
    10 WHERE name = 'db_block_size');

    Bytes
    ----------
    98304


    HOW DO I GET RIGD OF THESE SEGMENTS?

  5. #25
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Your active undo tablespace is UNDOTBS.

    You can't drop the active undo tablespace.

    From your previous post:
    SQL> DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;
    DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES
    *
    ERROR at line 1:
    ORA-01548: active rollback segment '_SYSSMU6$' found, terminate dropping
    tablespace
    Did you create another one, then switch to it? Maybe we need to start over. Something is obviously not right here. If it's got active rollback segments in it after the undo retention time, it's active. You're looking for no segments or pending offline segments - the query is in the link I posted last.

    By the way, be careful of setting undo_retention too low. Set it longer than your longest transaction or the max size you want your undo tablespace to grow. Yours is fine if your transactions don't last more than 5 seconds.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

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