Home » RDBMS Server » Server Utilities » impd data only on the same database from one table to another table (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
impd data only on the same database from one table to another table [message #689752] Mon, 15 April 2024 21:19 Go to next message
wtolentino
Messages: 404
Registered: March 2005
Senior Member
Is it possible to use the impdp to import a table from one table to another table for the same database? The reason for this is that because I can't use expdp for dump file due to space issue on the server. The table I am trying to export is about close to half billion rows.

I tried this impdp with no success
impdp myUser@myDB/****** tables=myUser.sample_tab_import01 content=data_only remap_table=myUser.sample_tab_import01:sample_tab_import02 table_exists_action=truncate nologfile=yes 1>impdp_myDB.log 2>&1

I got these errors:
Import: Release 19.0.0.0.0 - Production on Mon Apr 15 15:19:53 2024
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/opt/oracle/product/12.2.0.1.0/rdbms/log/expdat.dmp" for read
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 7
Re: impd data only on the same database from one table to another table [message #689753 is a reply to message #689752] Tue, 16 April 2024 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to use a db link as in my previous example.

It seems silly to create a db link on the same database but this is how it works.
I opened a SR on this in 10g and Oracle did not accept to create an enhancement request on this point.

Thinking about this, it seems it is just a command line syntax issue: as "dumpfile" parameter is optional, it can't know if your command refers on an implicit dump file or an export+import request.
I suggested we could specify a null dumpfile for this later but...

Note that I did not check but using a loopback db link may not mean Oracle uses the network layer.

[Updated on: Tue, 16 April 2024 01:19]

Report message to a moderator

Re: impd data only on the same database from one table to another table [message #689757 is a reply to message #689753] Tue, 16 April 2024 08:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9096
Registered: November 2002
Location: California, USA
Senior Member
I don't know if this is considered a loopback database link or not, but if you create a database link to the same database and use that as if it were a network link, then it works, as shown below.


SCOTT@orcl_12.1.0.2.0> CONN scott/tiger@orcl
Connected.
SCOTT@orcl_12.1.0.2.0> CREATE DATABASE LINK mylink CONNECT TO scott IDENTIFIED BY tiger USING 'ORCL'
  2  /

Database link created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE dept2 AS SELECT * FROM scott.dept@mylink WHERE 1 = 0
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM dept2
  2  /

no rows selected

SCOTT@orcl_12.1.0.2.0> HOST impdp scott/tiger tables=SCOTT.DEPT content=data_only network_link=mylink remap_table=DEPT:DEPT2

Import: Release 12.1.0.2.0 - Production on Tue Apr 16 06:14:45 2024

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** tables=SCOTT.DEPT content=data_only network_link=mylink remap_table=DEPT:DEPT2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . imported "SCOTT"."DEPT2"                                  4 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Tue Apr 16 06:14:51 2024 elapsed 0 00:00:04


SCOTT@orcl_12.1.0.2.0> SELECT * FROM dept2
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
        40 OPERATIONS     BOSTON
        10 ACCOUNTING     NEW YORK
        30 SALES          CHICAGO

4 rows selected.
Re: impd data only on the same database from one table to another table [message #689759 is a reply to message #689757] Tue, 16 April 2024 08:21 Go to previous message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I don't know if this is considered a loopback database link or not, but if you create a database link to the same database and use that as if it were a network link, then it works, as shown below.

Well, in my example in his previous topic I used a db link on the current database, I didn't specify it as it does not matter. The main point is you have to use a db link if you want to import without exporting.

Previous Topic: impd data only from one table to another table
Goto Forum:
  


Current Time: Sun May 19 22:30:40 CDT 2024