In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-18 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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.