PL/SQL DBMS_DATAPUMP ОШИБКА ORA-39109 Даже с установленной ролью EXP_FULL_DATABASE
Я использую процедуру PL/SQL для сохранения, используя datapump, старых данных в некоторых таблицах на разных схемах. Я пытаюсь централизовать все на схеме управления. Все работает нормально в той же схеме (например, HR), но процедура вызывает ошибку ORA-39109 при попытке экспортировать таблицы в другие схемы. Как SYS, я установил роль EXP_FULL_DATABASE и явно установил привилегию CREATE TABLE без удачи. Я также установил все привилегии под ролью EXP_FULL_DATABASE, но все еще безуспешно.
Это хранимая процедура:
create or replace PROCEDURE exp_tables_w_qfilter
(
p_schema_name IN VARCHAR2, -- schema name
p_table_name IN VARCHAR2, -- source tables
p_table_filter IN VARCHAR2 DEFAULT NULL -- table predicate to filter data (where clause)
)
IS
v_sid VARCHAR2(200) := 'DB11G'; -- sid for this database
v_handle NUMBER; -- job handle
v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
v_start_time DATE; -- start time for log file
v_logfile_name VARCHAR2(200); -- logfile name
v_dumpfile_name VARCHAR2(200); -- logfile name
v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
v_line_no INTEGER := 0; -- debug line no
v_sqlcode NUMBER; -- sqlcode
v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
vc_job_mode CONSTANT VARCHAR2(200) := 'TABLE'; -- Job mode
BEGIN
v_line_no := 100; -- debug line no
-- Ceate the log and dumpfile names
IF v_compatible = 'COMPATIBLE'
THEN
v_logfile_name := 'expdp_' || v_sid || '_' || p_table_name || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || p_table_name || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.dmp';
ELSE
v_logfile_name := 'expdp_' || v_sid || '_' || p_table_name || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.log';
v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || p_table_name || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.dmp';
END IF;
v_line_no := 150; -- debug line no
-- Open the job
BEGIN
v_handle :=
DBMS_DATAPUMP.open(operation => 'EXPORT',
job_mode => vc_job_mode,
job_name => 'EXPORT_' || vc_job_mode || '_' || p_table_name || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI'),
version => v_compatible);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
RAISE;
END;
v_line_no := 200; -- debug line no
-- Add a logfile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => v_logfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
v_line_no := 400; -- debug line no
-- Add a datafile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => 'dp' || '_%U_' || v_dumpfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
v_line_no := 500; -- debug line no
-- Filter for the schema
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
v_line_no := 550; -- debug line no
-- Filter for the table
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_name || '''');
v_line_no := 570; -- debug line no
-- Add a subquery
DBMS_OUTPUT.put_line('SCHEMA....:'||p_schema_name);
DBMS_OUTPUT.put_line('TABLE.....:'||p_table_name);
DBMS_OUTPUT.put_line('FILTER....:'||p_table_filter);
DBMS_DATAPUMP.data_filter(handle => v_handle, name => 'SUBQUERY', VALUE => p_table_filter);
v_line_no := 600; -- debug line no
-- Get the start time
v_start_time := SYSDATE;
-- Add a start time to the log file
DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);
v_line_no := 700; -- debug line no
-- Start the job
DBMS_DATAPUMP.start_job(handle => v_handle);
DBMS_DATAPUMP.detach(handle => v_handle);
v_line_no := 800; -- debug line no
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
RAISE;
END exp_tables_w_qfilter; -- Procedure exp_tables_w_qfilter
Это гранты, которые я дал моей схеме управления
create session,
create table,
create procedure,
datapump_exp_full_database,
exp_full_database,
datapump_imp_full_database,
imp_full_database
Это привилегии под ролью EXP_FULL_DATABASE, которую я явно дал управляющему пользователю.
READ ANY FILE GROUP
,RESUMABLE
,EXECUTE ANY PROCEDURE
,EXECUTE ANY TYPE
,SELECT ANY TABLE
,ADMINISTER SQL MANAGEMENT OBJECT
,ADMINISTER RESOURCE MANAGER
,BACKUP ANY TABLE
,CREATE SESSION
,SELECT ANY SEQUENCE
,CREATE TABLE
Я запускаю процедуру с;
DECLARE
CURSOR c_dp_tables is
SELECT owner,
tbname,
tsfield,
m2keep
FROM dp_tables;
v_history number;
v_query varchar(255);
v_field varchar(35);
BEGIN
FOR dptbl_rec IN c_dp_tables LOOP
v_history := dptbl_rec.m2keep;
v_field := dptbl_rec.tsfield;
v_query := 'where '||v_field || ' < add_months(sysdate, -'||v_history||')';
exp_tables_w_qfilter(dptbl_rec.owner, dptbl_rec.tbname, v_query);
END LOOP;
END;
Выход
Report error -
ORA-31631: sono necessari appositi privilegi
ORA-06512: a "HR.EXP_TABLES_W_QFILTER", line 117
ORA-06512: a line 19
31631. 00000 - "privileges are required"
*Cause: The necessary privileges are not available for operations such
as: restarting a job on behalf of another owner, using a device
as a member of the dump file set, or ommiting a directory
object associated with any of the various output files.
Refer to any following error messages for additional information.
*Action: Select a different job to restart, try a different operation, or
contact a database administrator to acquire the needed privileges.
В DATA_PUMP_DIR я могу найти файл журнала, говорящий:
$ more expdp_DB11G_ACCOUNT_ANALYTIC_LINE_2017_0614_1214.log
ORA-39109: Gli utenti privi di privilegi non possono utilizzare gli schemi degli altri utenti
ORA-39012: Client scollegato prima dell'avvio del job.
Любая идея? Спасибо