我正在使用PL / SQL过程使用数据泵在不同模式的某些表中保存旧数据 . 我正在尝试将所有内容集中在管理架构上 . 在同一模式（例如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.
任何的想法？谢谢