There is no "best" practice because this is largely determined by your company's policies relating to older data. Can the old data be simply deleted? Must it be archived for a certain number of years? If it is archived, will it be used on a semi-regular basis or almost never? Once you start to answer these questions, you can determine which method is best for you.
To give you an idea of the different methods, we'll go through a few scenarios.
If the data can simply be deleted, the DELETE statements against appropriate tables can help.
If the data needs to be accessed frequently, many people move the old data to an archive table. Then production can access the new data, while keeping the older data in the database. Many people also set up a data warehouse (if that is appropriate) for their older information. Also, many people don't need all of the details of their older data so they aggregate it.
If the data needs to be accessed only in rare circumstances, then it is probably best to keep the data out of the database. Export the data. Unplug a tablespace. Or whatever works to remove the data from the database, but keep it in a format that can easily be added to the database.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in March 2003