Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Chris Saxon

Thanks for the question, Arslan Afzal.

Asked: June 10, 2024 - 7:06 am UTC

Last updated: June 10, 2024 - 12:27 pm UTC

Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Viewed 1000+ times

You Asked

Hi,

I will be starting my development for an Archiving project. Aim is to move all the old data from Production database and make it lighter. It consist of 2 parts. One is to move all data except the latest 6 months into an Archiving database in Oracle from PROD DB. Other step is to remove the all data except the latest 6 months from Production DB.

I am doing some brainstorming on the step2 which is to delete data from Production DB. Now the size of DB is around 4 TB in PROD. Around 3 TB of the data has to be deleted from Production DB. Now the scenario is, that while bringing data from PROD to Arch DB, we removed FKey constraints and some indexes too. This cannot be done in PROD. Secondly, the application will be live. We are in discussion for a window where the app can be shut down for some time so we can work on it.

Based on above situation, where we have heavy tables having 5-6 years of data and we need to delete almost 85-90% of data from these tables (around 130). What options do I have? Since its production, our aim is to delete in minimum possible time and minimizing the possibility of effecting the PROD application.

and Chris said...

Here are a few options:

- Create-table-as-select to build a table only storing the rows you want to keep, then switch the tables over
- A filtered table move that "deletes" the data - this assumes it has no child tables
- Partition the table and drop the old partition(s) - Will make ongoing archiving easier; requires the partitioning option

Exactly which you use depends on your constraints and which trade-offs are most important to you. I dived into the details of these in an Office Hours session a couple of years ago:



Or read this blog post for more discussion on deleting lots of data fast https://blogs.oracle.com/sql/post/how-to-delete-millions-of-rows-fast-with-sql

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.