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博客

convert range partition to interval partition

最新文章