Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle SQL Developer Data Pump Import Wizard

User_VNO57Aug 19 2021

Dear community,
I am aware that this is a newbie question but I couldn't find a precise solution to the following problem:
I am trying to import a data pump (35 GB) from Oracle 12c Standard Edition into 18c Express Edition. As far as I know this should be possible. I followed a tutorial that can be found googling "SQL Developer 3.1 Data Pump Wizards (expdp, impdp)" (not sure if links are allowed in here).
I chose no remapping and selected the version 12c in the Options step. This is the summary
aIGO9.png
Anyways, while going through the steps of conducting a full import into the local host connection, the only table that appears is the job table itself but none of the expected tables out of the pump appear in the Tables section (the other tables that can be seen in the screenshot were there prior to the import). In addition, the process takes way to short - around 3 seconds which indicates the the pump is not being properly imported.
D9306.png
This is the log that is being produced during the job execution.
Rjw0a.png
Here is the code that is being generated in the PL/SLQ Tab:

set scan off
set serveroutput on
set escape off
whenever sqlerror exit 
DECLARE
    s varchar2(1000); 
    h1 number;
    errorvarchar varchar2(100):= 'ERROR';
    tryGetStatus number := 0;
begin
    h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'FULL', job_name => 'IMP_SD_26812-17_41_22', version => '12.1'); 
    tryGetStatus := 1;
    dbms_datapump.set_parallel(handle => h1, degree => 1); 
    dbms_datapump.add_file(handle => h1, filename => 'IMPORT-'||to_char(sysdate,'hh24_mi_ss')||'.LOG', directory => 'DATA_PUMP_DIR', filetype=>DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); 
    dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1); 
    dbms_datapump.add_file(handle => h1, filename => 'BKP_test_20072021.dmp', directory => 'DATA_PUMP_DIR', filetype => 1); 
    dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1); 
    dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC'); 
    dbms_datapump.set_parameter(handle => h1, name => 'REUSE_DATAFILES', value => 0); 
    dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNUSABLE_INDEXES', value => 0);
    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;
/

I've made sure to select the correct system directory which contains the pump.
In case that is an obvious mistake, kindly let me know where to look to troubleshoot the issue.

Comments
Post Details
Added on Aug 19 2021
1 comment
448 views