ORACLE创建按月和按天的自动递增分区

发布时间：2020-12-12 16:30:40 所属栏目：百科来源：网络整理

导读：一、建按月自增分区表： 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','

一、建按月自增分区表： 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; 7 / PL/SQL procedure successfully completed 1.4、查看分区 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 MONTH_INTERVAL_PARTITION_TABLE SYS_P22 MONTH_INTERVAL_PARTITION_TABLE SYS_P23 MONTH_INTERVAL_PARTITION_TABLE SYS_P24 MONTH_INTERVAL_PARTITION_TABLE SYS_P25 MONTH_INTERVAL_PARTITION_TABLE SYS_P26 MONTH_INTERVAL_PARTITION_TABLE SYS_P27 MONTH_INTERVAL_PARTITION_TABLE SYS_P28 MONTH_INTERVAL_PARTITION_TABLE SYS_P29 MONTH_INTERVAL_PARTITION_TABLE SYS_P30 MONTH_INTERVAL_PARTITION_TABLE SYS_P31 MONTH_INTERVAL_PARTITION_TABLE SYS_P32 MONTH_INTERVAL_PARTITION_TABLE SYS_P33 13 rows selected 1.5查看其中一个分区数据 SQL> select * From MONTH_INTERVAL_PARTITION_TABLE partition(SYS_P22); ID TIME_COL ---------- ----------- 0 2014/1/1 二、创建按天的分区表 2.1建表 SQL> create table day_interval_partition_table (id number,time_col date) partition by range(time_col) 2 interval (numtodsinterval(1,'day')) 3 ( 4 partition p_day_1 values less than (to_date('2014-01-01','yyyy-mm-dd')) 5 ); Table created 2.2、查看现在分区 SQL> select table_name,partition_name from user_tab_partitions where table_name='DAY_INTERVAL_PARTITION_TABLE'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ DAY_INTERVAL_PARTITION_TABLE P_DAY_1 2.3、插入数据 SQL> begin 2 for i in 1..12 loop 3 insert into DAY_INTERVAL_PARTITION_TABLE values(i,trunc(to_date('2014-01-01','yyyy-mm-dd')+i)); 4 end loop; 5 commit; 6 end; 7 / PL/SQL procedure successfully completed 2.4查看分区 SQL> select table_name,partition_name from user_tab_partitions where table_name='DAY_INTERVAL_PARTITION_TABLE'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ DAY_INTERVAL_PARTITION_TABLE P_DAY_1 DAY_INTERVAL_PARTITION_TABLE SYS_P34 DAY_INTERVAL_PARTITION_TABLE SYS_P35 DAY_INTERVAL_PARTITION_TABLE SYS_P36 DAY_INTERVAL_PARTITION_TABLE SYS_P37 DAY_INTERVAL_PARTITION_TABLE SYS_P38 DAY_INTERVAL_PARTITION_TABLE SYS_P39 DAY_INTERVAL_PARTITION_TABLE SYS_P40 DAY_INTERVAL_PARTITION_TABLE SYS_P41 DAY_INTERVAL_PARTITION_TABLE SYS_P42 DAY_INTERVAL_PARTITION_TABLE SYS_P43 DAY_INTERVAL_PARTITION_TABLE SYS_P44 DAY_INTERVAL_PARTITION_TABLE SYS_P45 2.5查看数据 SQL> select table_name,partition_name from user_tab_partitions where table_name='DAY_INTERVAL_PARTITION_TABLE'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ DAY_INTERVAL_PARTITION_TABLE P_DAY_1 DAY_INTERVAL_PARTITION_TABLE SYS_P34 DAY_INTERVAL_PARTITION_TABLE SYS_P35 DAY_INTERVAL_PARTITION_TABLE SYS_P36 DAY_INTERVAL_PARTITION_TABLE SYS_P37 DAY_INTERVAL_PARTITION_TABLE SYS_P38 DAY_INTERVAL_PARTITION_TABLE SYS_P39 DAY_INTERVAL_PARTITION_TABLE SYS_P40 DAY_INTERVAL_PARTITION_TABLE SYS_P41 DAY_INTERVAL_PARTITION_TABLE SYS_P42 DAY_INTERVAL_PARTITION_TABLE SYS_P43 DAY_INTERVAL_PARTITION_TABLE SYS_P44 DAY_INTERVAL_PARTITION_TABLE SYS_P45 13 rows selected SQL> select * From DAY_INTERVAL_PARTITION_TABLE; ID TIME_COL ---------- ----------- 1 2014/1/2 2 2014/1/3 3 2014/1/4 4 2014/1/5 5 2014/1/6 6 2014/1/7 7 2014/1/8 8 2014/1/9 9 2014/1/10 10 2014/1/11 11 2014/1/12 12 2014/1/13 12 rows selected SQL> select * From DAY_INTERVAL_PARTITION_TABLE partition(SYS_P39); ID TIME_COL ---------- ----------- 6 2014/1/7

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!