分区手动转自动实验.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
