No question at this time
DBA Top 10
1 A. Kavsek 12000
2 M. Cadot 7900
3 B. Vroman 5700
4 P. Wisse 4500
5 J. PĂ©ran 2000
6 . Lauri 1000
7 T. P 300
7 D. Johnson 300
7 R. Pattyn 300
10 J. B 200
10 J. Van Der Steen 200
10 G. Muijrers 200
10 R. Chandran 200
10 A. Ismail 200
10 G. Am77 200
10 B. Bartben 200
10 N. Abdul Rahman 200
10 J. Akhtar 200
10 G. Graham 200
10 D. Ramakrishna 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48484
Total active users1453
Act. users last 24h2
Act. users last hour0
Registered user hits last week97
Registered user hits last month634
Go up

DBPUMP API to import via network_link
Next thread: Cloud certification for Oracle DBA's
Prev thread: PSU for databases

Message Score Author Date
I want to use DBPUMP API to import schema from my ...... Nishant Kariya Feb 09, 2020, 14:33
1. Data Pump runs at the same time on server and...... Michel Cadot Feb 09, 2020, 15:19
Hi Michel, Thanks for the information. Below...... Nishant Kariya Feb 09, 2020, 17:19
This is the code that I am trying: ==========...... Nishant Kariya Feb 09, 2020, 17:20
Please use "pre" tags as explained at the top of...... Michel Cadot Feb 09, 2020, 18:43
After many tests I found the root of your error. ...... Score: 1000 PtsScore: 1000 PtsScore: 1000 PtsScore: 1000 PtsScore: 1000 Pts Michel Cadot Feb 09, 2020, 19:33
Any feedback? ... Michel Cadot Feb 12, 2020, 09:08

Follow up by mail Click here


Subject: DBPUMP API to import via network_link
Author: Nishant Kariya, India
Date: Feb 09, 2020, 14:33, 14 days ago
Message: I want to use DBPUMP API to import schema from my UAT to DEV (Data only), via network link.

I have client installed on my system and hence the only option I have is to use API, because standard impdp and expdp are server side utilities.

I am not able to use this API correctly as it always throws me error -

I want to use Remap schema option. Can anyone please suggest..

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API to import via network_link
Author: Michel Cadot, France
Date: Feb 09, 2020, 15:19, 14 days ago
Message:
1. Data Pump runs at the same time on server and client.
2. impdp and expdp are client part of Data Pump
3. DBMS_DATAPUMP package (Data Pump API) as all PL/SQL packgae runs on the server (but are called from the client)
4. If you don't give us what you did and get we can't help.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API to import via network_link
Author: Nishant Kariya, India
Date: Feb 09, 2020, 17:19, 13 days ago
Message: Hi Michel,

Thanks for the information.

Below is the error that I get while executing API(package).
I am trying to open a import job via network link.


==================================================================================
Error report -
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1470
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5915
ORA-06512: at line 6
31626. 00000 - "job does not exist"
*Cause: An invalid reference to a job which is no longer executing,
is not executing on the instance where the operation was
attempted, or that does not have a valid Master Table.
Refer to any following error messages for clarification.
*Action: Start a new job, or attach to an existing job that has a
valid Master Table.

==================================================================================
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API to import via network_link
Author: Nishant Kariya, India
Date: Feb 09, 2020, 17:20, 13 days ago
Message: This is the code that I am trying:


===========================================================================================================================
set scan off
set serveroutput on
set escape off
DECLARE
h1 number;
errorvarchar varchar2(100):= 'ERROR';
tryGetStatus number := 0;
begin
h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA',job_name => 'IMPORT_JOB_POC', version => 'COMPATIBLE', remote_link =>'DBPUMP_TEST_LINK' );
tryGetStatus := 1;
dbms_datapump.set_parallel(handle => h1, degree => 1);
dbms_datapump.add_file(handle => h1, filename => 'POC-'||to_char(sysdate,'yyyy-mm-dd-hh24_mi_ss')||'.LOG', directory => 'DATA_PUMP', filetype => 3);
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1);
dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''FDW_DATA_PUMP'')');
DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_SCHEMA','FDW_DATA_PUMP','FDW_DATA_PUMP');
dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 0);
dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
DBMS_DATAPUMP.SET_PARAMETER(handle => h1,name => 'TABLE_EXISTS_ACTION',value => 'TRUNCATE');
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
dbms_datapump.detach(handle => h1);
errorvarchar := 'NO_ERROR';
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN
DBMS_DATAPUMP.DETACH(h1);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;


===========================================================================================================================
END;
RAISE;
END;
/
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API to import via network_link
Author: Michel Cadot, France
Date: Feb 09, 2020, 18:43, 13 days ago
Message:
Please use "pre" tags as explained at the top of the "Reply" page.

What is your Oracle version(s) (both source and target databases) with 4 decimals like "12.2.0.1".

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API to import via network_link
Author: Michel Cadot, France
Date: Feb 09, 2020, 19:33, 13 days ago
Score:   Score: 1000 PtsScore: 1000 PtsScore: 1000 PtsScore: 1000 PtsScore: 1000 Pts
Message: After many tests I found the root of your error.
This is erroneous message from "open" procedure when the master table exists and the job not:
SQL> create table dptest (i number);


Table created.

SQL> DECLARE
2 h1 number;
3 begin
4 h1 := dbms_datapump.open (
5 operation => 'IMPORT',
6 job_mode => 'SCHEMA',
7 job_name => 'DPTEST',
8 remote_link =>'MYLINK' );
9 end;
10 /
DECLARE
*
ERROR at line 1:
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5285
ORA-06512: at line 4


It also happens when the job no more exists nor the master table but its name is still in the SGA
(you can't do anything about this).
I advise you to either not name the job or use a variable name to prevent from this error.

Note that your PL/SQL block just starts the Data Pump job when it ends the job is running and
as you ask to keep the master table, it will be always there when it'll end.

Also in your exception block insert the following before you detach:
DBMS_DATAPUMP.STOP_JOB (h1, immediate=>1, delay=>0);


Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API to import via network_link
Author: Michel Cadot, France
Date: Feb 12, 2020, 09:08, 11 days ago
Message:
Any feedback?

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here