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

I'd like to figure out the best way to archive the data that is no needed anymore, in order to impro开发者_开发百科ve 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?

---

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.

---

The best way to archive old data in ORACLE database is:

1. Define an archive and retention policy based on date or size.
2. Export archivable data to an external table (tablespace) based on a defined policy.
3. Compress the external table and store it in a cheaper storage medium.
4. Delete the archived data from your active database using SQL DELETE.
5. Then to clean up the space execute the below commands: alter table T_XYZ enable row movement; alter table T_XYZ shrink space;
6. If you still want to free up some disk space back to the OS (As Oracle would have now reserved the total space that it was previously using), then you may have to resize the datafile itself: SQL> alter database datafile '/home/oracle/database/../XYZ.dbf' resize 1m;

For more details, please refer: http://stage10.oaug.org/file/sroaug080229081203621527.pdf

---

I would export the data to a comma-delimited file so it can be exported into almost any database. So if you change versions of Oracle or go to something else years later you can restore it without much concern.

Use the spool file feature of SQL*Plus to do this: http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/#savingoutput