| impd data only from one table to another table [message #689747] |
Fri, 12 April 2024 16:18  |
wtolentino
Messages: 422
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 #689749 is a reply to message #689748] |
Fri, 12 April 2024 23:02  |
wtolentino
Messages: 422
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 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
