I have a table tab1 with three columns in one schema and the same table with two columns in another schema. I need to import the data from first schema into second schema. If I try to do as below, it fails due to obvious reasons. How can avoid the error and import only the data for the columns that exist?
Steps to reproduce the issue.
1. In test1 user:
create table tab1( col1 varchar2(20) not null, col2 varchar2(200), col3 varchar2(4000));
alter table tab1 add constraint pk_tab1 primary key (col1);
insert into tab1 values ('r1c1', 'r1c2', 'r1c3');
insert into tab1 values ('r2c1', 'r2c2', 'r2c3');
commit;
2. In test2 user:
create table tab1( col1 varchar2(20) not null, col2 varchar2(200));
alter table tab1 add constraint pk_tab1 primary key (col1);
3. Created the dump file using:
EXPDP test1/test1@xyz DIRECTORY=DATA_PUMP_DIR DUMPFILE=coltest.dmp LOGFILE=exp.txt TABLES=TAB1
4. Trying to use the following command to import the dump file:
IMPDP test2/test2@xyz DIRECTORY=DATA_PUMP_DIR DUMPFILE=coltest.dmp LOGFILE=imp1.txt REMAP_SCHEMA=TEST1:TEST2 TABLE_EXISTS_ACTION=APPEND CONTENT=DATA_ONLY
I get the following error:
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TEST2"."TAB1" failed to load/unload and is being skipped due to error:
ORA-00904: "COL3": invalid identifier
Job "TEST2"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 21:26:18
you can import into a view - the view can have all three columns - and then using an instead of trigger - just insert the two you want
So, you could set up a mapping schema - it'll have a view of the tables in the schema to import into. These views will have instead of triggers on them.
Excerpt from Expert One on One Oracle:
Import into Different Structures
This is an issue that comes up frequently; you have an export of some data and need to import it into a slightly different structure, is there any way to do that? I’ve seen this when exporting data from version 1 of a software package into a database that has version 2 of the software (or vice versa). The answer is yes, but we might need some help in doing so. There are 3 cases to consider here:
You have ADDED a column (no work on your part needed, Oracle will put a NULL or whatever DEFAULT you have set in it)
You have DROPPED a column (some work on your part)
You have changed the datatype of a column (again, some work on your part)
We will look at an example of each of the three. For the additional column case, we need to do nothing. Oracle will insert into the table normally and the default for the new column will be inserted for us. For the dropped and modified columns, we will import into a view with an instead of trigger that we use to perform whatever data mapping we need. In our case, we will just ignore the dropped column and we will convert a NUMBER into a DATE. Note that the use of an instead trigger will obviously add overhead – this is a fine solution for medium sets of data – you will not want to load 10’s of millions of rows in this fashion! Here are the tables for the example:
tkyte@TKYTE816> create table added_a_column ( x int );
Table created.
tkyte@TKYTE816> create table dropped_a_column ( x int, y int );
Table created.
tkyte@TKYTE816> create table modified_a_column( x int, y int );
Table created.
tkyte@TKYTE816> insert into added_a_column values ( 1 );
1 row created.
tkyte@TKYTE816> insert into dropped_a_column values ( 1, 1 );
1 row created.
tkyte@TKYTE816> insert into modified_a_column values ( 1, 1 );
1 row created.
tkyte@TKYTE816> commit;
Commit complete.
We will start by exporting the three tables:
tkyte@TKYTE816> host exp userid=tkyte/tkyte
tables=(added_a_column,dropped_a_column,modified_a_column)
Export: Release 8.1.6.0.0 - Production on Tue Mar 20 09:02:34 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table ADDED_A_COLUMN 1 rows exported
. . exporting table DROPPED_A_COLUMN 1 rows exported
. . exporting table MODIFIED_A_COLUMN 1 rows exported
Export terminated successfully without warnings.
So, that constitutes our test case. We have exported the three tables 'as is'. Now, let's modify them:
tkyte@TKYTE816> alter table added_a_column add ( y int );
Table altered.
tkyte@TKYTE816> alter table dropped_a_column drop column y;
Table altered.
tkyte@TKYTE816> delete from modified_a_column;
1 row deleted.
tkyte@TKYTE816> alter table modified_a_column modify y date;
Table altered.
Now, if we attempt to import, we will find that ADDED_A_COLUMN works OK, but the rest fail
tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y ignore=y
Import: Release 8.1.6.0.0 - Production on Tue Mar 20 09:02:34 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
Export file created by EXPORT:V08.01.06 via conventional path
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing TKYTE's objects into TKYTE
. . importing table "ADDED_A_COLUMN" 1 rows imported
. . importing table "DROPPED_A_COLUMN"
IMP-00058: ORACLE error 904 encountered
ORA-00904: invalid column name
. . importing table "MODIFIED_A_COLUMN"
IMP-00058: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes
Import terminated successfully with warnings.
OK, so now we have to put into the database, views that look like the original tables. To accomplish this we will:
Rename the tables for the duration of the IMPORT
Create a view that selects constants of the correct type – for example, select 1 for a number, select SYSDATE for a date, select RPAD('*', 30, '*') for a varchar2(30) and so on
Create an instead of trigger that 'does the right thing' for us, performing any data conversion/mapping we need
tkyte@TKYTE816> rename modified_a_column to modified_a_column_TEMP;
Table renamed.
tkyte@TKYTE816> create or replace view modified_a_column
2 as
3 select 1 x, 1 y from modified_a_column_TEMP;
View created.
tkyte@TKYTE816> create or replace trigger modified_a_column_IOI
2 instead of insert on modified_a_column
3 begin
4 insert into modified_a_column_TEMP
5 ( x, y )
6 values
7 ( :new.x, to_date('01012001','ddmmyyyy')+:new.y );
8 end;
9 /
Trigger created.
Here, we are converting the number that was stored in Y, into an offset from January 1, 2001. You would perform whatever conversion from string to date, date to number, number to string, and so on, you needed to here.
Now we take care of the dropped column:
tkyte@TKYTE816> rename dropped_a_column to dropped_a_column_TEMP;
Table renamed.
tkyte@TKYTE816> create or replace view dropped_a_column
2 as
3 select 1 x, 1 y from dropped_a_column_TEMP;
View created.
tkyte@TKYTE816> create or replace trigger dropped_a_column_IOI
2 instead of insert on dropped_a_column
3 begin
4 insert into dropped_a_column_TEMP
5 ( x )
6 values
7 ( :new.x );
8 end;
9 /
Trigger created.
Here, we are just getting rid of :new.y. We do not do anything with it – we just ignore it. It needs to be in the view so IMP has something to insert into.
Now we are ready to import again:
tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y ignore=y
Import: Release 8.1.6.0.0 - Production on Tue Mar 20 09:21:41 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
Export file created by EXPORT:V08.01.06 via conventional path
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing TKYTE's objects into TKYTE
. . importing table "ADDED_A_COLUMN" 1 rows imported
. . importing table "DROPPED_A_COLUMN" 1 rows imported
. . importing table "MODIFIED_A_COLUMN" 1 rows imported
Import terminated successfully without warnings.
Now the import runs cleanly, we need to go into the database and drop our views and rename our tables:
tkyte@TKYTE816> drop view modified_a_column;
View dropped.
tkyte@TKYTE816> drop view dropped_a_column;
View dropped.
tkyte@TKYTE816> rename dropped_a_column_TEMP to dropped_a_column;
Table renamed.
tkyte@TKYTE816> rename modified_a_column_TEMP to modified_a_column;
Table renamed.
When we look at the data, we expect to see the following:
three rows in added_a_column from the original insert and two imports
two rows in dropped_a_column from the original insert and one import that worked on it
one row in modified_a_column since we had to empty this table prior to altering the column type
tkyte@TKYTE816> select * from added_a_column;
X Y
---------- ----------
1
1
1
tkyte@TKYTE816> select * from dropped_a_column;
X
----------
1
1
tkyte@TKYTE816> select * from modified_a_column;
X Y
---------- ---------
1 02-JAN-01