mysql建表分区按天_ORACLE创建按月和按天的自动递增分区

一、建按月自增分区表：

1.1建表

SQL> create table month_interval_partition_table (id number,time_col date) partition by range(time_col)

2  interval (numtoyminterval(1,'month'))

3  (

4  partition p_month_1 values less than (to_date('2014-01-01','yyyy-mm-dd'))

5  );

Table created

1.2 查看现在分区

SQL> select table_name,partition_name from user_tab_partitions where table_name='MONTH_INTERVAL_PARTITION_TABLE';

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

MONTH_INTERVAL_PARTITION_TABLE P_MONTH_1

1.3、插入数据

SQL> begin

2  for i in 0..11 loop

3  insert into MONTH_INTERVAL_PARTITION_TABLE values(i,add_months(to_date('2014-01-01','yyyy-mm-dd'),i));

4  end loop;

5  commit;

6  end;