在ORACLE里用存储过程定期分割表

核心提示： target_name1 varchar2(64);cursor c1 is select segment_name from user_segments where segment_name=upper(source_name);dummy c1%rowtype;cursor c2 is

target_name1 varchar2(64);

cursor c1 is select segment_name from user_segments where segment_name=upper(source_name);

dummy c1%rowtype;

cursor c2 is select segment_name from user_segments where segment_name=upper(target_name);

dummy2 c2%rowtype;

begin

source_name1:=source_name;

target_name1:=target_name;

open c1;

fetch c1 into dummy;

-- if c1%found then

-- dbms_output.put_line(source_name1||'exist!');

-- end if;

open c2;

fetch c2 into dummy2;

-- if c2%notfound then

-- dbms_output.put_line(target_name1||'not exist!');

-- end if;

if c2%notfound and c1%found then

query_str :='alter table '||source_name1||' rename to '||target_name1;

execute immediate query_str;

dbms_output.put_line('rename success!');

end if;

close c1;

close c2;

exception

WHEN OTHERS THEN

times:=times+1;

if times<100 then

-- dbms_output.put_line('times:'||times);

rename_table(source_name1,target_name1,times);

else

dbms_output.put_line(SQLERRM);

dbms_output.put_line('error over 100 times,exit');

end if;

end;

/

截断分割log表的存储过程log_history:

create or replace procedure log_history

is

query_str varchar2(32767);

year_month varchar2(8);