Partition by day and delete historical partition through stored procedure
-- create partition tables on a daily basis, and periodically delete partitions before a specified number of days through stored procedures
/ * /
/ create a daily partition table * /
/ * /
-- order order asset snapshot table
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 (16Pol 2)
En_cday24endasset NUMBER (16Pol 2)
En_frozenbala NUMBER (16Pol 2)
En_accumincome NUMBER (16Pol 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)
(
Partition asset_self_p1 values less than (to_date ('2018-01-01-01-01))
);
-- Add comments to the table
Comment on table DTSDATA.tyebasset_beforecash_ds_self
Is' order Asset Snapshot Table'
-- Add comments to the columns
Comment on column DTSDATA.tyebasset_beforecash_ds_self.id_yebassetself
Is' record number (unique)'
Comment on column DTSDATA.tyebasset_beforecash_ds_self.vc_tradeacco
Is' trading account'
Comment on column DTSDATA.tyebasset_beforecash_ds_self.vc_fundcode
Is' fund code'
Comment on column DTSDATA.tyebasset_beforecash_ds_self.d_carryday
Is' date of income to be distributed'
Comment on column DTSDATA.tyebasset_beforecash_ds_self.en_cday15endasset
Is' cday as of 15:00 Asset'
Comment on column DTSDATA.tyebasset_beforecash_ds_self.en_cday24endasset
Is' cday as of 24:00 Asset'
Comment on column DTSDATA.tyebasset_beforecash_ds_self.en_frozenbala
Is' Frozen shares'
Comment on column DTSDATA.tyebasset_beforecash_ds_self.en_accumincome
Is' cumulative income'
Comment on column DTSDATA.tyebasset_beforecash_ds_self.remark
Is' remarks'
Comment on column DTSDATA.tyebasset_beforecash_ds_self.CREATED_BY
Is' input personnel'
Comment on column DTSDATA.tyebasset_beforecash_ds_self.CREATED_DATE
Is' creation date'
Comment on column DTSDATA.tyebasset_beforecash_ds_self.UPDATED_BY
Is' update personnel'
Comment on column DTSDATA.tyebasset_beforecash_ds_self.UPDATED_DATE
Is' Update date'
-- create primary key
Alter table DTSDATA.tyebasset_beforecash_ds_self add constraint PK_tyebasset_ds_self primary key (id_yebassetself) using index initrans 16
-create an index
Create unique index DTSDATA.IDX_tyebasset_ds_self on DTSDATA.tyebasset_beforecash_ds_self (vc_tradeacco,vc_fundcode,d_carryday) local
-- create synonyms, authorization
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
/ * /
/ regularly delete partition table historical partition package /
/ * /
Create or replace package puf_dts_xxx is
Procedure del_self_partition (
Days in int-- number of days to retain
R_error_code out int,-- error code
R_error_message out varchar2-error messa
);
End puf_dts_xxx
/
/ * /
/ * regularly delete the partition table historical partition package body /
/ * /
Create or replace package body puf_dts_xxx is
Procedure del_self_partition (days in int,-- retention days
R_error_code out int,-- error code
R_error_message out varchar2-error messa
) 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
-- gets the number of partitions except the default partition
Select count (1) into partitions_size
From user_tab_partitions
Where table_name = 'TYEBASSET_BEFORECASH_DS_SELF'
Delete_size: = partitions_size-1-days;-the number of partitions to be deleted: open centering partitions, fetch c_partitions into loop partitions, EXIT WHEN delete_size
