Normally when datapump fails you get a sensible error message but sometimes it seems to have hidden what the problem actually was. In the example below
when i create a new user and just give it create session and then run this plsql block i just get the message
Which tells me nothing about what actually went wrong
If i add the exception block in though i get the actual problem from DBMS_OUTPUT.
The user just needs create table rights and it will work. This is very useful for when datapump isn;t telling you what is really wrong.
DECLARE
l_dp_handle NUMBER;
l_last_job_state VARCHAR2(30) := 'UNDEFINED';
l_job_state VARCHAR2(30) := 'UNDEFINED';
l_sts KU$_STATUS;
v_logs ku$_LogEntry;
v_row PLS_INTEGER;
v_job_state varchar2(4000);
BEGIN
l_dp_handle := DBMS_DATAPUMP.open(operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
version => 'LATEST');
DBMS_DATAPUMP.add_file(handle => l_dp_handle,
filename => 'test.dmp',
directory => 'SCRATCH',
reusefile => 1);
DBMS_DATAPUMP.add_file(handle => l_dp_handle,
filename => 'test.log',
directory => 'SCRATCH',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
reusefile => 1);
DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_LIST', '''ALIGNE''');
DBMS_DATAPUMP.DATA_FILTER(handle => l_dp_handle,
name => 'INCLUDE_ROWS',
value => 0);
dbms_datapump.set_debug(debug_flags => to_number('1FF0300',
'XXXXXXXXXXXXX'),
version_flag => 1);
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.WAIT_FOR_JOB(l_dp_handle, v_job_state);
DBMS_OUTPUT.PUT_LINE(v_job_state);
END;
when i create a new user and just give it create session and then run this plsql block i just get the message
Which tells me nothing about what actually went wrong
If i add the exception block in though i get the actual problem from DBMS_OUTPUT.
DECLARE
l_dp_handle NUMBER;
l_last_job_state VARCHAR2(30) := 'UNDEFINED';
l_job_state VARCHAR2(30) := 'UNDEFINED';
l_sts KU$_STATUS;
v_logs ku$_LogEntry;
v_row PLS_INTEGER;
v_job_state varchar2(4000);
BEGIN
l_dp_handle := DBMS_DATAPUMP.open(operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
version => 'LATEST');
DBMS_DATAPUMP.add_file(handle => l_dp_handle,
filename => 'test.dmp',
directory => 'SCRATCH',
reusefile => 1);
DBMS_DATAPUMP.add_file(handle => l_dp_handle,
filename => 'test.log',
directory => 'SCRATCH',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
reusefile => 1);
DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_LIST', '''ALIGNE''');
DBMS_DATAPUMP.DATA_FILTER(handle => l_dp_handle,
name => 'INCLUDE_ROWS',
value => 0);
dbms_datapump.set_debug(debug_flags => to_number('1FF0300',
'XXXXXXXXXXXXX'),
version_flag => 1);
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.WAIT_FOR_JOB(l_dp_handle, v_job_state);
DBMS_OUTPUT.PUT_LINE(v_job_state);
EXCEPTION
WHEN OTHERS THEN
dbms_datapump.get_status(NULL, 8, 0, v_job_state, l_sts);
v_logs := l_sts.error;
v_row := v_logs.FIRST;
LOOP
EXIT WHEN v_row IS NULL;
dbms_output.put_line('logLineNumber=' || v_logs(v_row).logLineNumber);
dbms_output.put_line('errorNumber=' || v_logs(v_row).errorNumber);
dbms_output.put_line('LogText=' || v_logs(v_row).LogText);
v_row := v_logs.NEXT(v_row);
END LOOP;
RAISE;
END;
The user just needs create table rights and it will work. This is very useful for when datapump isn;t telling you what is really wrong.
Comments
Post a Comment