I'm embarassed to be asking this. I'm sure it will turn out to be a Homer Simpson "DOH!" moment.
A script to demo running impdp over a db link. Sql script creates the link, then proves it with a query to dual, then creates a couple of schemas, the the 'host' command to execute impdp. The query to the dblink immediately after its creation works fine, but then impdp reports ORA-02019 on it.
This is on a VBox VM, and the db link actually points back to the db in which it is created.
Transcript of putty log follows. All operations being executed by SYSTEM.
=~=~=~=~=~=~=~=~=~=~=~= PuTTY log 2021.05.11 16:36:25 =~=~=~=~=~=~=~=~=~=~=~=
login as: oracle
oracle@192.168.0.201's password:
Activate the web console with: systemctl enable --now cockpit.socket
Last login: Tue May 11 16:22:11 2021 from 192.168.0.115
The Oracle base has been set to /u01/app/oracle
============ oratab is ===========
cdb:/u01/app/oracle/product/19.0.0/dbhome_1:N
pdb01:/u01/app/oracle/product/19.0.0/dbhome_1:N
==================================
2021-05-11 16:36:35
oracle:cdb$ sqlplus /nolog @demo
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 11 16:36:54 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected.
SQL> set serverout on
SQL> --
SQL> -- create db link
SQL> --
SQL> CREATE PUBLIC DATABASE LINK lk_pdb01
2 CONNECT TO system IDENTIFIED BY halftrack
3 USING 'pdb01';
Database link created.
SQL> select * from dual@lk_pdb01;
D
-
X
1 row selected.
SQL> --
SQL> -- create source schema
SQL> --
SQL> create user bob
2 identified by bob
3 default tablespace users
4 temporary tablespace temp;
User created.
SQL> grant unlimited tablespace to bob;
Grant succeeded.
SQL> create table bob.test_table_1 (
2 fname varchar2(10),
3 lname varchar2(10),
4 dob date
5 );
Table created.
SQL> insert into bob.test_table_1 values ('Bob','Doofus',sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> --
SQL> -- create target schema
SQL> --
SQL> create user carol
2 identified by carol
3 default tablespace users
4 temporary tablespace temp;
User created.
SQL> grant unlimited tablespace to carol;
Grant succeeded.
SQL> create table carol.test_table_1 (
2 fname varchar2(10),
3 lname varchar2(10),
4 dob date
5 );
Table created.
SQL> insert into carol.test_table_1 values ('Carol','Floozy',sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> --
SQL> -- see what we have so far
SQL> --
SQL> select * from bob.test_table_1;
FNAME LNAME DOB
---------- ---------- ---------
Bob Doofus 11-MAY-21
1 row selected.
SQL> select * from carol.test_table_1;
FNAME LNAME DOB
---------- ---------- ---------
Carol Floozy 11-MAY-21
1 row selected.
SQL> --
SQL> -- run the export
SQL> --
SQL> host impdp system/halftrack@pdb01 directory=DATA_PUMP_DIR network_link=lk_pdb01 schemas=bob remap_schema=bob:carol TABLE_EXISTS_ACTION=truncate
Import: Release 19.0.0.0.0 - Production on Tue May 11 16:36:54 2021
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
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@pdb01 directory=DATA_PUMP_DIR network_link=lk_pdb01 schemas=bob remap_schema=bob:carol TABLE_EXISTS_ACTION=truncate
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CAROL" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "CAROL"."TEST_TABLE_1" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
ORA-31693: Table data object "CAROL"."TEST_TABLE_1" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Tue May 11 16:37:31 2021 elapsed 0 00:00:34
SQL> --
SQL> -- see what we have now
SQL> --
SQL> select * from bob.test_table_1;
FNAME LNAME DOB
---------- ---------- ---------
Bob Doofus 11-MAY-21
1 row selected.
SQL> select * from carol.test_table_1;
no rows selected
SQL> --
SQL> --drop table bob.test_table_1 purge;
SQL> --drop table carol.test_table_1 purge;
SQL> drop user bob cascade;
User dropped.
SQL> drop user carol cascade;
User dropped.
SQL> drop public database link lk_pdb01;
Database link dropped.
SQL> --
SQL> spo off