oracle 按天分区删除吗,按天分区并通过存储过程删除历史分区

-- 按天创建分区表，并通过存储过程定期删除指定天数前的分区

/****/

/创建按天分区表 */

/****/

-- 订单订单资产快照表

drop table DTSDATA.tyebasset_beforecash_ds_self;

create table DTSDATA.tyebasset_beforecash_ds_self

(

id_yebassetself VARCHAR2(32) default sys_guid() not null,

vc_tradeacco VARCHAR2(17) not null,

vc_fundcode VARCHAR2(6) not null,

d_carryday DATE not null,

en_cday15endasset NUMBER(16,2),

en_cday24endasset NUMBER(16,2),

en_frozenbala NUMBER(16,2),

en_accumincome NUMBER(16,2),

remark VARCHAR2(200) ,

created_by VARCHAR2(100) not null,

created_date DATE not null,

updated_by VARCHAR2(100) not null,

updated_date DATE not null

)

partition by range (d_carryday)

INTERVAL (NUMTODSINTERVAL(1,'day'))

(

partition asset_self_p1 values less than (to_date('2018-01-01','yyyy-mm-dd'))

);

-- Add comments to the table

comment on table DTSDATA.tyebasset_beforecash_ds_self

is '订单订单资产快照表';

-- Add comments to the columns

comment on column DTSDATA.tyebasset_beforecash_ds_self.id_yebassetself

is '记录编号(唯一)';

comment on column DTSDATA.tyebasset_beforecash_ds_self.vc_tradeacco

is '交易账号';

comment on column DTSDATA.tyebasset_beforecash_ds_self.vc_fundcode

is '基金代码';

comment on column DTSDATA.tyebasset_beforecash_ds_self.d_carryday

is '待分配收益日期';

comment on column DTSDATA.tyebasset_beforecash_ds_self.en_cday15endasset

is 'cday截止15点资产';

comment on column DTSDATA.tyebasset_beforecash_ds_self.en_cday24endasset

is 'cday截止24点资产';

comment on column DTSDATA.tyebasset_beforecash_ds_self.en_frozenbala

is '冻结份额';

comment on column DTSDATA.tyebasset_beforecash_ds_self.en_accumincome

is '累计收益';

comment on column DTSDATA.tyebasset_beforecash_ds_self.remark

is '备注';

comment on column DTSDATA.tyebasset_beforecash_ds_self.CREATED_BY

is '录入人员';

comment on column DTSDATA.tyebasset_beforecash_ds_self.CREATED_DATE

is '创建日期';

comment on column DTSDATA.tyebasset_beforecash_ds_self.UPDATED_BY

is '更新人员';

comment on column DTSDATA.tyebasset_beforecash_ds_self.UPDATED_DATE

is '更新日期';

-- 创建主键

alter table DTSDATA.tyebasset_beforecash_ds_self add constraint PK_tyebasset_ds_self primary key (id_yebassetself) using index initrans 16 ;

-- 创建索引

create unique index DTSDATA.IDX_tyebasset_ds_self on DTSDATA.tyebasset_beforecash_ds_self (vc_tradeacco,vc_fundcode,d_carryday) local ;

-- 创建同义词、授权

create or replace public synonym tyebasset_beforecash_ds_self for dtsdata.tyebasset_beforecash_ds_self ;

grant select, insert, update, delete on DTSDATA.tyebasset_beforecash_ds_self to FDWKTL, DTSOPR, R_DTSDATA_DML;

grant select on DTSDATA.tyebasset_beforecash_ds_self to R_DTSDATA_QRY,R_DTSDATA_DEV_QRY;

/****/

/定时删除分区表历史分区 package/

/****/

create or replace package puf_dts_xxx is

procedure del_self_partition(

days in int, --保留天数

r_error_code out int, --错误代码

r_error_message out varchar2 --错误消息

);

end puf_dts_xxx;

/

/****/

/*定时删除分区表历史分区 package body/

/****/

create or replace package body puf_dts_xxx is

procedure del_self_partition(days in int, --保留天数

r_error_code out int, --错误代码

r_error_message out varchar2 --错误消息

) is

partitions_size int;

delete_size int;

v_partition_name user_tab_partitions.partition_name%TYPE;

cursor c_partitions is

select partition_name from user_tab_partitions

where table_name = 'TYEBASSET_BEFORECASH_DS_SELF'

and partition_position > 1

order by partition_position asc;

begin

-- 获取除默认分区外的分区数量

select count(1) into partitions_size

from user_tab_partitions

where table_name = 'TYEBASSET_BEFORECASH_DS_SELF';

delete_size := partitions_size - 1 - days; -- 待删除分区数量

open c_partitions;

loop

fetch c_partitions

into v_partition_name;

EXIT WHEN delete_size <= 0;

begin

--删除分区后需要更新全局索引，否则后续插入数据会报ORA-01502异常

execute immediate 'alter table dtsdata.tyebasset_beforecash_ds_self drop partition ' || v_partition_name || ' update global indexes ';

delete_size := delete_size - 1;

end;

end loop;

close c_partitions;

Exception

When others then

r_error_code := SQLCODE;

r_error_message := SQLERRM;

puf_dts_error_log.log_error(r_error_code,

r_error_message,

'puf_dts_xxx.del_self_partition',

'删除' || days || '天前分区失败',

user);

end del_self_partition;

end puf_dts_xxx;

/

-- 指定分区查询

-- select * from TYEBASSET_BEFORECASH_DS_SELF partition(SYS_P131) ;

-- java代码 通过preparestatement调用

-- call puf_dts_clear_expproc.exportpredeal_proc(?,?,?,?)

--kettle定时任务 TyebassetBeforecashDsSelfMain