This discussion is archived
3 Replies Latest reply: Apr 28, 2008 7:20 AM by 629346 RSS

Move tablespace to another storage tier

user629987 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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