前景:
EBS XTTS跨平台迁移,需对大表FND_LOBS单独备份下,采用create table fnd_lobs_bk select * from fnd_lobs 报01555快照太旧
下列采用每1000行为一批,循环插入新表,避免报01555快照太旧
--创建空表
--create table apps.fnd_lobs_bk as select * from fnd_lobs where 1=2;
--循环插入(1000行提交一次)
declare
--声明集合类型
type file_id_type is table of APPS.FND_LOBS.file_id%type;
type file_name_type is table of APPS.FND_LOBS.file_name%type;
type file_content_type_type is table of APPS.FND_LOBS.file_content_type%type;
type file_data_type is table of APPS.FND_LOBS.file_data%type;
type upload_date_type is table of APPS.FND_LOBS.upload_date%type;
type expiration_date_type is table of APPS.FND_LOBS.expiration_date%type;
type program_name_type is table of APPS.FND_LOBS.program_name%type;
type program_tag_type is table of APPS.FND_LOBS.program_tag%type;
type language_type is table of APPS.FND_LOBS.language%type;
type oracle_charset_type is table of APPS.FND_LOBS.oracle_charset%type;
type file_format_type is table of APPS.FND_LOBS.file_format%type;
--声明变量
v_file_id file_id_type;
v_file_name file_name_type;
v_file_content_type file_content_type_type;
v_file_data file_data_type;
v_upload_date upload_date_type;
v_expiration_date expiration_date_type;
v_program_name program_name_type;
v_program_tag program_tag_type;
v_language language_type;
v_oracle_charset oracle_charset_type;
v_file_format file_format_type;
cursor cur_fnd_lobs is select * from APPS.FND_LOBS;
begin
open cur_fnd_lobs;
loop
exit when cur_fnd_lobs%notfound;
fetch cur_fnd_lobs bulk collect into v_file_id,v_file_name,v_file_content_type,v_file_data,v_upload_date,v_expiration_date,v_program_name,v_program_tag,v_language,v_oracle_charset,v_file_format limit 1000;
for i in 1..v_file_id.count loop
insert into APPS.FND_LOBS_BK(file_id,file_name,file_content_type,file_data,upload_date,expiration_date,program_name,program_tag,language,oracle_charset,file_format) values (v_file_id(i),v_file_name(i),v_file_content_type(i),v_file_data(i),v_upload_date(i),v_expiration_date(i),v_program_name(i),v_program_tag(i),v_language(i),v_oracle_charset(i),v_file_format(i));
end loop;
commit;
end loop;
close cur_fnd_lobs;
end;
/
