First question:

Why do users need to access archived data?

Some applications only allow access to orders (or whatever) up to X months/years old. If you want view older data you need to request the information from customer services who get the information for you.

If requests for archived data are rare this is the easiest method!

If users often search for archived data so you need to provide this functionality then it's worth asking why you're archiving. Doing so adds complexity. You should assess what benefits you're getting by archiving and if they're worth the costs of enabling access to old data.

Assuming you must archive and provide access to old data, here are some methods you could try:

Re-direct in the application

If a request comes for data older than 5 years, send the query directly to the archive database.

DB link

Create a database link between the current and archive database. Query across this to find data more than five years old. Managing performance for this could be difficult, particularly for complex SQL.

Materialized view over DB link

This is similar to the previous solution. But the current database has a materialized view over the archived data. But this effectively brings the archived information back into the current DB, making performance easier to manage.

But if you're pulling the data back to the current DB it does defeat many of the reasons for archiving in the first place!

When it comes to the archiving itself, I'd look into:

- Date partitioning the tables you're archiving
- Using transportable tablespaces to migrate the data between the DBs

https://oracle-base.com/articles/misc/transportable-tablespaces

If you're not on EE, check you could use data pump export/import. For further reading on moving data between DBs, check:

http://docs.oracle.com/database/121/ADMIN/transport.htm#ADMIN13721