Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Partition by day and delete historical partition through stored procedure

2025-03-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

-- 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report