Data Archival Strategy

Our db is growing pretty fast and now we need to purge / archive some of the data to another schema, we'll only keep 6 months of data in the current schema/table.

Here is the scenario:

2 schema: scott (source) & scott_arc (target)

tables in scott (source):
emp (regular table - criteria is date)
emp_p (partitioned table - criteria is date)

Task:
we have to purge/move/archive old data (only 6 months will stay in source, rest will move to target/arc) from these two tables and create identical same tables in SCOTT_ARC and move data to *_arc schema, need to create indexes too, similar to source.

- for regular tables, we can do select/insert, on date basis
- for partitioned tables, we can try exchange partition, as the current partitions are some like these:

EMP_20090104 <YYYYMMDD>

and this is not consistent too, means we don't have partitions for all the days in a month, this is the current status is:

MCDH_200909 2 (for 09 have only 2 days of data, means 2 partitions)
MCDH_200910 4 (for Oct have 4 days or data or 4 partitions)
MCDH_200911 16 and so on ...
...