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

Importing data to new database structure

We have an Oracle database with data in it. Now we need to migrate to a new DB structure in which most of the tables are altered and new tables added as well. The problem I'm facing is how to import the existing data from the old version to the new version?

We have an Oracle database with data in it. Now we need to migrate to a new DB structure in which most of the tables are altered and new tables added as well. The problem I'm facing is how to import the existing data from the old version to the new version? In DB2 I have already done it successfuly by writing customized export queries, because in DB2 you can export based on a query, e.g., I can write EXPORT TO userinfo.ixf OF IXF SELECT 3, USERID, USERNAME, FULLNAME, PASSWORD, PHONE, PHONE2, EMAIL, URL, ADDRESS, OTHERINFO, VERSION, SEARCHTRACE, LOGOUTTIME,' ' FROM CBR_USERINFO. Here the first value (3) and the last value (' ') are additional to what is present in the database. I need this because in the newer version two columns are added in this particular table.
If I had to do this task, I would simply add the new columns to the old database's table and then export it. When you import into the new database, it will import just fine. You can add the columns to the old table with a command similar to the following:
ALTER TABLE table_name ADD (column_name datatype);
There is no way to add columns to the export dump so you'll have to add them before you take the dump.

Dig Deeper on Oracle database backup and recovery

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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close