CNDBA社区
简介
该自动分区用于一级表分区，按月度分区，表空间按月度并轮转使用的
例如
SQL> select partition_name,tablespace_name,num_rows from user_tab_partitions where table_name=’T_RANGE’;
PARTITION_NAME TABLESPACE_NAME NUM_ROWS
T_RANGE_P2016_01 WZY_01 0
T_RANGE_P2016_02 WZY_02 0
T_RANGE_P2016_03 WZY_03 0
T_RANGE_P2016_04 WZY_04 0
T_RANGE_P2017_01 WZY_01 0
T_RANGE_P2017_02 WZY_02 0
T_RANGE_P2017_03 WZY_03 0
T_RANGE_P2017_04 WZY_04 0
T_RANGE_P2018_01 WZY_01 1
T_RANGE_P2018_02 WZY_02 1
T_RANGE_P2018_03 WZY_03 0
PARTITION_NAME TABLESPACE_NAME NUM_ROWS
T_RANGE_P2018_04 WZY_04 0
T_RANGE_P2019_01 WZY_01 0
T_RANGE_P2019_02 WZY_02
T_RANGE_P2019_03 WZY_03
T_RANGE_PMAX WZY_MAX
存储过程的原理是：分裂max分区，产生现有最大分区+1月度的分区。以上数据分区不完整，仅做参考
分区命名规则：P+年份+月度，该过程也支持按季度分区
表空间准备
create tablespace wzy_01 datafile ‘/u01/app/oracle/oradata/wzy_01.dbf’ size 5m autoextend on;
create tablespace wzy_02 datafile ‘/u01/app/oracle/oradata/wzy_02.dbf’ size 5m autoextend on;
create tablespace wzy_03 datafile ‘/u01/app/oracle/oradata/wzy_03.dbf’ size 5m autoextend on;
create tablespace wzy_04 datafile ‘/u01/app/oracle/oradata/wzy_04.dbf’ size 5m autoextend on;
create tablespace wzy_05 datafile ‘/u01/app/oracle/oradata/wzy_05.dbf’ size 5m autoextend on;
create tablespace wzy_06 datafile ‘/u01/app/oracle/oradata/wzy_06.dbf’ size 5m autoextend on;
create tablespace wzy_07 datafile ‘/u01/app/oracle/oradata/wzy_07.dbf’ size 5m autoextend on;
create tablespace wzy_08 datafile ‘/u01/app/oracle/oradata/wzy_08.dbf’ size 5m autoextend on;
create tablespace wzy_09 datafile ‘/u01/app/oracle/oradata/wzy_09.dbf’ size 5m autoextend on;
create tablespace wzy_10 datafile ‘/u01/app/oracle/oradata/wzy_10.dbf’ size 5m autoextend on;
create tablespace wzy_11 datafile ‘/u01/app/oracle/oradata/wzy_11.dbf’ size 5m autoextend on;
create tablespace wzy_12 datafile ‘/u01/app/oracle/oradata/wzy_12.dbf’ size 5m autoextend on;
create tablespace wzy_max datafile ‘/u01/app/oracle/oradata/wzy_max.dbf’ size 5m autoextend on;
分区表
create table t_range (dt date,name varchar2(50))
partition by range(dt)(
partition t_range_p2016_01 values less than (to_date(‘2016-02-01 00:00:00’,’yyyy-mm-dd hh24:mi:ss’)) tablespace wzy_01,
partition t_range_p2016_02 values less than (to_date(‘2016-03-01 00:00:00’,’yyyy-mm-dd hh24:mi:ss’)) tablespace wzy_02,
partition t_range_p2016_03 values less than (to_date(‘2016-04-01 00:00:00’,’yyyy-mm-dd hh24:mi:ss’)) tablespace wzy_03,
partition t_range_pmax values less than (maxvalue) tablespace wzy_max
存储过程
可以将test useful的内容启用，进行调试
CREATE OR REPLACE PROCEDURE auto_part_t_range is
/
foxytale make
qq 195136130 /
/ test useful
set serveroutput on /
v_partkey1 varchar2(100);
v_partkey2 varchar2(20);
t_full varchar2(30);
t_year varchar2(20);
t_times varchar2(20);
x_string varchar2(300);
c_table varchar2(30);
c_table_p varchar2(30);
c_table_max varchar2(30);
c_times number(10);
c_tbs varchar2(30);
begin
ctable:=’T_RANGE’;
c_table_p:=’T_RANGE_P’;
c_table_max:=’T_RANGE_PMAX’;
c_times:=12;
c_tbs:=’wzy‘;
—session set
execute immediate ‘alter session set nls_date_format=’’yyyy-mm-dd hh24:mi:ss’’’;
—get the param of partition table
select high_value,PARTITION_NAME
into v_partkey1,t_full
from user_tab_partitions
where table_name=c_table and
partition_position=
(
select max(partition_position)-1
from user_tab_partitions
where table_name=c_table
);
—configure the partitionary key
if c_times=12 then
v_partkey2:=add_months(to_date(substr(v_partkey1,11,19),’yyyy-mm-dd hh24:mi:ss’),1);
else if c_times=4 then
v_partkey2:=add_months(to_date(substr(v_partkey1,11,19),’yyyy-mm-dd hh24:mi:ss’),3);
end if;
end if;
—configure the partition table name
t_year:=substr(t_full,-7,4);
t_times:=substr(t_full,-2,2);
if (to_number(t_times)+1)>c_times then
t_times:=’01’;
t_year:=to_char(to_number(t_year)+1);
else
t_times:=lpad(to_char(to_number(t_times)+1),2,’0’);
end if;
ctable_p:=c_table_p||t_year||’‘||t_times;
c_tbs:=c_tbs||t_times;
—configure the exec sql string
x_string:=’ALTER TABLE ‘
||c_table
||’ SPLIT PARTITION ‘
||c_table_max
||’ AT (to_date(‘’’
||v_partkey2
||’’’,’’yyyy-mm-dd hh24:mi:ss’’)) INTO (PARTITION ‘
||c_table_p
||’ tablespace ‘
||c_tbs
||’,PARTITION ‘
||c_table_max
||’) UPDATE INDEXES’;
—test output
/test useful
dbms_output.put_line(v_partkey1);
dbms_output.put_line(v_partkey2);
dbms_output.put_line(t_full);
dbms_output.put_line(t_year);
dbms_output.put_line(t_times);
dbms_output.put_line(x_string);
dbms_output.put_line(c_tbs); /
execute immediate x_string;
end;
/
自动job
设置自动job每月定时生成新分区
VARIABLE testjobid number;
begin
sys.dbms_job.submit(:testjobid,auto_part_t_range,sysdate,’add_months(sysdate,1)’);
commit;
end;
版权声明：本文为博主原创文章，未经博主允许不得转载。
分区表，自动分区，存储过程
