14 Replies Latest reply: May 9, 2012 6:20 AM by 510685 RSS

    Import table data in to another table via datapump

    Vikash Jain (DBA )
      Hi,

      I have Oracle 11gR2 on Windows 2008 server R1 64 bit version.
      I have taken one table export backup via data pump , that table name is sql_temp , my requirement is to import the data into another table like sql_temp1 using the same data pump export backup.
      Is is possible ? how can we do this via data pump ?

      Thanks & Regards,
      Vikash Jain(Junior DBA)
        • 1. Re: Import table data in to another table via datapump
          hitgon
          Hi,

          Please find the solution and reference document
          http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php#TableExpImp

          Regards
          Hitgon

          Edited by: hitgon on Apr 26, 2012 11:20 AM
          • 2. Re: Import table data in to another table via datapump
            NikolayIvankin
            import this table into another schema and after that create table as select in the required one.

            Or why don't create table as select initially, without help of datapump?
            • 3. Re: Import table data in to another table via datapump
              mBk77
              The remap_table parameter allows you to rename a table within a schema,we rename the sql_temp table to the sql_temp1 table:

              impdp test/test tables=sql_temp directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
              remap_table=sql_temp :sql_temp1
              • 4. Re: Import table data in to another table via datapump
                NikolayIvankin
                mBk77 wrote:
                remap_table=sql_temp :sql_temp1
                I'm sorry, but could you be so kind to point me for a doc about remap_table clause?

                Found it, sorry.

                Edited by: Nikolay Ivankin on 26.04.2012 10:10
                • 5. Re: Import table data in to another table via datapump
                  Vikash Jain (DBA )
                  Hi Hitgon,

                  Thanks for your quick reply .
                  My problem is i have taken export data pump of sql_temp table and want to import the same in sql_temp1 table of the same schema.
                  How is it possible with data pump import ?

                  Thanks & Regards,
                  Vikash Jain(Junior DBA)
                  • 6. Re: Import table data in to another table via datapump
                    Girish Sharma
                    Not really a big deal :
                    C:\Windows\system32>expdp scott/tiger tables=emp directory=DATA_PUMP_DIR dumpfile=test.dmp logfile=log.log
                    
                    Export: Release 11.2.0.1.0 - Production on Thu Apr 26 11:49:18 2012
                    
                    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
                    
                    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=emp directory=DATA_PUMP_DIR dumpfile=test.dmp logfile=log.log
                    Estimate in progress using BLOCKS method...
                    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                    Total estimation using BLOCKS method: 64 KB
                    Processing object type TABLE_EXPORT/TABLE/TABLE
                    Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
                    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
                    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
                    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
                    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                    . . exported "SCOTT"."EMP"                               8.570 KB      14 rows
                    Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
                    ******************************************************************************
                    Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
                      E:\APP\SERVERROOM\ADMIN\ORCL\DPDUMP\TEST.DMP
                    Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 11:49:32
                    
                    
                    C:\Windows\system32>impdp scott/tiger tables=emp directory=DATA_PUMP_DIR dumpfile=test.dmp logfile=log.log remap_table=emp:testemp
                    
                    Import: Release 11.2.0.1.0 - Production on Thu Apr 26 11:50:10 2012
                    
                    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
                    
                    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
                    Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** tables=emp directory=DATA_PUMP_DIR dumpfile=test.dmp logfile=log.log remap_table=emp:testemp
                    Processing object type TABLE_EXPORT/TABLE/TABLE
                    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                    . . imported "SCOTT"."TESTEMP"                           8.570 KB      14 rows
                    Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
                    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
                    ORA-31684: Object type INDEX:"SCOTT"."PK_EMP" already exists
                    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
                    ORA-31684: Object type CONSTRAINT:"SCOTT"."PK_EMP" already exists
                    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                    ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SCOTT"."PK_EMP" already exists
                    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
                    ORA-31684: Object type REF_CONSTRAINT:"SCOTT"."FK_DEPTNO" already exists
                    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                    Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 4 error(s) at 11:50:13
                    
                    
                    C:\Windows\system32>sqlplus scott/tiger
                    
                    SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 26 11:50:31 2012
                    
                    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                    
                    
                    Connected to:
                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    
                    SQL> select count(*) from testemp;
                    
                      COUNT(*)
                    ----------
                            14
                    
                    SQL>
                    Regards
                    Girish Sharma
                    • 7. Re: Import table data in to another table via datapump
                      mBk77
                      impdp help=y
                      it will show you all the possible things that you can do during an import.
                      • 8. Re: Import table data in to another table via datapump
                        Vikash Jain (DBA )
                        Thank You very much Girish,

                        I got my answer .. your effort really mean to me.

                        Thanks & regards,
                        Vikash Jain
                        • 9. Re: Import table data in to another table via datapump
                          Vikash Jain (DBA )
                          Thanks mBk77,

                          Your answer is also correct , but i can mark only correct answer ...
                          • 10. Re: Import table data in to another table via datapump
                            713555
                            careful with constraint and index names importing to same schema and renaming table, same names cant exist for those objects in the same schema even though youve renamed the table. youll have to recreate the indexes and constraints manually with different names which is sometimes qwuicker then remaping those objects.

                            just to be aware.
                            • 11. Re: Import table data in to another table via datapump
                              Vikash Jain (DBA )
                              Hi Girish ,

                              While trying as you mentioned below , i m getting below error ..

                              C:\>impdp alg10/alg10 tables=week_mas directory=DUMPDIRECTORY dumpfile=TEST_DATAPUMP.DMP logfile=Week_Mas.DATAPUMP.log remap_table=week_mas:week_mas_test

                              Import: Release 11.1.0.6.0 - Production on Thursday, 26 April, 2012 16:17:23

                              Copyright (c) 2003, 2007, Oracle. All rights reserved.

                              Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
                              With the OLAP option
                              Master table "ALG10"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
                              Starting "ALG10"."SYS_IMPORT_TABLE_01": alg10/******** tables=week_mas directory=DUMPDIRECTORY dumpfile=TEST_DATAPUMP.DMP logfile=Week_Mas.DATAPUMP.log remap_table=week_mas:week_mas_test
                              Processing object type TABLE_EXPORT/TABLE/TABLE
                              ORA-39151: Table "ALG10"."WEEK_MAS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
                              Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                              Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
                              Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                              Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
                              Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                              Job "ALG10"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 16:17:25

                              Can you please tell me where i missed ?
                              should i create week_mas_test table as week_mas (structure alone) ?

                              Thanks & regards,
                              Vikash Jain
                              • 12. Re: Import table data in to another table via datapump
                                Girish Sharma
                                ORA-39151: Table "ALG10"."WEEK_MAS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
                                Probably you have re-imported the table, because Oracle never says Wrong...! Just try to :
                                1.Drop the existing table like drop table week_mas purge;
                                2.impdp alg10/alg10 tables=week_mas directory=DUMPDIRECTORY dumpfile=TEST_DATAPUMP.DMP logfile=Week_Mas.DATAPUMP.log remap_table=week_mas:week_mas_test TABLE_EXISTS_ACTION=APPEND

                                Now let us know, if you gets any problem.

                                Regards
                                Girish Sharma
                                • 13. Re: Import table data in to another table via datapump
                                  Vikash Jain (DBA )
                                  hi Girish,

                                  I tried as you suggested me to do ..before importing i dropped the existing table week_mas_test.

                                  please check this below output:


                                  C:\>impdp alg10/alg10 tables=week_mas directory=DUMPDIRECTORY dumpfile=TEST_DATAPUMP.DMP logfile=Week_Mas.DATAPUMP01.log remap_table=week_mas:week_mas_test TABLE_EXISTS_ACTION=APPEND

                                  Import: Release 11.1.0.6.0 - Production on Friday, 27 April, 2012 11:47:17

                                  Copyright (c) 2003, 2007, Oracle. All rights reserved.

                                  Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
                                  With the OLAP option
                                  Master table "ALG10"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
                                  Starting "ALG10"."SYS_IMPORT_TABLE_01": alg10/******** tables=week_mas directory=DUMPDIRECTORY dumpfile=TEST_DATAPUMP.DMP logfile=Week_Mas.DATAPUMP01.log remap_table=week_mas:week_mas_test TABLE_EXISTS_ACTION=APPEND
                                  Processing object type TABLE_EXPORT/TABLE/TABLE
                                  ORA-39152: Table "ALG10"."WEEK_MAS" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
                                  Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                                  . . imported "ALG10"."WEEK_MAS" 0 KB 0 rows
                                  Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
                                  Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                                  Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
                                  Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                                  Job "ALG10"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 11:47:28


                                  C:\>sqlplus alg10/alg10
                                  SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 27 11:47:48 2012
                                  Copyright (c) 1982, 2007, Oracle. All rights reserved.

                                  Connected to:
                                  Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
                                  With the OLAP option

                                  SQL> select * from week_mas_test;
                                  select * from week_mas_test
                                  *
                                  ERROR at line 1:
                                  ORA-00942: table or view does not exist

                                  Kindly suggest me , what should be the reason now ?

                                  Thanks & Regards
                                  Vikash Jain
                                  • 14. Re: Import table data in to another table via datapump
                                    510685
                                    Hi, According to the documentation, there are restrictions :
                                    •Only objects created by the Import will be remapped. In particular, preexisting tables will not be remapped if TABLE_EXISTS_ACTION is set to TRUNCATE or APPEND.

                                    This means that if you're APPENDING to an existing table, you're not allowed to remap at the same time.

                                    So, please try this & let me know :
                                    impdp alg10/alg10 tables=week_mas directory=DUMPDIRECTORY dumpfile=TEST_DATAPUMP.DMP logfile=Week_Mas.DATAPUMP01.log remap_table=week_mas:week_mas_test