初体验Oracle11g分区
--创建表create table us_pwdwronglimit
(
msisdn varchar2(11) not null,
limitdate date not null,
times number(3) not null
)
partition by range (limitdate)
interval (numtodsinterval(1,'day'))
(
partition p1 values less than (to_date('2011-01-01','yyyy-mm-dd')) tablespace tbs_mread_dat
);
-- Add comments to the columns
comment on column us_pwdwronglimit.msisdn
is '手机号码';
comment on column us_pwdwronglimit.limitdate
is '限制日期';
comment on column us_pwdwronglimit.times
is '错误次数';
create indexus_pwdwronglimit_local on us_pwdwronglimit(msisdn) tablespace tbs_mread_idx local;
--删除分区数据业务
CREATE OR REPLACE PROCEDURE PRO_PWDWRONGLIMIT_CTRL
is
names varchar2(1024); --分区名称
dates varchar2(1024); --分区日期
temp varchar2(1024);
v_temp varchar2(1024);
v_temp_sys varchar2(1024);
v_sql varchar2(1024);
v_cs varchar2(1024);
v_error varchar2(1024);
TYPE t_ref_cursor IS REF CURSOR;
c t_ref_cursor;
--获取分区列表
begin
v_cs := 'select PARTITION_NAME,HIGH_VALUE
from USER_TAB_PARTITIONS
where TABLE_NAME = upper(''us_pwdwronglimit'') and PARTITION_NAME <> ''P1''';
open c for v_cs;
fetch c
into names,dates;
while c% found loop
--获得分区时间
v_temp := 'select to_char('||dates||',''yyyy-MM-dd'') from dual';
execute immediate v_temp into temp ;
--获取昨天
v_temp := 'select to_char(sysdate-1,''yyyy-mm-dd'') from dual';
execute immediate v_temp into v_temp_sys ;
--时间比较
if (to_date(temp,'yyyy-MM-dd') <= to_date(v_temp_sys,'yyyy-MM-dd'))
then
---删除分区
v_sql := 'alter table us_pwdwronglimit truncate partition ' ||names;
execute immediate v_sql;
end if;
--取下一条数据
fetch cinto names,dates;
end loop;
close c;
commit;
exception
when others then
v_error := sqlcode || ' - ' || sqlerrm;
prc_iread_sys_writelog (2,4,'PRO_PWDWRONGLIMIT_CTRL',v_error,'');
rollback;
end PRO_PWDWRONGLIMIT_CTRL;
/
begin
sys.dbms_scheduler.create_job(job_name => 'REG_PWDWRONGLIMIT',
job_type => 'STORED_PROCEDURE',
job_action => 'PRO_PWDWRONGLIMIT_CTRL',
start_date => to_date('01-01-2011 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
end_date => to_date(null),
job_class => 'DBMS_JOB$',
enabled => true,
auto_drop => false,
comments => '');
end;
/
页:
[1]