We are on Oracle 9.2 and Sun 5.8. We have a database that has static data that changes once every two months or so... So, what my developers do every two months is load new data (sometimes old data is updated) into TEST database. Once they are done updating old data and adding new data, they tell me to update the production database with all the new changes they just made to TEST. How is that possible without bringing the production database down? How can TEST be replicated to the production database?
There are a few options available to you to move data. The biggest problem is that you want to leave the data in the production instance available to you until you have the new data moved to production. If I had to tackle this issue, I'd look at using the following method:
- Move the data from test to production with the Transportable Tablespace option. If you want to keep the same table name(s), then use different table owners. You can use other methods to move the data from test to production as well like export/import or copying the data across a database link.
- With one SQL statement, issue something like the following:
DELETE FROM prod_user.tableX; INSERT INTO prod_user.tableX SELECT * FROM test_user.tableX; COMMIT;When you delete the data from prod_user.tableX, no one will be able to see that you've deleted the data until you COMMIT. Before you commit, you insert the new data. Once the COMMIT is complete, the data in the table is refreshed. Depending on the data volume, you might need a very large rollback segment to handle this sort of transaction. If that is the case, then set one up and make sure that your transaction uses that rollback segment for this operation.
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.