​ oracle分区表(附带按照月自动分区、按天自动分区)

--list_range  示例   
drop table list_range_tab purge;

create table list_range_tab
(n1 number,n2 date)
partition by list(n1)
subpartition by range(n2)
(partition p01 values(1)
  ( subpartition  subp01 values less than (to_date('3001-01-01 22:00:00','yyyy-mm-dd hh24:mi:ss')),
    subpartition  subp_default values less than (maxvalue)
    ))
 
 
 
 
 
--range_list  示例   
drop table range_list_tab purge;

create table range_list_tab
(n1 date,n2 number)
partition by range(n1)
subpartition by list(n2)
(partition p01 values less than (to_date('3001-01-01 22:00:00','yyyy-mm-dd hh24:mi:ss'))
  ( subpartition  subp01 values(1) ),
 partition  p_default values less than (maxvalue)
    );  
 
 
 
--range_list_inter  主分区自动分区 子分区自动分区示例   
drop table range_list_tab_inter purge;
 
--按天自动分区
create table range_list_tab_inter_dd
(n1 date,n2 number)
partition by range(n1)
interval(numtodsinterval(1,'day'))
subpartition by list(n2)
(partition p01 values less than (to_date('3001-01-01 22:00:00','yyyy-mm-dd hh24:mi:ss'))
  ( subpartition  subp01 values(1) ,
    subpartition               subp_default values(default))
    );  


insert into range_list_tab_inter_dd values(to_date('3001-01-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),1);
insert into range_list_tab_inter_dd values(to_date('3001-02-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),1);
insert into range_list_tab_inter_dd values(to_date('3001-03-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),1);

insert into range_list_tab_inter_dd values(to_date('3001-01-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),111);
insert into range_list_tab_inter_dd values(to_date('3001-02-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),222);
insert into range_list_tab_inter_dd values(to_date('3001-03-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),333);
insert into range_list_tab_inter_dd values(to_date('3001-03-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),444);
insert into range_list_tab_inter_dd values(to_date('3001-03-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),555);


select t1.table_name,
       t1.partition_position,
       t1.partition_name,
       t2.subpartition_position,
       t2.subpartition_name
       
  from user_tab_partitions t1, user_tab_subpartitions t2
 where t1.table_name = t2.table_name
   and t1.table_name = 'RANGE_LIST_TAB_INTER'
   order by t1.partition_position asc,t2.subpartition_position asc;

select * from user_tab_subpartitions where table_name = 'RANGE_LIST_TAB_INTER_DD'

--按月自动分区
drop table range_list_tab_inter_mm purge;

delete from range_list_tab_inter_mm ;

create table range_list_tab_inter_mm
(n1 date,n2 number)
partition by range(n1)  --指定 主分区 以 n1 字段做 range 分区
interval(numtoyminterval(1,'month'))  --指定主分区的扩展方式
subpartition by list(n2)          --指定 子分区 以 n2 字段 做 list 分区
(partition p01 values less than (to_date('3001-01-01 22:00:00','yyyy-mm-dd hh24:mi:ss'))  --指定主分区1的上限
  ( subpartition  subp01 values(1) ,                --指定子分区1 的 值
    subpartition               subp_default values(default))       --指定子分区 的 默认值，保证所有数据都可以入库
    );  

insert into range_list_tab_inter_mm values(to_date('3001-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),1);
insert into range_list_tab_inter_mm values(to_date('3001-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),1);
insert into range_list_tab_inter_mm values(to_date('3001-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),3);



select t1.table_name,
       t1.partition_position,
       t1.partition_name,
       t2.subpartition_position,
       t2.subpartition_name
       
  from user_tab_partitions t1, user_tab_subpartitions t2
 where t1.table_name = t2.table_name
   and t1.table_name = 'RANGE_LIST_TAB_INTER_MM'
   order by t1.partition_position asc,t2.subpartition_position asc;

转载于:https://www.cnblogs.com/iyoume2008/p/7735635.html