Deleting UNDOTBS - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 25

Thread: Deleting UNDOTBS

  1. #11
    Join Date
    Jul 2005
    Posts
    10
    I checked the spfile and init.ora.
    The undo_management parameter is set to AUTO.
    In spfile the undo_tablespace was pointing to my new tablespace while in init.ora it was pointing to my old one. I changed it, restarted the db.

    Checked the status in dba_tablespaces. The old one is marked offline there.
    But still I get the same error if I try to drop it. It doesnot allow me to drop that active rollback sagment also!


    Aaha, - Mr davey23uk - Didn't you notice the **.....** note in my first post ?
    Please stay away..
    Last edited by TomNJerry; 07-02-2005 at 03:34 AM.

  2. #12
    Join Date
    Apr 2003
    Posts
    353
    Try this.
    alter tablespace undotbs1 online;
    drop tablespace undotbs1 including CONTENTS AND DATAFILES;

  3. #13
    Join Date
    Jul 2005
    Posts
    10
    Hi engiri,
    I had already tried that. Din't work

  4. #14
    Join Date
    Apr 2003
    Posts
    353
    The below details might help us.
    show parameter undo
    select * from dba_tablespaces where tablespace_name='UNDOTBS1';
    select * from dba_data_files where tablespace_name='UNDOTBS1';
    SELECT STATUS,RELATIVE_FNO,TABLESPACE_NAME,COUNT(*) FROM DBA_ROLLBACK_SEGS GROUP BY STATUS,RELATIVE_FNO,TABLESPACE_NAME;

  5. #15
    Join Date
    Apr 2003
    Posts
    353
    Make sure the undo tablespace is set to undotbs2
    alter system set undo_retention=50 scope=both;
    --make sure the undotbs1 is online.
    Do restart the database two times.(Normal)
    try drop now.
    If not post the result for the commands in the previous post.
    Increase the retention later.

  6. #16
    Join Date
    Jul 2005
    Posts
    10
    I already tried setting undo_retention=1 and restarting the DB. But doesnot work.



    SQL> show parameter undo

    NAME TYPE VALUE
    ------------------------------------ ----------- -------------
    undo_management string AUTO
    undo_retention integer 900
    undo_suppress_errors boolean FALSE
    undo_tablespace string UNDOTBS1




    SQL> select * from dba_tablespaces where tablespace_name='UNDOTBS';

    TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
    ------------------------------ ---------- -------------- ----------- -----------
    MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MAN
    ----------- ------------ ---------- --------- --------- --------- ----------
    ALLOCATIO PLU SEGMEN
    --------- --- ------
    UNDOTBS 4096 65536 1
    2147483645 65536 ONLINE UNDO LOGGING LOCAL
    SYSTEM NO MANUAL



    SQL> select * from dba_data_files where tablespace_name='UNDOTBS';

    FILE_NAME
    --------------------------------------------------------------------------------
    FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
    ---------- ------------------------------ ---------- ---------- ---------
    RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
    ------------ --- ---------- ---------- ------------ ---------- -----------
    E:\ORACLE\ORADATA\MYDB\UNDOTBS01.DBF
    2 UNDOTBS 6485442560 1583360 AVAILABLE
    2 YES 8388608000 2048000 1280 6485377024 1583344

    D:\UNDOTBS\UNDOTBS02.DBF
    13 UNDOTBS 1048576000 256000 AVAILABLE
    13 NO 0 0 0 1048510464 255984

    FILE_NAME
    --------------------------------------------------------------------------------
    FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
    ---------- ------------------------------ ---------- ---------- ---------
    RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
    ------------ --- ---------- ---------- ------------ ---------- -----------

    D:\UNDOTBS\UNDOTBS03.DBF
    14 UNDOTBS 1048576000 256000 AVAILABLE
    14 NO 0 0 0 1048510464 255984

    D:\UNDOTBS\UNDOTBS04.DBF
    15 UNDOTBS 1048576000 256000 AVAILABLE

    FILE_NAME
    --------------------------------------------------------------------------------
    FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
    ---------- ------------------------------ ---------- ---------- ---------
    RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
    ------------ --- ---------- ---------- ------------ ---------- -----------
    15 NO 0 0 0 1048510464 255984




    SQL> SELECT STATUS,RELATIVE_FNO,TABLESPACE_NAME,COUNT(*) FROM DBA_ROLLBACK_SEGS GROUP BY STATUS,RELATIVE_FNO,TABLESPACE_NAME;

    STATUS RELATIVE_FNO TABLESPACE_NAME COUNT(*)
    ---------------- ------------ ------------------------------ ----------
    OFFLINE 2 UNDOTBS 4
    ONLINE 1 SYSTEM 1
    ONLINE 16 UNDOTBS1 10
    PARTLY AVAILABLE 2 UNDOTBS 1




    If you notice in the last query one rollback segment is partly available, which
    is not allowing to drop that tablespace. So I tried this:


    SQL> alter session set undo_suppress_errors=TRUE;
    Session altered.

    SQL> alter rollback segment "_SYSSMU6$" offline;
    Rollback segment altered.

    Then tried dropping the tablespace; But in vain.
    Last edited by TomNJerry; 07-02-2005 at 10:23 AM.

  7. #17
    Join Date
    Apr 2003
    Posts
    353
    What is your undo tablespace now?
    You have not changed your undo tablespace as suggested before by one Pando and others.
    You are still using UNDOTBS1 as your undo tablespace.

    You have not executed the queries correctly.
    I requested information for UNDOTBS1 but you have executed for UNDOTBS

    Make the UNDOTBS2 or any other newly created tablespace as
    current undo tablespace and make your SPFILE also recognise by using scope=both.

    Revert with details.

  8. #18
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    fine, ill stay away. I know the answer to your problem, but you are obviously so intelligient and knowledgable you can figure it out yourself

  9. #19
    Join Date
    Jul 2005
    Posts
    10
    No no. Its a confusion. See - My original undo tablespace was UNDOTBS.
    And the one i created newly is - UNDOTBS1. Since I need to drop UNDOTBS (not UNDOTBS1) , I executed your queries for UNDOTBS. If you have doubt, following is what I did and documented as per Tim and Pando's advice:



    Step 1:
    CREATE UNDO
    TABLESPACE "UNDOTBS1"
    DATAFILE 'D:\UNDOTBS\UNDOTBS05.DBF' SIZE 200M
    REUSE AUTOEXTEND
    ON NEXT 5120K MAXSIZE 16383M

    Check in the Enterprise manager consol if the tablespace is created.

    Step 2: Fire a COMMIT query to ensure that all transections are commited.

    Step 3: Tell the system to use the new tablespace
    ALTER SYSTEM SET undo_tablespace=UNDOTBS1;

    Step 4: Restart the DB. Just for safty.

    Step 5: Drop the table old space
    DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

    Step 6: Restart the DB. Check if it opens properly. Fire some DDL query.

    Step 7: Delete the old datafiles from the filesystem. If not sure about the filename check from the view dba_data_files.

    Step 8: Restart the DB.

    I got stucked in step 5
    Last edited by TomNJerry; 07-02-2005 at 08:52 AM.

  10. #20
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    I completely disagree with steps 4, 6 and 8. There is no reason to restart the db when switching undo tablespaces.

    If the old one doesn't free up (drop), just give it some time. Don't panic. You may need to wait for it to age out the data (undo_retention seconds).

    Step 7 - I thought 9i deleted the os file too? humm...

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