5 Replies Latest reply: Mar 26, 2009 6:05 PM by ski123 RSS

    take tablespace offline

    ski123
      Oracle 10g,
      which tablespace can not be taken offline?
      sysaux tablespace
      system tablespace
      any temp tablespace
      any undo tablespace

      I thought it sysaux and system tablespace can not be offline. but the anwser is system and temp. Why?
      Appreciate any help
      Thanks
      S.
        • 1. Re: take tablespace offline
          Anand...
          Hi..

          >
          I thought it sysaux and system tablespace can not be offline. but the anwser is system and temp. Why?>

          Little confused with your question.

          which tablespace can not be taken offline?

          Ans:-

          1. SYSTEM
          2.UNDO tablespace

          Because they have ACTIVE ROLLBACK SEGMENTS

          And what is the need of taking temporary tablespace offline.


          Anand
          • 2. Re: take tablespace offline
            ski123
            I mean in these four tablespaces, which one can not be taken offline?

            I thought they should be system and sysaux. But the standard answer is system and temp tablespace.

            Now your answer is system and undo tablespace.

            it seems like we all agree system tablespace can not be taken offline.

            But why sysaux tablespace can be taken offline? (I thought it should not)

            why temp tablespace can not be taken offline?

            Thanks
            S.
            • 3. Re: take tablespace offline
              Anand...
              But why sysaux tablespace can be taken offline? (I thought it should not)
              why temp tablespace can not be taken offline?
              >

              Those tablespace which have active rollback segments can't be taken offline, which definitely SYSTEM and undo tablespaces have.
              SYS on 26-MAR-09 at oracle >alter tablespace sysaux offline;
              
              Tablespace altered.
              
              SYS on 26-MAR-09 at oracle >alter tablespace system offline;
              alter tablespace system offline
              *
              ERROR at line 1:
              ORA-01541: system tablespace cannot be brought offline; shut down if necessary
              
              
              SYS on 26-MAR-09 at oracle >alter tablespace sysaux online;
              
              Tablespace altered.
              
              SYS on 26-MAR-09 at oracle >
              SYS on 26-MAR-09 at oracle >
              SYS on 26-MAR-09 at oracle >alter tablespace undotbs1 online;
              
              Tablespace altered.
              
              SYS on 26-MAR-09 at oracle >alter tablespace undotbs1 offline;
              alter tablespace undotbs1 offline
              *
              ERROR at line 1:
              ORA-30042: Cannot offline the undo tablespace
              
              
              SYS on 26-MAR-09 at oracle >alter tablespace temp offline;
              alter tablespace temp offline
              *
              ERROR at line 1:
              ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
              
              
              SYS on 26-MAR-09 at oracle >alter temporary tablespace temp offline;
              alter temporary tablespace temp offline
                    *
              ERROR at line 1:
              ORA-00940: invalid ALTER command
              Temporary tablespace can't be taken offline, its a tempfile that can be taken offline.

              [http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tspaces005.htm#i1006770]

              Anand

              Edited by: Anand... on Mar 27, 2009 12:01 AM
              • 4. Re: take tablespace offline
                user3266490
                hi,
                system,undo, temp.You can take the undo tablespace offline but before you have to switch over to new undo tablespace
                • 5. Re: take tablespace offline
                  ski123
                  Thank you very much.
                  S.