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:

  1. Is there a way to find out beforehand how much archive will generate during the partitioning of a table of this size?
  2. Do we have a chance to calculate the duration of the process in advance?
  3. Would it be a good choice to stop other database jobs that will load during the process?
  4. Is it necessary to make a change on the primary key index for the operation or other dependencies or trigger?

For after the process:

  1. Does it cause heavy load on the database when large partitions are dropped?

  2. 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?

  3. The migrated partition will be dropped from the migration process. Will there be a problem on the basis of archive generate while doing this?

Thank you.

Best Regards.

  • Please define "archive". I can only assume you mean NOT InDatabase Archiving. (Ps - 100GB table is small) Commented Feb 13, 2022 at 21:25
  • @MichaelKutz Yes, I don't mean InDatabase archiving. Before I do this I want to confirm the accuracy of my method. Yes we also have a 1TB table and it's not huge, but even on a 50GB table the physical standby can be affected a lot.
    – jrdba123
    Commented Feb 13, 2022 at 23:06

1 Answer 1


Interval partitioning should be the propriate method for this. If you can take care that most of the application also takes this into account (meaning: their queries should ALWAYS contain the partition criteria in the WHERE clause), then it will speed up things.


  1. not really. I would create a clone database on another (similar) system and test it there - this should answer most of your questions.
  2. see 1, look at v$session_longops. Not that this predictions are 100% accurate, but they can give you an idea.
  3. If possible, yes. Since 12.2 you can partition a table directly online, so actually you still can use it - but it does not speed up things, naturally
  4. That should not be necessary

post operation:

  1. without foreign keys and triggers, no. If you turn them off, it wont.
  2. With interval partitioning on month level this should not be difficult. The classic method is to move that data to a DWH and access is via DB link if necessary. Don't use export (I hope you mean expdp), see https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/evolve-nopartition-table.html
  3. no, if you use alter table drop partition update global indexes.
  • Hi, thank you for update. I partitioned (interval by mothly) it 2 weeks ago and successfully completed.
    – jrdba123
    Commented Apr 11, 2022 at 21:12
  • Sorry to have come too late :-)
    – Tuxinose
    Commented Apr 11, 2022 at 21:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.