Home » RDBMS Server » Server Utilities » impd data only from one table to another table (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
impd data only from one table to another table [message #689747] Fri, 12 April 2024 16:18 Go to next message
wtolentino
Messages: 404
Registered: March 2005
Senior Member
How do I use the impdp to import a table's data from one table to another table? I am using a network link parameter.

Remote table: WRR_CASE_PAYMTH_SMRY
Table to be imported to: WRR_CASE_PAYMTH_SMRYTMP

This does not work:
impdp myAcct@dbQAT/********** schemas=WRRAPP include=table('WRR_CASE_PAYMTH_SMRY') content=DATA_ONLY network_link=dbPseudo NOLOGFILE=YES 1> impdp_04122024.log 2>&1

Please advise. Thank you.
Re: impd data only from one table to another table [message #689748 is a reply to message #689747] Fri, 12 April 2024 19:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
Here is a link with some sample syntaxes for export and import via network_link using expdp and impdp and required privileges:

https://oracle-base.com/articles/10g/oracle-data-pump-10g#NetworkExportsImports

Although it looks like you can import without a dumpfile, you may still need to specify a directory even if you are not using a log file.

[Updated on: Fri, 12 April 2024 19:49]

Report message to a moderator

Re: impd data only from one table to another table [message #689749 is a reply to message #689748] Fri, 12 April 2024 23:02 Go to previous messageGo to next message
wtolentino
Messages: 404
Registered: March 2005
Senior Member
Hi Barbara,

Thanks for the reply. I have been using the impdp with a simple import of all schema objects using a network link with success.

Example:
impdp myAcct@dbQAT/********** schemas=WRRAPP content=METADATA_ONLY network_link=dbPseudo NOLOGFILE=YES 1> impdp_04122024.log 2>&1

But haven't yet with a scenario to import for data only from one table to another table. Normally I could do something like this with the database link:

insert into WRR_CASE_PAYMTH_SMRYTMP
select * from WRR_CASE_PAYMTH_SMRY@dbPseudo;

However, not at this time because the table is complicated by partitions and subpartitions and rows is at close to half-billion rows.

[Updated on: Fri, 12 April 2024 23:33]

Report message to a moderator

Re: impd data only from one table to another table [message #689750 is a reply to message #689747] Sat, 13 April 2024 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> -- before impdp
SQL> select * from emp2;

no rows selected

>impdp michel/michel tables=MICHEL.EMP content=data_only network_link=mylink remap_table=EMP:EMP2

Import: Release 11.2.0.4.0 - Production on Sam. Avr. 13 11:33:19 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MICHEL"."SYS_IMPORT_TABLE_01":  michel/******** tables=MICHEL.EMP content=data_only network_link=mylink remap_table=EMP:EMP2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . imported "MICHEL"."EMP2"                                 14 rows
Job "MICHEL"."SYS_IMPORT_TABLE_01" successfully completed at Sam. Avr. 13 11:33:45 2024 elapsed 0 00:00:24

SQL> -- after impdp
SQL> select * from emp2;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 23/05/1987 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 03/12/1981 00:00:00        950                    30
      7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 23/01/1982 00:00:00       1300                    10

14 rows selected.

[Updated on: Sat, 13 April 2024 04:37]

Report message to a moderator

Re: impd data only from one table to another table [message #689751 is a reply to message #689750] Sat, 13 April 2024 07:37 Go to previous message
wtolentino
Messages: 404
Registered: March 2005
Senior Member
i figure it out. thanks everyone. Michel your example also helps and i appreciate it.
Previous Topic: Services restart during datapump expdp and impdp
Next Topic: impd data only on the same database from one table to another table
Goto Forum:
  


Current Time: Wed May 29 15:44:33 CDT 2024