ITPub博客

首页 > 数据库 > Oracle > convert range partition to interval partition

convert range partition to interval partition

原创 Oracle 作者:warmbreeze 时间:2016-10-10 14:35:19 0 删除 编辑
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

create table   tpart (
name varchar2(10),
create_date DATE
)
PARTITION BY RANGE (create_date)
(
PARTITION P2001 VALUES LESS THAN (TO_DATE('2002-01-01','YYYY-MM-DD')),
PARTITION P2002 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')),
PARTITION P2003 VALUES LESS THAN (TO_DATE('2004-01-01','YYYY-MM-DD'))
);

select partition_name ,INTERVAL from dba_tab_partitions where table_name='TPART';
PARTITION_NAME                 INT
------------------------------ ---
P2001                          NO
P2002                          NO
P2003                          NO

select partitioning_type,partition_count,interval from dba_part_tables where table_name='TPART';
PARTITION PARTITION_COUNT INTERVAL
--------- --------------- ------------------------------
RANGE                   3

alter table tpart set interval(NUMTOYMINTERVAL(1,'year'));

select partitioning_type,partition_count,interval from dba_part_tables where table_name='TPART';
PARTITION PARTITION_COUNT INTERVAL
--------- --------------- ------------------------------
RANGE             1048575 NUMTOYMINTERVAL(1,'YEAR')

SQL> insert into tpart values('new part',date '2005-01-01');
1 row created.
SQL> commit;
Commit complete.

SQL> select partition_name ,INTERVAL from dba_tab_partitions where table_name='TPART';
PARTITION_NAME                 INTERVAL
------------------------------ ------------------------------
P2001                          NO
P2002                          NO
P2003                          NO
SYS_P81                        YES


ref: PARTITION_COUNT Shows Large Value 1048575 With Interval Partitioning (文档 ID 1447928.1)



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/37279/viewspace-2126057/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-15

  • 博文量
    45
  • 访问量
    42515