I have this slow data pump export, and I have some suggestions for parameters that could improve speed. But I cannot seem to pass them via the DBMS_DATAPUMP package. Is it possible?
DECLARE PUMP_HANDLE NUMBER := DBMS_DATAPUMP.OPEN (OPERATION=>'EXPORT', JOB_MODE=>'TABLE', JOB_NAME=>'EXP_DATABASE_370');
BEGIN
DBMS_DATAPUMP.ADD_FILE (PUMP_HANDLE, DIRECTORY=>'EXP_DATABASE_DIR', FILENAME=>'MY_DATA_A1.DMP', FILETYPE=>DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP.ADD_FILE (PUMP_HANDLE, DIRECTORY=>'EXP_DATABASE_DIR', FILENAME=>'MY_DATA_A2.DMP', FILETYPE=>DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP.ADD_FILE (PUMP_HANDLE, DIRECTORY=>'EXP_DATABASE_DIR', FILENAME=>'MY_DATA_A5.TXT', FILETYPE=>DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.METADATA_FILTER (PUMP_HANDLE, NAME=>'NAME_EXPR', VALUE=>'IN (''MY_DATABASE_370'')');
DBMS_DATAPUMP.SET_PARAMETER (PUMP_HANDLE, NAME=>'INCLUDE_METADATA', VALUE=>1);
DBMS_DATAPUMP.SET_PARALLEL (PUMP_HANDLE, DEGREE=>4);
<<THIS_LINE_FAILS>> DBMS_DATAPUMP.SET_PARAMETER (PUMP_HANDLE, NAME=>'ACCESS_METHOD', VALUE=>'DIRECT_PATH');
DBMS_DATAPUMP.START_JOB(PUMP_HANDLE);
DBMS_DATAPUMP.DETACH (PUMP_HANDLE);
END;
The line marked <<THIS_LINE_FAILS>> raises an exception:
ORA-20020: Errors: ORA-39001: invalid argument value; ORA-39049: invalid parameter name ACCESS_METHOD;
ORA-06512: at line 10
Replacing <<THIS_LINE_FAILS>> with this call also fails with the same message
DBMS_DATAPUMP.SET_PARAMETER (PUMP_HANDLE, NAME=>'ACCESS_METHOD', VALUE=>'EXTERNAL_TABLES');
Replacing <<THIS_LINE_FAILS>> with this call also fails with the same message
DBMS_DATAPUMP.SET_PARAMETER (PUMP_HANDLE, NAME=>'ACCESS_METHOD', VALUE=>1); /* INTEGER doesn't seem to work either */
Replacing <<THIS_LINE_FAILS>> with this call also fails with a similar message
DBMS_DATAPUMP.SET_PARAMETER (PUMP_HANDLE, NAME=>'PARALLEL_FORCE_LOCAL', VALUE=>1);
Replacing <<THIS_LINE_FAILS>> with this call also fails, with a quite different message
DBMS_DATAPUMP.SET_PARAMETER (PUMP_HANDLE, NAME=>'DATA_OPTIONS', VALUE=>'DISABLE_APPEND_HINT');
ORA-20020: Errors: ORA-39001: invalid argument value; ORA-39207: Value NULL is invalid for parameter DATA_OPTIONS.;