What's the best way to perform data archiving on an Oracle database?

oracle archiving
Rulas picture Rulas · Nov 11, 2009 · Viewed 25.5k times · Source

I'd like to figure out the best way to archive the data that is no needed anymore, in order to improve the application performance and also to save disk space. In your experience what is the best way to implement this, what kind of tools can I use? It is better to develop an specific in house application for that purpose?

Answer

Sam picture Sam · Jan 25, 2016

One way to manage archiving:

  1. Partition your tables on date range, e.g. monthly partitions
  2. As partitions become obsolete, e.g. after 36 months, those partitions can now be moved to your data warehouse, which could be another database of just text files depending upon your access needs.
  3. After moving, the obsolete partitions can be removed from your primary database, so always maintaining just (e.g.) 36 months of current data.
  4. All this can be automated using a mix of SQL/Shell scripts.

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

We have an application running locally where we're experiencing the following error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I've tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try …

Read More