If I partition the source by day range Is there a way to move the previous days partition from the source table to a compressed partition on the remote over database link.
If archived table was on the source instance, Id just do a alter move compress. I could run an insert delete but interested to see whats possible with moving an uncompressed partitions to a compressed partition over a link.
The simplest way to 'move' the data is to use Transportable Tablespaces.
See the DBA doc
1. Use a separate tablespace for each partition of your warehouse table
2. Take the tablespace offline when you are ready to move it
3. 'Transport' the tablespace to the other server - rename it to a 'working' name
4. Do your 'alter move compress' on the other server if you need to and compress the data to the actual tablespace and name
In order to compress the data you will HAVE to move it.
I know if a table or tablespace was in default compress mode and inserts are done direct insert then the rows are compressed.
My understanding is data is imported direct append with impdp so interested to see will it compress on the other side naturally due to the direct append. I'll post back here after I try it.