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
- 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.
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.