重命名原始表到目标表的存储过程

1.重命名原始表到目标表的存储过程rename_table:

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;

2.截断分割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;

3.用户需要有create any table系统权限

         因为在执行存储过程时，由角色赋予的权限会失效, 所以执行log_history的用户一定要有DBA单独赋予的

         create any table系统权限。

         最后在OS里定时每月一号凌晨0:00分执行log_history，让存储过程定期分割表。

         如果要分割的日志表很多，模仿log_history可以写很多类似的存储过程来分割不同项目里的日志表。

         然后让OS按月，按周或者不定期地执行这些存储过程, 管理员只要查看日志就可以了。

4. 下面SQL语句杀掉长期没有释放非正常的锁

alter system kill session 'sid,serial#';