用例：客户将在DMP文件中发送其DB的备份。这就是他们要发送的全部。
我正在使用DBMS_DATAPUMP构建一个将将该DMP文件导入Oracle的脚本。但是，由于我不知道原始的表空间名称，我会收到以下错误：ORA-39083：对象类型用户：" xxxxx"无法使用错误创建：ORA-00959：表空间'xxxxxxx'不存在
这是我的PL/SQL过程：
PROCEDURE IMPORTING
(
DMPFILES IN VARCHAR2,
FROMSCHEMA IN VARCHAR2,
TOSCHEMA IN VARCHAR2
) AS
ind NUMBER; -- Loop index
h1 NUMBER; -- Data Pump job handle
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- To keep track of job state
le ku$_LogEntry; -- For WIP and error messages
js ku$_JobStatus; -- The job status from get_status
jd ku$_JobDesc; -- The job description from get_status
sts ku$_Status; -- The status object returned by get_status
array apex_application_global.vc_arr2;
BEGIN
h1 := DBMS_DATAPUMP.open('IMPORT','FULL',NULL,'EXAMPLE1');
dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
-- usign this function to split the files passed as a String to an array
array := apex_util.string_to_table(DMPFILES, ',');
for i in 1 .. array.count loop
DBMS_DATAPUMP.ADD_FILE(h1,array(i),'DATA_PUMP_DIR');
end loop;
DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_SCHEMA',FROMSCHEMA,TOSCHEMA);
dbms_datapump.set_parameter(handle => h1, name =>
'INCLUDE_METADATA', value => 1);
dbms_datapump.set_parameter(handle => h1, name =>
'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
dbms_datapump.set_parameter(handle => h1, name =>
'REUSE_DATAFILES', value => 0);
dbms_datapump.set_parameter(handle => h1, name =>
'SKIP_UNUSABLE_INDEXES', value => 0);
DBMS_DATAPUMP.START_JOB(h1);
-- The import job should now be running. In the following loop, the job is
-- monitored until it completes. In the meantime, progress information is
-- displayed. Note: this is identical to the export example.
percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;
-- If the percentage done changed, display the new value.
if js.percent_done != percent_done
then
dbms_output.put_line('*** Job percent done = ' ||
to_char(js.percent_done));
percent_done := js.percent_done;
end if;
-- If any work-in-progress (WIP) or Error messages were received for the job,
-- display them.
if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
then
le := sts.wip;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
else
le := null;
end if;
end if;
if le is not null
then
ind := le.FIRST;
while ind is not null loop
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;
-- Indicate that the job finished and gracefully detach from it.
dbms_output.put_line('Job has completed');
dbms_output.put_line('Final job state = ' || job_state);
dbms_datapump.detach(h1);
END IMPORTING;
基本上我的问题是：有没有办法将DMP文件导入Oracle不知道原始表空间...？
事先感谢您的帮助。
更新：我发现使用此脚本时，我还需要知道模式名称。因此，这将在脚本上进行以下更改：
PROCEDURE IMPORTING
(
DMPFILES IN VARCHAR2,
FROMSCHEMA IN VARCHAR2,
TOSCHEMA IN VARCHAR2,
FROMTABLESPACE IN VARCHAR2,
TOTABLESPACE IN VARCHAR2
) AS
ind NUMBER; -- Loop index
h1 NUMBER; -- Data Pump job handle
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- To keep track of job state
le ku$_LogEntry; -- For WIP and error messages
js ku$_JobStatus; -- The job status from get_status
jd ku$_JobDesc; -- The job description from get_status
sts ku$_Status; -- The status object returned by get_status
array apex_application_global.vc_arr2;
BEGIN
h1 := DBMS_DATAPUMP.open('IMPORT','FULL',NULL,'EXAMPLE1');
dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
-- usign this function to split the files passed as a String to an array
array := apex_util.string_to_table(DMPFILES, ',');
for i in 1 .. array.count loop
DBMS_DATAPUMP.ADD_FILE(h1,array(i),'DATA_PUMP_DIR');
end loop;
DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_TABLESPACE',FROMTABLESPACE,TOTABLESPACE);
DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_SCHEMA',FROMSCHEMA,TOSCHEMA);
dbms_datapump.set_parameter(handle => h1, name =>
'INCLUDE_METADATA', value => 1);
dbms_datapump.set_parameter(handle => h1, name =>
'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
dbms_datapump.set_parameter(handle => h1, name =>
'REUSE_DATAFILES', value => 0);
dbms_datapump.set_parameter(handle => h1, name =>
'SKIP_UNUSABLE_INDEXES', value => 0);
DBMS_DATAPUMP.START_JOB(h1);
-- The import job should now be running. In the following loop, the job is
-- monitored until it completes. In the meantime, progress information is
-- displayed. Note: this is identical to the export example.
percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;
-- If the percentage done changed, display the new value.
if js.percent_done != percent_done
then
dbms_output.put_line('*** Job percent done = ' ||
to_char(js.percent_done));
percent_done := js.percent_done;
end if;
-- If any work-in-progress (WIP) or Error messages were received for the job,
-- display them.
if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
then
le := sts.wip;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
else
le := null;
end if;
end if;
if le is not null
then
ind := le.FIRST;
while ind is not null loop
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;
-- Indicate that the job finished and gracefully detach from it.
dbms_output.put_line('Job has completed');
dbms_output.put_line('Final job state = ' || job_state);
dbms_datapump.detach(h1);
END IMPORTING;
我用导出的DMP测试了这一点，因此我知道表空间和模式名称。
摘要：从DMP文件中了解表空间名称和架构名称的任何方法？
谢谢。
简短答案：否
长答案：是如果生成了DDL文件，则实际上可以抓住Fille并搜索表空间和用户。请参阅Bellow脚本生成DDL文件：
create or replace
PROCEDURE GENERATE_SQL
(
DMPFILES IN VARCHAR2
, SQLFILENAME IN VARCHAR2
, VERSIONPARAM IN VARCHAR2
, JOBNAME IN VARCHAR2
) AS
h1 number;
array apex_application_global.vc_arr2;
ind NUMBER; -- Loop indexdasdads
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- To keep track of job state
le ku$_LogEntry; -- For WIP and error messages
js ku$_JobStatus; -- The job status from get_status
jd ku$_JobDesc; -- The job description from get_status
sts ku$_Status; -- The status object returned by get_status
BEGIN
h1 := dbms_datapump.open(
operation => 'SQL_FILE',
job_mode => 'SCHEMA',
remote_link => null,
job_name => JOBNAME,
version => VERSIONPARAM
);
array := apex_util.string_to_table(DMPFILES, ',');
for i in 1 .. array.count loop
dbms_output.put_line('array(i): ' || array(i));
DBMS_DATAPUMP.ADD_FILE(h1,array(i),'DBRAT_DMP',dbms_datapump.ku$_file_type_dump_file);
end loop;
dbms_output.put_line('datapump_job: ' || h1);
dbms_datapump.add_file(
handle => h1,
filename => SQLFILENAME,
directory => 'DBRAT_DMP',
filetype => dbms_datapump.ku$_file_type_sql_file);
dbms_datapump.start_job(
handle => h1,
skip_current => 0,
abort_step => 0);
percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;
-- If the percentage done changed, display the new value.
if js.percent_done != percent_done
then
dbms_output.put_line('*** Job percent done = ' ||
to_char(js.percent_done));
percent_done := js.percent_done;
end if;
-- If any work-in-progress (WIP) or Error messages were received for the job,
-- display them.
if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
then
le := sts.wip;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
else
le := null;
end if;
end if;
if le is not null
then
ind := le.FIRST;
while ind is not null loop
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;
-- Indicate that the job finished and gracefully detach from it.
dbms_output.put_line('Job has completed');
dbms_output.put_line('Final job state = ' || job_state);
dbms_datapump.detach(h1);
END GENERATE_SQL;