Backup tables for oracle

create or replace procedure sp_backuptable(v_nowtablename varchar2, --product table name
v_baktablename varchar2, --backup table name,like test not like zxdbp_vpbx.test
v_nowkeepdaynum int, --keep day number of product table records
v_bakkeepdaynum int, --keep day number of backup table records
v_datefield varchar2, --field culumn
v_datefieldformat varchar2, --date format of field culumn
v_dopernum int, --time interval of bach commition,the unit is second
v_extcond varchar2 --extention condition for query field
) as
v_nowkeeptodate varchar2(300); --keep date of product table records
v_bakkeeptodate varchar2(300); --keep date of product table records
v_strcheck varchar2(2000);
v_strsql varchar2(8000);
v_tmpbegindate varchar2(500);
v_tmpnowbegindate varchar2(300);
v_tmpnowenddate varchar2(300);
v_tmpbakbegindate varchar2(300);
v_tmpbakenddate varchar2(300);

v_exsistpbase varchar2(300);
v_exsistpnew varchar2(300);
v_p varchar2(300);
v_seq number;

v_change_dopernum int;
v_change_datefieldformat varchar2(300);
v_count int;
v_datefieldischar int; --if the date format is character format

cursor cur_plist is
select PARTITION_NAME
from user_tab_partitions
where table_name = upper(v_baktablename) and PARTITION_NAME< 'P'||to_char(trunc(sysdate-v_bakkeepdaynum,'month'),'yyyymm') and PARTITION_NAME != 'PBASE';

begin
select backup_seq.nextval into v_seq from dual;
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'sp_backuptable('
||''''||v_nowtablename||''','
||''''||v_baktablename||''','
||v_nowkeepdaynum||','
||v_bakkeepdaynum||','
||''''||v_datefield||''','
||''''||v_datefieldformat||''','
||v_dopernum||','
||''''||v_extcond||''''
||').');
commit;
--begin to check parameters
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Check parameters.');
commit;
v_change_dopernum := v_dopernum;
if (v_datefieldformat not in
('datetime', 'yyyy.mm.dd', 'yyyy-mm-dd', 'yyyy/mm/dd', 'yyyymmdd',
'yyyy.mm.dd hh', 'yyyy-mm-dd hh', 'yyyy/mm/dd hh', 'yyyymmddhh',
'yyyy.mm.dd hh:mi:ss', 'yyyy-mm-dd hh:mi:ss', 'yyyy/mm/dd hh:mi:ss',
'yyyymmddhhmiss')) then
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Error:date format is wrong.');
commit;
return;
end if;

select count(1)
into v_count
from user_tables
where table_name = upper(v_nowtablename)
and rownum < 2;
if v_count <= 0 then
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Error:product table does not exists.');
commit;
return;
end if;

select count(1)
into v_count
from user_tables
where table_name = upper(v_baktablename)
and rownum < 2;
if v_count <= 0 then
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Error:backup table does not exists.');
commit;
return;
end if;

select count(1)
into v_count
from user_tab_columns
where table_name = upper(v_nowtablename)
and column_name = upper(v_datefield)
and rownum < 2;
if v_count <= 0 then
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Error:column does not exists.');
commit;
return;
end if;

if v_nowkeepdaynum < 0 then
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Error:keep day number of product table is less than zero.');
commit;
return;
end if;
if v_bakkeepdaynum < 0 then
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_bakkeepdaynum,'Error:keep day number of backup table is less than zero.');
commit;
return;
end if;
if v_change_dopernum <= 0 then
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Error:commit time interval is less than zero.');
commit;
return;
end if;

select replace(v_datefieldformat, 'hh', 'hh24')
into v_change_datefieldformat
from dual;
v_datefieldischar := 1;
if (v_datefieldformat = 'datetime') then
v_tmpbegindate := 'nvl(min(' || v_datefield || '),sysdate)';
v_tmpnowbegindate := 'v_nowbegindate';
v_tmpnowenddate := 'v_nowenddate';
v_tmpbakbegindate := 'v_bakbegindate';
v_tmpbakenddate := 'v_bakenddate';
v_datefieldischar := 0;
else
if (v_datefieldformat in
('yyyy.mm.dd', 'yyyy/mm/dd', 'yyyy-mm-dd', 'yyyymmdd')) then
v_change_dopernum := 1440;
elsif (v_datefieldformat in
('yyyy.mm.dd hh', 'yyyy/mm/dd hh', 'yyyy-mm-dd hh', 'yyyymmddhh')) then
v_change_dopernum := 60;
end if;

v_tmpbegindate := 'to_date(nvl(min(' || v_datefield ||
'),to_char(sysdate,''' || v_change_datefieldformat ||
''')),''' || v_change_datefieldformat || ''')';
v_tmpnowbegindate := 'to_char(v_nowbegindate,''' ||
v_change_datefieldformat || ''')';
v_tmpnowenddate := 'to_char(v_nowenddate,''' ||
v_change_datefieldformat || ''')';
v_tmpbakbegindate := 'to_char(v_bakbegindate,''' ||
v_change_datefieldformat || ''')';
v_tmpbakenddate := 'to_char(v_bakenddate,''' ||
v_change_datefieldformat || ''')';

end if;

--keep date
v_nowkeeptodate := 'trunc(sysdate)-' || v_nowkeepdaynum;
v_bakkeeptodate := 'trunc(sysdate)-' || v_bakkeepdaynum;

--check format sql
v_strcheck := '';
if v_datefieldischar = 0 then
v_strcheck := '
v_count :=0;
select count(1) into v_count from ' || v_nowtablename ||
' where rownum<2;
if v_count = 0 then
select count(1) into v_count from ' ||
v_baktablename || ' where rownum<2;
if v_count = 0 then
return;
else
select ' || v_datefield ||
' into v_checkdate from ' || v_baktablename ||
' where rownum<2;
end if;
else
select ' || v_datefield ||
' into v_checkdate from ' || v_nowtablename || ' where rownum<2;
end if;

if v_checkdate is null then
return;
end if;
if v_checkdate <> to_date(to_char(v_checkdate,
''yyyymmddhh24miss''),
''yyyymmddhh24miss'') then
insert into backup_log(operation,prodtablename,msg) values('||v_seq||','''||v_nowtablename||''',''Error:date format is wrong.'');
commit;
return;
end if;';
else
v_strcheck := '
v_count :=0;
select count(1) into v_count from ' || v_nowtablename ||
' where rownum<2;
if v_count = 0 then
select count(1) into v_count from ' || v_baktablename ||
' where rownum<2;
if v_count = 0 then
return;
else
select ' || v_datefield ||
' into v_strcheckdate from ' || v_baktablename ||
' where rownum<2;
end if;
else
select ' || v_datefield || ' into v_strcheckdate from ' ||
v_nowtablename || ' where rownum<2;
end if;

if v_strcheckdate is null then
return;
end if;
if v_strcheckdate <> to_char(to_date(v_strcheckdate,
''' || v_change_datefieldformat || '''),
''' || v_change_datefieldformat || ''') then
insert into backup_log(operation,prodtablename,msg) values('||v_seq||','''||v_nowtablename||''',''Error:date format is wrong.'');
commit;
return;
end if;';
end if;

--begin add new partitions.
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Add new partitions.');
commit;
select count(*) into v_exsistpbase from user_tab_partitions where table_name = upper(v_baktablename) and partition_name = 'PBASE';
select count(*) into v_exsistpnew from user_tab_partitions where table_name = upper(v_baktablename) and partition_name = 'P'|| to_char(add_months(trunc(sysdate-v_nowkeepdaynum,'month'),1),'yyyymm');
if ( v_exsistpbase = 1 and v_exsistpnew = 0 ) then
begin
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Add partition p'||lower(to_char(add_months(trunc(sysdate-v_nowkeepdaynum,'month'),1),'yyyymm'))||'.');
commit;
if (v_datefieldformat = 'datetime') then
v_strsql := ' alter table '||v_baktablename||' split partition pbase at (to_date('''
|| to_char(add_months(add_months(trunc(sysdate-v_nowkeepdaynum,'month'),1),1),'yyyymm')||''',''yyyymm''))'
||' INTO'
||' (partition P' || to_char(add_months(trunc(sysdate-v_nowkeepdaynum,'month'),1),'yyyymm')
||' ,partition PBASE)';
else
v_strsql := ' alter table '||v_baktablename||' split partition pbase at ('''
||to_char(add_months(add_months(trunc(sysdate-v_nowkeepdaynum,'month'),1),1),v_change_datefieldformat)||''')'
||' INTO'
||' (partition P' || to_char(add_months(trunc(sysdate-v_nowkeepdaynum,'month'),1),'yyyymm')
||' ,partition PBASE)';
end if;
begin
execute immediate v_strsql;
exception when others then
begin
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Error:execute dynamic sql '||substr(v_strsql,1,3900));
commit;
return;
end;
end;
end;
end if;
--end of add new partitions.

--begin to move data from product table to backup table.
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Move data,keep data in table '
||v_nowtablename||' from time '||to_char(sysdate-v_nowkeepdaynum,'yyyymmddhh24miss')||' to now'
||',keep data in table '
||v_baktablename||' from time '||to_char(trunc(sysdate-v_bakkeepdaynum,'month'),'yyyymmddhh24miss')||' to '||to_char(sysdate-v_nowkeepdaynum,'yyyymmddhh24miss')||'.');
commit;
v_strsql := 'declare
v_nowbegindate date;
v_nowenddate date;
v_bakbegindate date;
v_bakenddate date;
v_rows int;
v_checkdate date;
v_strcheckdate varchar2(100);
v_count int;
begin
' || v_strcheck || '
select ' || v_tmpbegindate || ' into v_nowbegindate from ' ||
v_nowtablename || ' where 1=1 ' || v_extcond || ';
v_nowenddate := v_nowbegindate+' || v_change_dopernum ||
'/1440;
if v_nowenddate>' || v_nowkeeptodate || ' then
v_nowenddate:=' || v_nowkeeptodate || ';
end if;
v_bakenddate:=' || v_bakkeeptodate || ';
while (v_nowbegindate < ' || v_nowkeeptodate ||
') loop
insert into ' || v_baktablename || ' select * from ' ||
v_nowtablename || '
where ' || v_datefield || '>=' || v_tmpnowbegindate || '
and ' || v_datefield || '<=' || v_tmpnowenddate || ' ' ||
v_extcond || ';
delete from ' || v_nowtablename || ' where ' || v_datefield || '>=' ||
v_tmpnowbegindate || '
and ' || v_datefield || '<=' || v_tmpnowenddate || ' ' ||
v_extcond || ';
commit;
select ' || v_tmpbegindate || '
into v_nowbegindate from
' || v_nowtablename || ' where 1=1
' || v_extcond || ';
v_nowenddate := v_nowbegindate+' || v_change_dopernum ||
'/1440;
if v_nowenddate>' || v_nowkeeptodate || ' then
v_nowenddate:=' || v_nowkeeptodate || ';
end if;
end loop;
end;';

begin
execute immediate v_strsql;
exception when others then
begin
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Error:execute dynamic sql '||substr(v_strsql,1,3900));
commit;
return;
end;
end;
--end of move data from product table to backup table.

--begin to drop the old partitions.
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Drop the old partitions.');
commit;
open cur_plist;
loop
fetch cur_plist into v_p;
exit when cur_plist%notfound;
begin
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Drop partition '||lower(v_p)||'.');
commit;
begin
execute immediate 'alter table '||v_baktablename||' drop partition ' || v_p; -- drop the partition.
exception when others then
begin
insert into backup_log(operation,prodtablename,msg) values(v_seq,v_nowtablename,'Error:execute dynamic sql '||substr(v_strsql,1,3900));
commit;
return;
end;
end;
end;
end loop;
close cur_plist;
--end of drop the old partitions.
end;
/