3 Replies Latest reply: Apr 28, 2008 9:20 AM by 629346 RSS

    Move tablespace to another storage tier

    user629987
      Hi,

      With the ALTER TABLE...MOVE PARTITION you can transfer a partition to another tablespace on the same or different storage tier.

      How about if you want to transfer the whole tablespace (containing many partitions) to another storage tier? What is the SQL statement to do this?

      Thanks.
        • 1. Re: Move tablespace to another storage tier
          629346
          Hello,

          Moving the tablespace and the associated datafiles to another storage tier alters the physical structure of the database and is not recommended. Using ALTER TABLE ... MOVE PARTITION to move each of the partitions to another tablespace individually is the preferred method for storage tier migration.

          If moving the entire tablespace is absolutely necessary, it can be accomplished by taking the tablespace offline, moving the datafiles through the OS, updating the tablespace using ALTER TABLESPACE ... RENAME DATAFILE, and performing a full database backup. Refer to the "Managing Datafiles and Tempfiles" chapter of the "Database Administrator's Guide" for more details on how to do this.


          Adam
          • 2. Re: Move tablespace to another storage tier
            user629987
            Thanks for your reply, Adam!

            How about dropping a tablespace which contains 1 or more data partitions?
            Is it as easy as DROP TABLESPACE xyz INCLUDING CONTENTS AND DATAFILES?
            Or are there other things to consider?
            • 3. Re: Move tablespace to another storage tier
              629346
              Hi,

              There are some restrictions on when DROP TABLESPACE can be used if there are table partitions residing on that tablespace. For example, this won't work if there are range or hash partitions. It would be better to explicitly drop the individual table partitions or the whole table first, and then drop the tablespace.


              Adam