在Oracle数据库里用存储过程定期分割表

create or replace procedure rename_table

(source_name in varchar2,

target_name in varchar2,

times in out number)

is

query_str varchar2(4000);

source_name1 varchar2(64);

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);

times number;

begin

select to_char(sysdate-15,'YYYYMM') into year_month from dual;

times:=0;

query_str :='create table log_new pctfree 10 pctused 80 

as select * from log where 1=2';

execute immediate query_str;

query_str :='alter table log_new add constraints log_'||year_month||'_pk

primary key (id) tablespace indx nologging pctfree 10';

execute immediate query_str; 

query_str :='alter table log_his modify logtime default sysdate';

execute immediate query_str; 

query_str :='create index log_'||year_month||'_logtime on log(logtime)

tablespace indx nologging pctfree 10';

execute immediate query_str; 

rename_table('log','log'||year_month,times);

query_str :='alter table log_new rename to log';

execute immediate query_str;

end;

/

select object_id,session_id,locked_mode from v$locked_object;

select t2.username,t2.sid,t2.serial#,t2.logon_time 

from v$locked_object t1,v$session t2 

where t1.session_id=t2.sid order by t2.logon_time;

alter system kill session 'sid,serial#';