oracle自动分区demo

10g时候没有interval分区，11g的自动分区命名又不友好，这个算是个解决方案

CREATE OR REPLACE PROCEDURE auto_partition(v_table in varchar2,v_table_p in varchar2,v_table_max in varchar2,v_tbs in varchar2,v_times in number) is
/*
foxytale make
qq 195136130
*/
/* testuserful
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

/*
c_table:='T_RANGE';
c_table_p:='T_RANGE_P';
c_table_max:='T_RANGE_PMAX';
c_times:=12;
c_tbs:='wzy_';
*/
c_table:=v_table;
c_table_p:=v_table_p;
c_table_max:=v_table_max;
c_tbs:=v_tbs;
c_times:=v_times;

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

c_table_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 GLOBAL INDEXES'; 
--test output
/*
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;                       
/

==========================================================================================================
/* query */

select table_name,partition_name,partition_position,high_value from dba_tab_partitions where table_name='T_RANGE';
select table_name,substr(partition_name,1,12),partition_position,high_value from dba_tab_partitions where table_name='T_RANGE';

/*var contain the char before number
--example of tbs 
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;

--example of table
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
);
--the var should be 
c_table:='T_RANGE';
c_table_p:='T_RANGE_P';
c_table_max:='T_RANGE_PMAX';
c_times:=12;
c_tbs:='wzy_';

/* execute command*/
exec auto_partition('T_RANGE','T_RANGE_P','T_RANGE_PMAX','wzy_',12);

--the job submit
VARIABLE jobno number;
BEGIN
   DBMS_JOB.SUBMIT(:jobno, 
      'auto_partition('T_RANGE','T_RANGE_P','T_RANGE_PMAX','wzy_',12);', 
      SYSDATE, 'add_months(SYSDATE,1)');
   COMMIT;
END;
 

exec DBMS_JOB.remove('3')
*/