What is the "source" here? Records exported with what utility? EXP or EXPDP?
If it was EXP, then all you have to do is to include the IGNORE=Y parameter. It will make it possible to continue with import even though the table exists. All "duplicates" (as of primary/unique keys/indexes) will be rejected. All new records will be imported. Here's an example: I created a T_DEPT table in Scott's schema (equal to his DEPT table); unique index is on DEPTNO column:SQL> create table t_dept as select * from dept;
Table created.
SQL> create unique index uitd on t_dept (deptno);
Index created.
SQL> select * from t_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
In another user's schema (it is MIKE), I created another T_DEPT table. Deleted a few records, inserted a new one:SQL> show user
USER is "MIKE"
SQL> select * from t_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
99 test test
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
SQL>
OK, let's export Mike's T_DEPT table:SQL> $exp mike/lion@ora10 tables=t_dept file=t_dept.dmp
Export: Release 10.2.0.1.0 - Production on Uto Stu 9 19:32:54 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T_DEPT 3 rows exported
Export terminated successfully without warnings.
SQL>
Let's import it into Scott's schema. See what's going on:SQL> $imp scott/tiger@ora10 file=t_dept.dmp ignore=y full=y
Import: Release 10.2.0.1.0 - Production on Uto Stu 9 19:33:39 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by MIKE, not by you
import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
. importing MIKE's objects into SCOTT
. importing MIKE's objects into SCOTT
. . importing table "T_DEPT"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.UITD) violated
Column 1 10
Column 2 ACCOUNTING
Column 3 NEW YORK
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.UITD) violated
Column 1 20
Column 2 RESEARCH
Column 3 DALLAS 1 rows imported
IMP-00017: following statement failed with ORACLE error 20000:
"BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,'"ITD"',NULL,NULL,NULL,4,1,4,1,1,1,0"
",0); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: INDEX "SCOTT"."ITD" does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2121
ORA-06512: at "SYS.DBMS_STATS", line 5393
ORA-06512: at line 1
Import terminated successfully with warnings.
SQL>
Finally, what do we have in Scott's T_DEPT table? (Should be existing records, plus newly added ('test') one):SQL> show user
USER is "SCOTT"
SQL> select * from t_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
99 test test
SQL>
] 
Blog
Search
Help
Members
Register
Login
Home
