我们来自五湖四海,不为别的,只因有共同的爱好,为中国互联网发展出一分力!
领航时时彩重庆版

重庆时时彩号码开奖历史记录:Oracle11g下自动创建分区

2014年05月11日23:23 阅读: 33060 次

领航时时彩重庆版 www.9nwl5.cn 标签: Oracle11g下自动创建分区

11g之前,维护分区需要手工。11g之后使用interval来实现自动扩展分区,简化了维护。

根据年: INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))

根据月: INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))

根据天: INTERVAL(NUMTODSINTERVAL(1,'DAY'))

根据时分秒: NUMTODSINTERVAL( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'})

下面用按月自动扩展来做个试验:

SQL> create table t_range (id number not null PRIMARY KEY, test_date date)
partition by range (test_date) interval (numtoyMinterval (1,'MONTH'))
(
partition p_2014_01_01 values less than (to_date('2014-01-01', 'yyyy-mm-dd'))
);
--看到只有一个分区
SQL> select partition_name from user_tab_partitions where table_name='T_RANGE';
PARTITION_NAME
------------------------------
P_2014_01_01


SQL> insert /*+append */ into t_range select rownum,
to_date(to_char(sysdate - 140, 'J') +
trunc(dbms_random.value(0, 80)),
'J')
from dual
connect by rownum <= 100000;
SQL> commit;

--可以看到SYS开头的分区是自动扩展的
SQL> select partition_name from user_tab_partitions where table_name='T_RANGE';
PARTITION_NAME
------------------------------
P_2014_01_01
SYS_P21
SYS_P22
SYS_P23

--再看看t_range的表结构

create table T_RANGE
(
ID NUMBER not null,
TEST_DATE DATE
)
partition by range (TEST_DATE)
(
partition P_2014_01_01 values less than (TO_DATE(' 2014-01-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition SYS_P21 values less than (TO_DATE(' 2014-02-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition SYS_P22 values less than (TO_DATE(' 2014-03-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition SYS_P23 values less than (TO_DATE(' 2014-04-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_RANGE
add primary key (ID);

--如果对分区名不太爽,则可以自己修改一下:

alter table t_range rename partition SYS_P21 to p_2014_02_01;
alter table t_range rename partition SYS_P22 to p_2014_03_01;
alter table t_range rename partition SYS_P23 to p_2014_04_01;

摘自官方文档:

Restrictions on Interval Partitioning The INTERVAL clause is subject to the restrictions listed in "Restrictions on Partitioning in General" and "Restrictions on Range Partitioning". The following additional restrictions apply:

You can specify only one partitioning key column, and it must be of NUMBER, DATE, FLOAT, or TIMESTAMP data type.

This clause is not supported for index-organized tables.

This clause is not supported for tables containing nested table columns or varray columns.

You cannot create a domain index on an interval-partitioned table.

Interval partitioning is not supported at the subpartition level.

Serializable transactions do not work with interval partitioning. Trying to insert data into a partition of an interval partitioned table that does not yet have a segment causes an error.

In the VALUES clause:

You cannot specify MAXVALUE (an infinite upper bound), because doing so would defeat the purpose of the automatic addition of partitions as needed.

You cannot specify NULL values for the partitioning key column.

分享到: 更多
©2018 安全焦点 版权所有.

  • 奚国华委员:新兴际华将党建六大优势转化为发展优势 2018-12-06
  • 创业女青年哈丽娟:世界是自己的 与他人没关系 2018-12-06
  • 移植2胚怀5胎 一针减3化风险 十堰市人民医院再创生命奇迹 2018-11-06
  • 首届妈祖信俗“立德”论坛成功举办 2018-10-25
  • 郧西男孩两条腿“长”在一起 十堰市人民医院手术整形帮他迈开双腿 2018-10-25
  • 重磅 江西省委副书记李炳军同志任省委党校校长 2018-10-19
  • 商务部新闻发言人对美白宫6月18日声明发表谈话 2018-10-12
  • 从献血条件,看看你的身体是否达标 2018-09-26
  • 全国“非遗”保护工作先进名单公布 2018-09-26
  • 储大同 中国医学科学院肿瘤医院首席科学家 2018-08-15
  • 【理上网来·喜迎十九大】扶贫也创新!十八大以来年均减贫人数超1300万 获历史突破 2018-08-15
  • 开创美丽中国建设新局面 2018-08-13
  • 毛泽东三游故宫看了些什么 2018-08-13
  • 独家视频:新时代来啦! 2018-08-12
  • 旅游市场再创佳绩端午假期河北接待游客2026.77万 揽金147.79亿 2018-08-12
  • 239| 118| 996| 443| 235| 884| 159| 819| 670| 469|