ORACLE分区窗口按月按天自动分区

偏执的太偏执、 2022-05-08 00:14 785阅读 0赞

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