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
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.