Deleting UNDOTBS
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Deleting UNDOTBS

  1. #1
    Join Date
    Jul 2005
    Posts
    10

    Deleting UNDOTBS

    HI DBAs,

    I am a J2EE developer and have a very basic question about UNDO tablespace. I have a 9i database running in NO ARCHIVELOG mode and the UNDOTBS01.DBF file has eaten up more than 6GB of my disk space. There are three other datafiles each of 1 GB added to the UNDO Tablespace. The problem is- I am running out of space.

    So can I make the UNDOTBS01.DBF (I think this one is created by default at the first transection) offline and than delete it safely?

    I tried this with a development db in same envirnment. It gave me message that I need to enable Media Recovery first. I enabled Archivelog mode and then the file was allowed to make offline. But the db got shutdown and didnot open ! It gave me the message that the UNDOTBS01.dbf needs to be recovered. And then I found that to do that one needs to connect to OEM which I dont have. Under these circumstances, I think it is not at all safe to attempt deleting the UNDOTBS file.

    Then I would like to ask you - can I shrink the size ? The file is 85 % used.

    **I saw an existing question like this; but the discussion itself brings some questions about those participants**

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    create a new undo

    then modify the default undo to the new one

    alter system set undo_tablespace = 'UNDOTBS02';

    drop the old one

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    conn sys/password as sysdba

    Identify the current undo tablespace:

    set long 1000
    select dbms_metadata.get_ddl('TABLESPACE', 'UNDOTBS1') from dual;

    DBMS_METADATA.GET_DDL('TABLESP
    --------------------------------------------------------------------------------

    CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
    'C:\ORACLE\ORADATA\DB10G\UNDOTBS01.DBF' SIZE 26214400
    AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
    BLOCKSIZE 8192
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE



    1 row selected.

    Use this information to build a new undo tablespace, remember to change both the tablespace name and the datafile name. Also, don't forget to define a smaller size or there isn't much point doing this:

    CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE
    'C:\ORACLE\ORADATA\DB10G\UNDOTBS02.DBF' SIZE 26214400
    AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
    BLOCKSIZE 8192
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

    Tell the system your switching to another undo tablespace:

    ALTER SYSTEM SET undo_tablespace=UNDOTBS2;

    Drop the original undo tablespace:

    DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

    Especially on Windows, you might find the datafiles associated with the undo tablespace remain on disk. If so, restart the instance:

    SHUTDOWN IMMEDIATE
    STARTUP

    Then manually remove the datafiles associated with the initial undo tablespace. These should be listed by the query at the top.

    You should now have a smaller undo tablespace.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Sorry Pando. By the time I'd written the example you'd beat me to the punch. I guess I should requery before posting

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Jul 2005
    Posts
    10
    Oh !!, Great guys. Such a detailed and prompted reply . Lots of Thanks to Tim and Pando & Company.

    I am testing this in my dev instance.
    Last edited by TomNJerry; 07-01-2005 at 04:58 AM.

  6. #6
    Join Date
    Jul 2005
    Posts
    10
    Hi,

    The procedure that you people mentioned works fine in my dev server. But when I did the same in the actual server it gave me the following error while dropping the old tablespace:

    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


    I tried after restarting the DB also; didnot work .

    Then I did this:

    SQL> drop rollback segment "_SYSSMU6$";
    drop rollback segment "_SYSSMU6$"
    *
    ERROR at line 1:
    ORA-30025: DROP segment '_SYSSMU6$' (in undo tablespace) not allowed


    What to do ?

  7. #7
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    alter tablespace x offline;

    Check the tablespace has a status of "offline pending", which means that it won't offline it until the active transactions that are using the tablespace have finished processing. One you see the tablespace is offline, then you can drop it.
    Assistance is Futile...

  8. #8
    Join Date
    Jul 2005
    Posts
    10
    Hi waitecj,

    I had already tried that. I made the sablespace offline and then tried dropping it. But it didnot work.

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    did you actually create a new one and assign that to be the new undo tablespace for the database?

  10. #10
    Join Date
    Jan 2001
    Posts
    3,131
    Can't you just shrink the existing one after the re-boot?

    SQL> ALTER DATABASE
    2 DATAFILE 'D:\ORADATA\Hanky\SYSTEM\UNDOTBS01.DBF' RESIZE 1500M;

    Database altered.

    SQL>


    Another thing to look into is WHY did it grow this big?
    I remember when this place was cool.

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