OraFAQ Forum: Server Utilities » impd data only on the same database from one table to another table

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

impd data only on the same database from one table to another table [message #689752]

Mon, 15 April 2024 21:19

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

Report message to a moderator

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

Michel Cadot
Messages: 68675
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

Barbara Boehmer
Messages: 9097
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.

Report message to a moderator

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

Michel Cadot
Messages: 68675
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.

Report message to a moderator

Previous Topic:	impd data only from one table to another table
Next Topic:	Not able to run expdp

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 08 11:28:36 CDT 2024