We are migrating our Oracle database to a new environment. There are some custom tables which are present in both the databases, but the new environment database has some extra columns in those tables. Could you please help me in using the export/import utility so that the extra columns are either imported as null values or are exported from the old environment as null values which will then be imported as is? Thanks.

    Requires Free Membership to View

There are likely two options here that will allow you to transfer data from the source to the target while preserving the extra columns on the target system. Using export/import will not allow you to preserve those columns in the target database. You can use a query option with export, but this only allows certain rows to be exported and does not allow only certain columns to be exported.

The first option involves creating a database link between the source and target databases, and then using the insert command to only insert the rows and columns you require. Here is an example:

INSERT INTO emp (empid, ename, email)
SELECT empid, ename, email
FROM emp@remote_db
WHERE ... ;

Your second option would be SQL*Loader. You would have to read the rows out of the source tables into a format that SQL*Loader could read, but you would then have the option of using SQL*Loader to insert into the target or to append if rows already existed. For more information about SQL*Loader, review theOracle Database 10g Release 2 Utilities (10.2).

This was first published in December 2006

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: