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?
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.
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?
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.