Version: Oracle 12cR2 EE
The table contains the following column types: NUMBER, CLOB, NVARCHAR2, TIMESTAMP, VARCHAR2
Table size: 95GB
Table row count: 20M
Table partitioned? No
Indexes on table: there is only one primary key as an index on table.
Depdencies: there is only one trigger "before each row (insert)" as a dependency on table.
Constraints: there are 3 constraints (1 of them is primary key and others are check).
We want to archive this table. However, I could not decide how the archiving should be done for such a large table. It would be a nightmare to move records before a certain date into a new table with CTAS and then delete these imported records on the existing table.
I think the best way would be to make the table interval partitioned by month (I'm open to any ideas if you have better solutions).
A few pre-operation and sequence questions:
- Is there a way to find out beforehand how much archive will generate during the partitioning of a table of this size?
- Do we have a chance to calculate the duration of the process in advance?
- Would it be a good choice to stop other database jobs that will load during the process?
- Is it necessary to make a change on the primary key index for the operation or other dependencies or trigger?
For after the process:
Does it cause heavy load on the database when large partitions are dropped?
For example, is it possible to set up an order like this; can we keep only the last 3 or last 6 months' data on the main table and move the older partitions to the archive table every month? Is it correct to use only the export import method when migrating?
The migrated partition will be dropped from the migration process. Will there be a problem on the basis of archive generate while doing this?