Home » RDBMS Server » Server Utilities » append tables content to an existing tables (solaris 10 on oracle 11gR2)
append tables content to an existing tables [message #482094] Tue, 09 November 2010 08:39 Go to next message
sailesh
Messages: 72
Registered: September 2007
Location: MAURITIUS
Member
Hi all,

I am facing a problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.

we have found something called table_exist_action=append in impdp.

Thanks,

Sailesh
Re: append tables content to an existing tables [message #482095 is a reply to message #482094] Tue, 09 November 2010 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the question is?

Regards
Michel
Re: append tables content to an existing tables [message #482108 is a reply to message #482095] Tue, 09 November 2010 12:36 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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>
Previous Topic: Exporting table structure from a particular DB
Next Topic: importing data from 10G to 8i
Goto Forum:
  


Current Time: Thu Mar 28 04:55:15 CDT 2024