分区手动转自动实验.txt

create table interval_baks (prod_id number(6),time_id date)

partition by range (time_id)

(

 partition p1 values less than (TO_DATE('2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),

 partition p2 values less than (TO_DATE('2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),

 partition p3 values less than (TO_DATE('2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

);

insert into interval_baks values(1,to_date('2019-01-01 06:00:00' , 'yyyy-mm-dd hh24:mi:ss'));

insert into interval_baks values(2,to_date('2019-02-03 08:00:00' , 'yyyy-mm-dd hh24:mi:ss'));

insert into interval_baks values(3,to_date('2019-03-15 10:00:00' , 'yyyy-mm-dd hh24:mi:ss'));

insert into interval_baks values(4,to_date('2019-04-20 10:00:00' , 'yyyy-mm-dd hh24:mi:ss'));

SQL> insert into interval_baks values(4,to_date('2019-04-20 10:00:00' , 'yyyy-mm-dd hh24:mi:ss'));

insert into interval_baks values(4,to_date('2019-04-20 10:00:00' , 'yyyy-mm-dd hh24:mi:ss'))

            *

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

select table_name,partition_name from user_tab_partitions where table_name='INTERVAL_BAKS';

--设置自动分区属性

alter table INTERVAL_BAKS set interval(numtoyminterval(1, 'month'));

insert into interval_baks values(4,to_date('2019-04-20 10:00:00' , 'yyyy-mm-dd hh24:mi:ss'));

--验证

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

TABLE_NAME                     PARTITION_NAME

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

INTERVAL_BAKS                  P1

INTERVAL_BAKS                  P2

INTERVAL_BAKS                  P3

INTERVAL_BAKS                  SYS_P924

SQL> select * from interval_baks partition(p1);

   PROD_ID TIME_ID

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

         1 2019-01-01 06:00:00

SQL> select * from interval_baks partition(p2); 

   PROD_ID TIME_ID

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

         2 2019-02-03 08:00:00

SQL> select * from interval_baks partition(p3);

   PROD_ID TIME_ID

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

         3 2019-03-15 10:00:00

SQL> select * from interval_baks partition(SYS_P924);

   PROD_ID TIME_ID

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

         4 2019-04-20 10:00:00