Automatic maintenance of Oracle 10g Partition Table

Oracle 10 Gb partition tables do not support automated management. Generally, you must manually create and delete partitions. Today, we have a script for automated table space management.

This script consists of three parts: sys_ConfigTable. SQL, sys_pro_AddAndDropPartition. SQL, and sys_pro_MergeTable. SQL.

1. sys_ConfigTable. SQL mainly creates a configuration table: enter the name of the table to be automatically maintained, the retention days, and the number of partitions per day;

2. sys_pro_AddAndDropPartition. SQL is used to automatically add table partitions and delete expired partitions;

3. When sys_pro_MergeTable. SQL and other daily partitions are greater than 1, we merge the old partitions and recreate the invalid indexes.

1. sys_ConfigTable. SQL

Drop table CONFIGTABLE;
Create table CONFIGTABLE
(
Name VARCHAR2 (64) not null,
Value VARCHAR2 (64) not null,
Type VARCHAR2 (64) not null,
Isrun NUMBER (1) not null,
Remark VARCHAR2 (64)
)
;

Prompt Loading CONFIGTABLE...
Insert into CONFIGTABLE (name, value, type, isrun, remark)
Values ('bi _ m_visitloocs', 'bi _ m_visitloocs', 'protocal ', 1, 'History log table ');
Insert into CONFIGTABLE (name, value, type, isrun, remark)
Values ('bi _ m_visitloocs', '123', 'save', 1, 'Log retention days ');
Insert into CONFIGTABLE (name, value, type, isrun, remark)
Values ('bi _ m_visitloocs', '1', 'num _ part_byday ', 1, 'number of partitions to be added per Day ');

Insert into CONFIGTABLE (name, value, type, isrun, remark)
Values ('bi _ logfile', 'bi _ logfile', 'protocol', 1, 'historical log table ');
Insert into CONFIGTABLE (name, value, type, isrun, remark)
Values ('bi _ logfile', '000000', 'save', 1, 'Log retention days ');
Insert into CONFIGTABLE (name, value, type, isrun, remark)
Values ('bi _ logfile', '1', 'num _ part_byday', 1, 'number of partitions to be added per day ');
Commit;

/* Insert into CONFIGTABLE (name, value, type, isrun, remark)
Values ('accesslog', 'accesslog', 'protocol', 1, 'History log table ');
Insert into CONFIGTABLE (name, value, type, isrun, remark)
Values ('accesslog', '123', 'save', 1, 'Log retention days ');
Insert into CONFIGTABLE (name, value, type, isrun, remark)
Values ('accesslog', '24', 'num _ part_byday', 1, 'number of partitions to be added per day ');
Commit;
*/

/*
-- Create table
Drop table BI_M_VISITLOCUS;
Create table BI_M_VISITLOCUS
(
Accesstime DATE not null,
Machineid VARCHAR2 (100) not null,
Channeltype INTEGER default 0 not null,
Channelname VARCHAR2 (200 ),
Pagename VARCHAR2 (100 ),
Categoryid VARCHAR2 (100 ),
Categorylevel INTEGER,
Category VARCHAR2 (200 ),
Productid VARCHAR2 (200 ),
Productname VARCHAR2 (200 ),
Pagetype INTEGER,
Sessionid VARCHAR2 (100 ),
Producttype INTEGER default 0,
Machinetype VARCHAR2 (10) default 'hd ',
Remark VARCHAR2 (200 ),
Useragent VARCHAR2 (100)
)
Partition by range (ACCESSTIME)
(
Partition P_20130114_23 values less than (TO_DATE ('2017-01-14 23:59:59 ', 'yyyy-MM-DD HH24: MI: ss '))
-- Tablespace user
);
*/

1 2 3 Next Page