ORACLE创建按月和按天的自动递增分区
来源:互联网 发布:nginx redis模块 编辑:程序博客网 时间:2024/05/23 16:31
一、建按月自增分区表:
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
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
阅读全文
0 0
- ORACLE创建按月和按天的自动递增分区
- ORACLE创建按月和按天的自动递增分区
- 按月分区触发器函数编写,自动创建分区表
- Oracle 11g 实现按月创建表分区
- 【Oracle】创建序列和触发器:实现插入时自动递增
- Oracle数据库创建表ID字段的自动递增
- Oracle数据库创建表ID字段的自动递增
- Oracle 11g 通过间隔分区实现按月创建表分区
- Oracle 11g 通过间隔分区实现按月创建表分区
- Oracle按日自动创建分区
- FCKeditor 上传自动重命名、按月创建文件夹和基本操作
- FCKeditor 上传自动重命名、按月创建文件夹和基本操作
- Oracle数据库创建自动递增列
- ORACLE 创建和使用分区的表
- SQL SERVER 如何按月创建表分区?
- oracle 11g表智能按月分区
- oracle 9i 大数据表按月分区
- Oracle 11g下自动创建分区
- jquery实现侧边栏手风琴三级导航菜单demo
- 写一个单利模式吧
- CMake 运行错误could not find CMAKE_ROOT!!!
- Mybatis: resultType和resultMap的区别
- UVA 11404
- ORACLE创建按月和按天的自动递增分区
- 关于前端引入icon图标
- 43 linux的驱动模型,实现设备驱动代码的可移植性
- python3没有了xrange
- linux的popen函数使用
- 设计模式 状态模式 以自动售货机为例
- android 配置 小乌龟SVN SVN需要忽略的文件
- simulate grep- leave key word lines
- Scrapy学习笔记 爬取w3school