4 Replies Latest reply: Mar 23, 2007 8:46 AM by Nicolas.Gasparotto RSS

    Change default tablespace for a schema

    568397
      I have a user created initially with the default tablespace A.

      My Questions:
      I'd like to change his default tablespace A to a defauft tablespace B ?
      What happens to his previous tables created in tablespace A after changing to tablespace B ?

      Apart from exp/imp, what is the other way to do it ?

      Thanks.
        • 1. Re: Change default tablespace for a schema
          DelfinoNunez
          Yes you can change the default table space from a user see the next example:
          NOTE: I did it with the system tablespace because I don't have another one to test.
          SQL> SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='SCOTT';

          USERNAME                       DEFAULT_TABLESPACE
          ------------------------------ ------------------------------
          SCOTT                          USERS
          SQL> alter user scott default tablespace system;

          User altered.

          SQL> SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='SCOTT';

          USERNAME                       DEFAULT_TABLESPACE
          ------------------------------ ------------------------------
          SCOTT                          SYSTEM
          Once you change the tablespace for a user the previous/existing objects stay the same, I suppose that you never specified a tablespace when you created the objects and let to use the default tablespace from the user, the objects stay stored in the previous tablespace(tablespace A) and new objects will be created in the new default tablespace (tablespace B). Like in the example above, the objects for SCOTT stay in the USERS tablespace and any new object will be stored in the SYSTEM tablespace.

          I don't think exp/imp is related with your questions, why do you want to use exp/imp,, to move the objects from one tablespace or for something else?
          • 2. Re: Change default tablespace for a schema
            247514
            Other than exp/imp the tables, you can also use

            Alter table mytable move tablespace B;

            to move tables, need to rebuild indexes after that. Plus that's your opportunity to move index tablespace as well.
            • 3. Re: Change default tablespace for a schema
              Doublethink
              Yeah, when u got a LONG datatype ? please explain possibilities, what's the best way to do this?

              Please reply

              thanks
              • 4. Re: Change default tablespace for a schema
                Nicolas.Gasparotto
                Yeah, when u got a LONG datatype ?
                It shouldn't be the case since many years :-)
                please explain possibilities, what's the best way to do this?
                Exp/imp.

                Nicolas.