Hello,
I just had a "fun" discussion with a client DBA. We were discussing the best method to archive historical partitions in a data warehouse (large fact tables).

The fact tables are daily partitioned, with a month's worth in a single monthly tablespace. We want to keep 12 months online and archive the 13th month each month.

What are the recommended steps to do this type of archiving/backup? We aren't putting things into another DB - just need the tablespace backed up so we can drop partitions and re-use the space.

Do we just use expdp to export the partitions and the issue a "alter table drop partitoin" and then shrink that month's tablespace (presumably keeping the tablespace online)? Or maybe even drop that tablespace?

Or is it best to use partition exchange to a new tablespace (exchanging all partitions to plain old tables), then use transportable tablespaces as a backup solution? If this is optimal, do we drop the tablespace or keep it around (with no storage)?

Or is there a third option using standard RMAN read-only backups (which we also have for "just in case" purposes)?

Or maybe none of the above?

If you can be detailed on the steps, I'd appreciate it (it's the details we've been "discussing" )
Thanks.