创建备份恢复测试环境

描述: 本环境在scott schema下创建,目的创建一时间基本表second_scheduler, 每小时自动分区. 并创建scheduler每6小时删除分区表6小时前的分区.数据库版本11.2.0.4.0 on linux

grant create job to scott;


------------------------------------------------------------------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


1 创建时间分区表


drop table second_scheduler; 
  
 create table second_scheduler (time_s date)
partition by range (time_s) interval (NUMTODSINTERVAL(1,'HOUR'))
(
partition p1 values less than (to_date('2016-11-14 16:00:00','yyyy-mm-dd hh24:mi:ss'))
);  




------------------------------------------------------------------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2 创建自动插入时间数据procedure


create or replace procedure insert_second_scheduler
as
begin
insert into scott.second_scheduler values(sysdate);
commit;
end;
/


begin
 dbms_scheduler.create_job (
 job_name => 'job_insert_second_scheduler',
 job_type => 'STORED_PROCEDURE',
 job_action => 'insert_second_scheduler',
 start_date => sysdate,
 repeat_interval => 'FREQ=secondly; INTERVAL=1;',
 comments => 'backup test'
 );
 end;
 /


 exec dbms_scheduler.enable('job_insert_second_scheduler'); 
  exec dbms_scheduler.disable('job_insert_second_scheduler'); 
  
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------------------------------------------------------------------




  
 
  
------------------------------------------------------------------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  
3   Oracle 11g自动创建分区（INTERVAL PATITION）后的定时删分区  procedure 
   
create or replace procedure pro_drop_partitions(tablename varchar2,drop_before_hours number default 6)
as
v_SqlExec varchar2(2000); --DDL语句变量
V_Err_Num number; --ORA error number
V_Err_Msg varchar2(100); --error message


cursor cursor_table_parts is
  select t.subobject_name partition_name
  from user_objects t
  where object_name = upper(tablename)
  and t.object_type='TABLE PARTITION'
  and t.GENERATED='Y'
  and t.CREATED < (sysdate-drop_before_hours/24)
  order by t.created;  
 record_table_parts cursor_table_parts%rowType;
 
Begin
--开户游标循环获取分区名
open cursor_table_parts;
loop
fetch cursor_table_parts into record_table_parts;
exit when cursor_table_parts%notFound;
--显示并执行删除分区命令语句
v_SqlExec := 'alter table '||tablename||' drop partition '||record_table_parts.partition_name;
dbms_output.put_line('execute command : '||v_SqlExec);
dbms_utility.exec_ddl_statement(v_SqlExec);
end loop;
close cursor_table_parts;
exception
when others then
V_Err_Num := SQLCODE;
V_Err_Msg := SUBSTR(SQLERRM,1,100);
dbms_output.put_line('pro_drop_partitions 执行出现异常,错误码 : '||V_Err_Num||'错误描述 : '||V_Err_Msg);
END pro_drop_partitions;
/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------------------------------------------------------------------

 exec dbms_scheduler.enable('job_insert_second_scheduler'); 
  exec dbms_scheduler.disable('job_insert_second_scheduler'); 
  
   
  
------------------------------------------------------------------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4 创建自动删除分区job


exec dbms_scheduler.drop_job(job_name => 'JOB_DROP_SS_PARTITIONS');
exec dbms_scheduler.drop_program(program_name        => 'second_scheduler_PROGRAM');


BEGIN
  dbms_scheduler.create_program(program_name        => 'second_scheduler_PROGRAM',
                                program_type        => 'STORED_PROCEDURE',
                                program_action      => 'pro_drop_partitions',
                                number_of_arguments => 2,
                                enabled             => FALSE,
                                comments            => 'backup test');
  --声明输入参数
  dbms_scheduler.define_program_argument(program_name      => 'second_scheduler_PROGRAM',
                                         argument_name     => 'tablename',
                                         argument_position => 1,
                                         argument_type     => 'VARCHAR2',
                                         default_value     => 'second_scheduler');


  dbms_scheduler.define_program_argument(program_name      => 'second_scheduler_PROGRAM',
                                         argument_name     => 'drop_before_hours',
                                         argument_position => 2,
                                         argument_type     => 'number',
                                         default_value     => '6');


  dbms_scheduler.enable(NAME => 'second_scheduler_PROGRAM');


dbms_scheduler.create_job (
job_name => 'job_drop_ss_partitions',
program_name    => 'second_scheduler_PROGRAM',
start_date => sysdate,
repeat_interval => 'FREQ=HOURLY; INTERVAL=6;',
comments => 'backup test'
); 


  --给参数赋值
  dbms_scheduler.set_job_argument_value(job_name          => 'JOB_DROP_SS_PARTITIONS',
                                        argument_position => 1,  
                                        argument_value    => 'second_scheduler');


  dbms_scheduler.set_job_argument_value(job_name          => 'JOB_DROP_SS_PARTITIONS',
                                        argument_position => 2,
                                        argument_value    => 2);
END;
/




begin
 dbms_scheduler.set_job_argument_value(job_name          => 'JOB_DROP_SS_PARTITIONS',
                                        argument_position => 1,  
                                        argument_value    => 'second_scheduler');


 dbms_scheduler.set_job_argument_value(job_name          => 'JOB_DROP_SS_PARTITIONS',
                                        argument_position => 2,
                                        argument_value    => 6);
 dbms_scheduler.enable('JOB_DROP_SS_PARTITIONS');
end;
/ 


 
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11780477/viewspace-2128629/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11780477/viewspace-2128629/