Had this today which was difficult to track down - running the following code
DECLAREl_dp_handle
l_last_job_state
l_job_state
l_sts KU$_STATUS;NUMBER;VARCHAR2(30) := 'UNDEFINED';VARCHAR2(30) := 'UNDEFINED';BEGINl_dp_handle := DBMS_DATAPUMP.open(
operation =>
job_mode =>
remote_link =>
job_name =>
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename =>
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename =>
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.detach(l_dp_handle);'EXPORT','SCHEMA',NULL,'EMP_EXPORT26',version => 'LATEST');'TEST.dmp',directory => 'TEST_DIR','TEST.log',directory => 'TEST_DIR',END
/
Was getting the ORA-39001 error, the account I created had connect,resource and had created the directory itself.
Running with a DBA account ran fine.
It turned out it was the old gotcha problem with directories - even through the account i created was the one that created the directory it didn't have privileges on it as all directories end up owned by SYS.
To FIX:
GRANT ALL ON DIRECTORY XXX to user;;
DECLAREl_dp_handle
l_last_job_state
l_job_state
l_sts KU$_STATUS;NUMBER;VARCHAR2(30) := 'UNDEFINED';VARCHAR2(30) := 'UNDEFINED';BEGINl_dp_handle := DBMS_DATAPUMP.open(
operation =>
job_mode =>
remote_link =>
job_name =>
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename =>
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename =>
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.detach(l_dp_handle);'EXPORT','SCHEMA',NULL,'EMP_EXPORT26',version => 'LATEST');'TEST.dmp',directory => 'TEST_DIR','TEST.log',directory => 'TEST_DIR',END
/
Was getting the ORA-39001 error, the account I created had connect,resource and had created the directory itself.
Running with a DBA account ran fine.
It turned out it was the old gotcha problem with directories - even through the account i created was the one that created the directory it didn't have privileges on it as all directories end up owned by SYS.
To FIX:
GRANT ALL ON DIRECTORY XXX to user;;
Comments
Post a Comment