Forum Stats

  • 3,780,520 Users
  • 2,254,406 Discussions
  • 7,879,373 Comments

Discussions

Move undo and temporary tablespace to new path

708725
708725 Member Posts: 32
edited Jun 25, 2009 10:35AM in General Database Discussions
Hi All,

I want to move my undo and temporary tablespace to new path because of space issue. I am using Oracle 10g release 2 and working on production server can't take shutdown without prior permission.

Please tell me the steps to do so...

thanks
Api

Answers

  • OrionNet
    OrionNet Member Posts: 4,542
    Api,

    You can simply create new temporary tablespace in new location and set it as default temporarty tablespace. And similar you can do that with undo tablespace
    sqlplus '/as sysdba'
    
    
    SQL>CREATE TEMPORARY TABLESPACE temp2   TEMPFILE '/new_location/temp2_01.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 28 MAXSIZE unlimited;
    
    SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2
    
    #drop old temp tablespace
    SQL>DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
    
    
    #undo tablespace
    
    SQL>create undo tablespace undotbs2 datafile '/new_location/newundo02.dbf' size 2000m;
    
    #Set new tablespace as undo tablespace
    SQL> alter system set undo_tablespace= undotbs2 ;
    
    #Drop old undotbs tablespace
    SQL> drop tablespace undotbs including contents;
    Regards
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    I want to move my undo and temporary tablespace to new path because of space issue.
    I am using Oracle 10g release 2 and working on production server can't take shutdown without prior permission.
    Create new UNDO & TEMP tablespaces, switch to new TS, & then drop old ones.
  • Surachart Opun
    Surachart Opun Member Posts: 1,662
    About create/change/drop

    UNDO:

    SQL>show parameter undo

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

    SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE '+DATA_NEWPATH' SIZE 100M AUTOEXTEND ON;

    http://www.oracle-base.com/articles/9i/AutomaticUndoManagement.php

    SQL> alter system set undo_tablespace=undotbs2;

    *** after that can drop UNDOTBS1

    TEMP:
    SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA_NEWPATH' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml

    SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;


    *** after that can drop old temp tablespace -> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES
  • 708725
    708725 Member Posts: 32
    Ya thanks a lot for the answer but I have one more query...

    Mine is the production server and I can't shutdown so may I do all this operation when database is open?

    How do I know that users are accessing the old Undo tablespace or temporary tablespace or not, because after checking that no one is using the old ones, I can drop it. So when can I drop old undo and temp tablespaces?
  • oradba11
    oradba11 Member Posts: 595 Blue Ribbon
    If you are trying to drop these tablespace ..which is being use by some transactions ..oracle will not allow to do that..
    Read some documents on google ..thay will clear you doubts also..

    enjoy
This discussion has been closed.