Q
Problem solve Get help with specific problems with your technologies, process and projects.

Loading updated schema from test to production

I have a Schema 'ABC' in the test environment, where we regularly load data, test it and move that data to production database with the same schema name. I've been using the EXP/IMP command to load the new data. Is there any better option?

I have a dilemma ... I have a Schema 'ABC' in the test environment, where we regularly load data, test it and move...

that data to production database with the same schema name. When the ABC schema in the test database has new data (this new data can be new tables, new rows into existing tables, updated records in the old tables, new views, etc.), it's my job now to load this new schema with all its objects into the production environment with preferably no downtime.

Right now, what I am doing is bringing the database down, deleting the old schema with the user using the cascade option, then creating a new user and using EXP/IMP command to load the new data. Is there any better option? Obviously, IMP is not going to work when you want to update the existing records, and that is the reason why I am deleting the user and recreating the same user.

You didn't specify which version of Oracle you are using. Hopefully, you are using at least Oracle 8i. Beginning in Oracle 8i, you can use the Transportable Tablespace feature to quickly move data from one system to another. You will probably find the Oracle documentation on this subject very helpful.

Basically, what you do is make the tablespaces in your development instance READ ONLY. You use OS commands to copy the tablespace's datafiles. You also use the EXP utility to create a metadata file describing the tablespace's contents. Then, you put the tablespace back in READ WRITE mode.

Next, you move these datafiles to the production server. It should be very quick to drop the tablespace in production and plug in the tablespace you are transporting to production. I've used this method many times to cut my data moves from many, many hours to just minutes as opposed to using EXP/IMP.

For More Information


This was last published in February 2003

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close