I am trying to do a datapump import using network_link, but it doesn't work. My db is 184.108.40.206.7 on Solaris.
I've tried to create the db link as private and public. No difference.
Here is what I did:
sql>create database link somedblinkname connect to dan identified by "daniel4321" using
Database link created.
select instance_name from V$instance@somedblinkname;
select username from dba_users where username='SCOTT';
impdp dan1 schemas=scott remap_schema=scott:tmpdaniel directory=DPUMPDIR NETWORK_LINK=sommedblinkname
Import: Release 220.127.116.11.0 - Production on Thu Dec 19 12:39:27 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORA-39001: invalid argument value
ORA-39170: Schema expression 'SCOTT' does not correspond to any schemas.
I am trying to import schema SCOTT from database1 into schema TMPDANIEL in database2. User SCOTT exist in the database1, but it doesn't exist in database2.
User dan1 and dan that is used in the DB link, have import and export full database roles and also dba role (imp/exp roles should be in dba role, but i granted explicitly also).
The db link works properly and I can query everything in database2 from database1.
The import should be started from database1 or from database2? Does it matter from where i'm starting the import?
Why do I get the above error?
Hi, that's a different issue. My schema doesn't contain any special character. If i do expdp to the local directory it works fine.
When I try to send the schema objects across it fails. More, if i create the user in the database2, scott schema, it kind of works. When I have SCOTT schema on database2, it imports only the sys_import_% tables from the user that is in the db link definition.
I have not played with network_link alot and I do not know if this make sense
"Cross-schema references are not imported for non-privileged users unless the other schema is remapped to the current schema. For example, a trigger defined on a table within the importing user's schema, but residing in another user's schema, is not imported."
datapump import always 'pulls' from the database link. In your example scott seems to be a local user.
your command needs to connect to the destination database with impdp user/pass , the source is then referenced as the network link. Think of the network link as just being a remote file To load.
I was thinking this could be the issue. BUT, if i am not specifying schemas then the import is working - sort of anyway.
It imports/transfers the tables in the current user to the remote db link user schema.
That's why i'm confused if it works or not. Besides, i need to be able to export-import into a remote destination. I don't like the solution with setting up SSH keys all over the servers ...
I will try to see if it works from the remote database to import.
apparently remote operation can be issued only from remote (destination) database:
If i understand correctly, there is no other difference between export and import when using network_link except the fact that expdp writes to a file into the dest system and impdp writes directly to the remote db.....