Quote Originally Posted by PAVB
Here is what we do...

1- Export affected partition (exp/compress on the fly)
2- Move dump file to tape
3- Drop affected partition
4- Coalesce tablespace

Notes:

#1 For some tables where we know reload is likely, we do not drop the partition, we just truncate it.

#2 Since all our indexes are partitioned, no need to rebuild.

#3 Since we do not have a tablespace per each partition, no need to deal with empty tablespace.

By the way, we move selected Data Warehouse data to tape after seven -as in 7- years.
On step 1, I didn't know expdp could compress on the fly ... did I miss that feature? Maybe you're using plain exp?

#3 - I'm interested, how are your tablespaces set up then? Do you have one tablespace for all partitions? Seems like you wouldn't have the flexibility of setting things to read-only and using RMAN (or maybe you're not using RMAN?). I just had another thread about this topic and Mr. Aldridge recommended a tablespace per partition ... thoughts on that?

Oh, and I forgot to mention, this is 10gR2 on linux ... (not that it matters)