Monthly partition is also possible/comment only
anonymous, August 17, 2012 - 8:06 am UTC
And if You want a monthly partition, then use:
interval(100)
A reader, February 06, 2013 - 3:31 am UTC
But what about the interval when there is a change in year? If we have partition created till 20121201, then next partition high value should be 20130101. In this case the difference will be 8900. So I believe the next partition after 20121201 will be 20121301 which could not be the valid month. Any workaround for this.
February 06, 2013 - 1:55 pm UTC
8900 what?
I don't know what you mean at all here.
If you have monthly partitions, we add months - not days or hours or anything - you can add a month to 01-dec-2012 resulting in 01-jan-2013.
Problem when year changes
Shobhank, March 13, 2013 - 10:07 am UTC
Scenario: Monthly partitioning on DateID (YYYYMMDD):
Partition interval = 100
1st Partition DateID < 19990101
2nd Partition DateID < 19990201
3rd Partition DateID < 19990301
:
:
:
nth Partition DateID < 19991201
(n+1)th Partition DateID < 19991301
Which is wrong; 13th month?
Next partition should be 20000101
which has interval = 8900
Just to restate the above person's correct point!
March 13, 2013 - 10:42 am UTC
but that wouldn't be a problem with *my* solution with the interval of 1.
and it wouldn't be an issue with using 100 either.
think about it, interval partitioning only creates partitions when the data is inserted with a value. it doesn't create empty partitions.
So, if you insert 19991201 it'll create a partition and put anything with the values 19991201 up to but not including 19991301 into it (but you'll never put 19991300 in there - that isn't a date)
when you put 20000101 in there, it'll create a partition for 20000101 up to but not including 20000201.
it'll work dandy.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table product
2 ( prod_id number,
3 prod_code varchar2(3),
4 release_date number
5 )
6 partition by range(release_date)
7 interval(100)
8 (partition empty values less than (19990101) )
9 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into product (prod_id, prod_code, release_date )
2 select rownum, rownum, to_char( add_months( to_date('19990101','yyyymmdd'), rownum ),'yyyymmdd' )
3 from dual
4 connect by level <= 20;
20 rows created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table tmp
2 as
3 select table_name, partition_name, to_lob(high_value) high_value
4 from user_tab_partitions
5 where table_name = 'PRODUCT';
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select t.*,
2 (select release_date
3 from product
4 where release_date >= xfrom
5 and release_date < xto) val
6 from (
7 select table_name, partition_name, to_number(high_value)-100 xfrom, to_number(high_value) xto
8 from tmp
9 ) t;
TABLE_NA PARTITION_ XFROM XTO VAL
-------- ---------- ---------- ---------- ----------
PRODUCT EMPTY 19990001 19990101
PRODUCT SYS_P2657 19990201 19990301 19990201
PRODUCT SYS_P2658 19990301 19990401 19990301
PRODUCT SYS_P2659 19990401 19990501 19990401
PRODUCT SYS_P2660 19990501 19990601 19990501
PRODUCT SYS_P2661 19990601 19990701 19990601
PRODUCT SYS_P2662 19990701 19990801 19990701
PRODUCT SYS_P2663 19990801 19990901 19990801
PRODUCT SYS_P2664 19990901 19991001 19990901
PRODUCT SYS_P2665 19991001 19991101 19991001
PRODUCT SYS_P2666 19991101 19991201 19991101
PRODUCT SYS_P2667 19991201 19991301 19991201
PRODUCT SYS_P2668 20000101 20000201 20000101
PRODUCT SYS_P2669 20000201 20000301 20000201
PRODUCT SYS_P2670 20000301 20000401 20000301
PRODUCT SYS_P2671 20000401 20000501 20000401
PRODUCT SYS_P2672 20000501 20000601 20000501
PRODUCT SYS_P2673 20000601 20000701 20000601
PRODUCT SYS_P2674 20000701 20000801 20000701
PRODUCT SYS_P2675 20000801 20000901 20000801
PRODUCT SYS_P2676 20000901 20001001 20000901
21 rows selected.
interval partition on date
Sridhar, April 23, 2013 - 11:29 am UTC
Interval partition on date behaves strange in DEV environment .
select * from fct_lm_all where effective_day = '29-Feb-2012'
Effective_day is a DATE type and the partitioned column.
This query works well in PROD and UAT but not in development . This query needs a trunc(effective_day) in development to return any rows. Any help on what could be different in DEV is appreciated .
Thanks
Sridhar
April 23, 2013 - 1:16 pm UTC
do this,
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
in all situations and see what dates you have in there.
I'll bet you a dollar that in development - you have "time" on your dates and in the other environments you do not.
also, NEVER CODE
select * from fct_lm_all where effective_day = '29-Feb-2012'
do this:
select * from fct_lm_all where effective_day = to_date('29-Feb-2012','dd-mon-yyyy')
always use an explicit date mask converting strings to dates and dates to strings!!!
A reader, April 24, 2013 - 10:06 am UTC
Correct . we alway need to qualify date filter with to_date as a practice.
However such date filters ( day = '29-Feb-2012' ) work fine on all other tables in the database
except this table
The only difference we see is that this is an interval partitioned table on date.
Will this make any difference ?
Alter session didnt work. And this parameter is same ( dd-mon-yyyy ) in all 3 environments.
April 24, 2013 - 1:05 pm UTC
the alter session was so you could see the data fully.
ops$tkyte%ORA11GR2> set echo on
ops$tkyte%ORA11GR2> select created from all_users where rownum < 5;
CREATED
---------
05-SEP-10
05-SEP-10
05-SEP-10
05-SEP-10
ops$tkyte%ORA11GR2> @bigdate
ops$tkyte%ORA11GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte%ORA11GR2> select created from all_users where rownum < 5;
CREATED
--------------------
05-sep-2010 15:40:00
05-sep-2010 15:40:00
05-sep-2010 15:40:02
05-sep-2010 15:41:22
when you do that with YOUR data - does the data show a time or does the data show 00:00:00 for the time.
what I suspect is the date column in your one non-functioning database has TIME on it. It does not have midnight. therefore it is not equal to 29-feb-2012 (which has 00:00:00 associated with it)
do this:
select count(*) from table where date_column = to_date('29-feb-2012','dd-mon-yyyy');
select count(*) from table where date_column between
to_date('29-feb-2012','dd-mon-yyyy') and
to_date('29-feb-2012','dd-mon-yyyy')+1-1/24/60/60;
post the results. if one returns 0 and the other returns non-zero, then you have time on your date - you data is botched.\
and frankly, I don't care if your default is currently dd-mon-yyyy - USE EXPLICIT FORMATS WITH DATES on to_date, on to_char. you are subject to sql injection bugs, you are subject to serious logic bombs otherwise.
A reader, April 26, 2013 - 10:09 am UTC
Please review the results below . I am not getting the result unless i trunc on effective_day . ( issue only in DEV )
I have checked that all rows have 00:00:00 time in the column.
SQL> select count(*) from fct_lm_all where effective_day = to_date('29-feb-2012','dd-mon-yyyy');
COUNT(*)
----------
0
SQL> select count(*) from fct_lm_all where trunc(effective_day) = to_date('29-feb-2012','dd-mon-yyyy');
COUNT(*)
----------
19911
SQL> select count(*) from fct_lm_all where effective_day between to_date('29-feb-2012','dd-mon-yyyy') and
to_date('29-feb-2012','dd-mon-yyyy')+1-1/24/60/60;
COUNT(*)
----------
0
SQL> select count(*) from fct_lm_all where trunc(effective_day) between
to_date('29-feb-2012','dd-mon-yyyy') and
to_date('29-feb-2012','dd-mon-yyyy')+1-1/24/60/60;
COUNT(*)
----------
19911
SQL> select count(*) from fct_lm_all where effective_day
= to_date('31-Jan-2012 00:00:00','dd-mon-yyyy hh24:mi:ss');
COUNT(*)
----------
0
SQL> select count(*) from fct_lm_all where trunc(effective_day)
= to_date('31-Jan-2012 00:00:00','dd-mon-yyyy hh24:mi:ss');
COUNT(*)
----------
20698
April 26, 2013 - 1:55 pm UTC
can you please select out the date column using dd-mon-yyyy hh24:mi:ss (which I've asked you to do a few times now) so we can see the data?
do this for us:
select to_char( effective_day, 'dd-mon-yyyy hh24:mi:ss' ), dump( effective_day )
from fct_lm_all
where trunc(effective_day) =
to_date('29-feb-2012','dd-mon-yyyy')
and rownum <= 25;
A reader, April 26, 2013 - 2:58 pm UTC
TO_CHAR(EFFECTIVE_DAY,'DD-MON- DUMP(EFFECTIVE_DAY)
1 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
2 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
3 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
4 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
5 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
6 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
7 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
8 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
9 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
10 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
11 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
12 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
13 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
14 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
15 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
16 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
17 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
18 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
19 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
20 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
21 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
22 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
23 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
24 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
25 29-feb-2012 00:00:00 Typ=12 Len=7: 120,112,2,29,1,1,1
April 26, 2013 - 4:30 pm UTC
ahh, I suspect a busted index.
do one of your plans have an index range scan and the other a full scan?
can you:
analyze index index_name validate structure;
(that'll lock it - be aware of that).
Serializable Transaction on Interval Partitioned tables
Rajeshwaran Jeyabal, May 01, 2013 - 12:07 pm UTC
Tom:
I was reading VLDB guide on "When to Use Interval Partitioning"
<quote>
Serializable transactions do not work with interval partitioning. Inserting data into a partition of an interval partitioned table that does not have a segment yet causes an error.
</quote>
I don't see the above statement happens. I am on 11.2.0.2. do you see any flaw in my test case?
create table t
( x int,
y varchar2(20) )
partition by range(x)
interval (1)
( partition p0 values less than (0) );
insert into t(x,y) values(1,'a');
commit;
rajesh@ORA11G> alter session set isolation_level=serializable;
Session altered.
rajesh@ORA11G> insert into t(x,y) values(2,'a');
1 row created.
rajesh@ORA11G> commit;
Commit complete.
rajesh@ORA11G>
http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm#BJEFIFCE
May 06, 2013 - 3:39 pm UTC
i've filed a doc issue against this. they say the same thing about deferred segment creation and that isn't true either.
Interval partitioning on sub partitions
satish, July 22, 2013 - 11:38 am UTC
Is it possible to create sub-partitions as interval partitions? I have table which is list partitioned and each partition sub partitioned by date range.We want to know if those range partitions can be created automatically using interval option.
July 31, 2013 - 4:21 pm UTC
A reader, July 25, 2016 - 8:24 am UTC
How to partition a table in interval on a column which does not contain consequence number like suppose talk about empa-id is like 1 2 3 and so on. but i want to interval partition on emp-id in my case is 1 30 73 190 200 7883 398489 .... so on.
July 25, 2016 - 6:59 pm UTC
That doesn't sound like a particularly useful column to partition by (perhaps look at hash partitioning), but if you really wanted to, you simply choose a sensible range, eg
partition by range ( empid)
interval (50000)
to have ranges of employee numbers of size n to n+50000
A reader, July 26, 2016 - 5:21 pm UTC
Does partition key contain null value?
Because I have table contain only 3 value of a column like null,1 and 2. Which is flag value.
July 26, 2016 - 8:41 pm UTC
Partition keys can be null - depending on how you've defined your partitions!
SQL> create table t (
2 x int
3 ) partition by range (x) (
4 partition p0 values less than (10)
5 );
Table created.
SQL> insert into t values (null);
insert into t values (null)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
SQL>
SQL> drop table t purge;
Table dropped.
SQL> create table t (
2 x int
3 ) partition by range (x) (
4 partition p0 values less than (10),
5 partition pmax values less than (maxvalue)
6 );
Table created.
SQL> insert into t values (null);
1 row created.
SQL>
SQL> drop table t purge;
Table dropped.
SQL> create table t (
2 x int
3 ) partition by list (x) (
4 partition p0 values (0)
5 );
Table created.
SQL> insert into t values (null);
insert into t values (null)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
SQL>
SQL> drop table t purge;
Table dropped.
SQL> create table t (
2 x int
3 ) partition by list (x) (
4 partition p0 values (0, null)
5 );
Table created.
SQL> insert into t values (null);
1 row created.
SQL>
SQL>
SQL> drop table t purge;
Table dropped.
SQL> create table t (
2 x int
3 ) partition by hash (x) partitions 2;
Table created.
SQL> insert into t values (null);
1 row created.
Interval + Reference partition in 12c
Rajeshwaran, Jeyabal, September 02, 2019 - 12:00 pm UTC
Team,
was reading about Interval + Reference partition in Oracle 12c from docs @
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/partition-create-tables-indexes.html#GUID-52397EF9-EC3B-4E80-8211-77E683263530 the below demo was from 12.2
demo@PDB1> create table p(
2 order_id number primary key,
3 order_date date,
4 data varchar2(30) )
5 enable row movement
6 partition by range(order_date)
7 interval( numtoyminterval(1,'month') )
8 ( partition p0 values less than
9 (to_date('01-jan-2012','dd-mon-yyyy')) );
Table created.
demo@PDB1> create table c1(
2 order_id number,
3 line_id number,
4 data varchar2(30),
5 constraint c1_pk primary key(order_id,line_id),
6 constraint c1_fk foreign key(order_id)
7 references p )
8 enable row movement
9 partition by reference(c1_fk) ;
Table created.
demo@PDB1> create table c2(
2 order_id number,
3 line_id number,
4 sub_line_id number,
5 data varchar2(30),
6 constraint c2_pk primary key(order_id,line_id,sub_line_id),
7 constraint c2_fk foreign key(order_id,line_id)
8 references c1 )
9 enable row movement
10 partition by reference(c2_fk) ;
partition by reference(c2_fk)
*
ERROR at line 10:
ORA-14652: reference partitioning foreign key is not supported
demo@PDB1>Is it not possible to create Interval + Reference partition on more than on PK-FK hierarchy? Kindly advice.
September 02, 2019 - 2:52 pm UTC
All the FK columns must be NOT NULL when using reference partitioning. The second child table doesn't appear to be picking up the mandatory-ness of these columns from the primary key for some reason.
Explicitly declaring them as NOT NULL does the trick for me:
create table p (
order_id number primary key,
order_date date,
data varchar2(30)
)
enable row movement
partition by range(order_date)
interval( numtoyminterval(1,'month') ) (
partition p0 values less than (
to_date('01-jan-2012','dd-mon-yyyy')
)
);
create table c1(
order_id number,
line_id number,
data varchar2(30),
constraint c1_pk primary key(order_id,line_id),
constraint c1_fk foreign key(order_id)
references p
)
enable row movement
partition by reference(c1_fk) ;
create table c2(
order_id number not null,
line_id number not null,
sub_line_id number,
data varchar2(30),
constraint c2_pk
primary key(order_id,line_id,sub_line_id),
constraint c2_fk
foreign key(order_id,line_id)
references c1
)
enable row movement
partition by reference(c2_fk) ;
select partition_name
from user_tab_partitions
where table_name = 'C2';
PARTITION_NAME
P0
Exchange cascade on interval + reference partition tables
Rajeshwaran, Jeyabal, September 03, 2019 - 9:22 am UTC
Thanks the above works.
However the documentation
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/maintenance-partition-tables-indexes.html#GUID-93BFB499-39A6-4DDE-88C3-BF7A63848E25 has this
<quote>
....
The exchange operation can be targeted at any level in a reference partitioned hierarchy and cascades to child tables starting from the targeted table.
....
</quote>
Here is my testcase from 12.2 that got error. could you help us to understand, what is missing/mismatch with FK on constraints?
demo@PDB1> create table p(
2 order_id number primary key,
3 order_date date,
4 data varchar2(30) )
5 partition by range(order_date)
6 interval( numtoyminterval(1,'month') )
7 ( partition p0 values less than
8 (to_date('01-jan-2012','dd-mon-yyyy')) );
Table created.
demo@PDB1> create table c1(
2 order_id number,
3 line_id number,
4 data varchar2(30),
5 constraint c1_pk primary key(order_id,line_id),
6 constraint c1_fk foreign key(order_id)
7 references p )
8 partition by reference(c1_fk) ;
Table created.
demo@PDB1> create table c2(
2 order_id number not null,
3 line_id number not null,
4 sub_line_id number,
5 data varchar2(30),
6 constraint c2_pk primary key(order_id,line_id,sub_line_id),
7 constraint c2_fk foreign key(order_id,line_id)
8 references c1
9 on delete cascade )
10 partition by reference(c2_fk) ;
Table created.
demo@PDB1> create table p_temp(
2 order_id number primary key,
3 order_date date,
4 data varchar2(30) );
Table created.
demo@PDB1>
demo@PDB1> create table c1_temp(
2 order_id number,
3 line_id number,
4 data varchar2(30),
5 constraint c1_temp_pk primary key(order_id,line_id),
6 constraint c1_temp_fk foreign key(order_id)
7 references p_temp );
Table created.
demo@PDB1> create table c2_temp(
2 order_id number not null,
3 line_id number not null,
4 sub_line_id number,
5 data varchar2(30),
6 constraint c2_temp_pk primary key(order_id,line_id,sub_line_id),
7 constraint c2_temp_fk foreign key(order_id,line_id)
8 references c1_temp
9 on delete cascade) ;
Table created.
demo@PDB1> select table_name,partition_name,interval
2 from user_tab_partitions
3 where table_name in ('P','C1','C2')
4 order by 1,2;
TABLE_NAME PARTITION_ INT
---------- ---------- ---
C1 P0 NO
C2 P0 NO
P P0 NO
demo@PDB1> insert into p(order_id,order_date,data)
2 values(1,to_date('17-aug-2019','dd-mon-yyyy'),'*');
1 row created.
demo@PDB1> insert into c1(order_id,line_id,data)
2 values(1,1,'*');
1 row created.
demo@PDB1> insert into c2(order_id,line_id,sub_line_id,data)
2 values(1,1,1,'*');
1 row created.
demo@PDB1> select table_name,partition_name,interval
2 from user_tab_partitions
3 where table_name in ('P','C1','C2')
4 order by 1,2;
TABLE_NAME PARTITION_ INT
---------- ---------- ---
C1 P0 NO
C1 SYS_P3548 YES
C2 P0 NO
C2 SYS_P3548 YES
P P0 NO
P SYS_P3548 YES
6 rows selected.
demo@PDB1> alter table c1
2 exchange partition SYS_P3548
3 with table c1_temp
4 CASCADE ;
alter table c1
*
ERROR at line 1:
ORA-14128: FOREIGN KEY constraint mismatch in ALTER TABLE EXCHANGE PARTITION
demo@PDB1>
September 03, 2019 - 12:45 pm UTC
The FK of the table you're exchanging needs to point to the parent partitioned table:
create table p(
order_id number primary key,
order_date date,
data varchar2(30)
) partition by range(order_date)
interval( numtoyminterval(1,'month') ) (
partition p0 values less than
(to_date('01-jan-2012','dd-mon-yyyy'))
);
create table c1(
order_id number not null,
line_id number not null,
data varchar2(30),
constraint c1_pk primary key(order_id,line_id),
constraint c1_fk foreign key(order_id)
references p
on delete cascade
)
partition by reference(c1_fk) ;
create table c2(
order_id number not null,
line_id number not null,
sub_line_id number,
data varchar2(30),
constraint c2_pk primary key(order_id,line_id,sub_line_id),
constraint c2_fk foreign key(order_id,line_id)
references c1
on delete cascade )
partition by reference(c2_fk) ;
create table c1_temp(
order_id number not null,
line_id number not null,
data varchar2(30),
constraint c1_temp_pk primary key(order_id,line_id),
constraint c1_temp_fk foreign key(order_id)
references p
on delete cascade
);
create table c2_temp(
order_id number not null,
line_id number not null,
sub_line_id number,
data varchar2(30),
constraint c2_temp_pk primary key(order_id,line_id,sub_line_id),
constraint c2_temp_fk foreign key(order_id,line_id)
references c1_temp
on delete cascade
) ;
select table_name,partition_name,interval
from user_tab_partitions
where table_name in ('P','C1','C2')
order by 1,2;
insert into p(order_id,order_date,data)
values(1,to_date('17-aug-2019','dd-mon-yyyy'),'*');
insert into c1(order_id,line_id,data)
values(1,1,'*');
insert into c2(order_id,line_id,sub_line_id,data)
values(1,1,1,'*');
select table_name,partition_name,interval
from user_tab_partitions
where table_name in ('P','C1','C2')
order by 1,2;
TABLE_NAME PARTITION_NAME INTERVAL
C1 P0 NO
C1 SYS_P9274 YES
C2 P0 NO
C2 SYS_P9274 YES
P P0 NO
P SYS_P9274 YES
alter table c1
exchange partition SYS_P9274
with table c1_temp
cascade
update indexes;
select * from c1_temp;
ORDER_ID LINE_ID DATA
1 1 *
select * from c2_temp;
ORDER_ID LINE_ID SUB_LINE_ID DATA
1 1 1 *
About not to use a date in the partitioning scheme
Shane, October 08, 2019 - 8:54 am UTC
Hi Tom,
Why you said that "Do not use a date in the partitioning scheme then, just use a number with interval 1."? Is there any concerns about using date or timestamp as the partition key?
Thank you very much.
October 08, 2019 - 9:50 am UTC
Sure you can use date/timestamp for interval partitioning.
But read the question:
But please note that release_date column is having number data type (as per design)
...
They want data type *NOT* to be altered.
Interval Partitioning is not splitting partition after the reset of INTERVAL
Venkat, March 01, 2022 - 9:07 am UTC
I have a scenario where interval partition is not creating the MONTH partitions after the reset of INTERVAL to null and setting it back again to MONTH(which is done during the the purging of the older partitions) for the rows updated/inserted in the existing partition. Could you show some light on this.? Am I missing anything here? Is there a way to resolve this on a live table without outage?
Appreciate your valuable comments
SQL> SELECT BANNER FROM V$VERSION;
BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
SQL> CREATE TABLE tab_prtn_1(col1 number, col2 date, col3 date)
2 PARTITION BY RANGE(col2)
3 interval (NUMTOYMINTERVAL(1,'MONTH'))
4 (
5 PARTITION VALUES LESS THAN (TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
6 )
7 enable row movement;
Table created.
SQL> insert into tab_prtn_1(col1, col2, col3) values (1, sysdate, sysdate+5);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> Select table_name, partition_name, high_value, high_value_length, partition_position, initial_extent, next_extent From User_tab_partitions Where Table_name='TAB_PRTN_1';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION INITIAL_EXTENT NEXT_EXTENT
---------- --------------- -------------------------------------------------------------------------------- ----------------- ------------------ -------------- -----------
TAB_PRTN_1 SYS_P1463991 TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 1
TAB_PRTN_1 SYS_P1463992 TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 2 8388608 1048576
SQL> Update tab_prtn_1
2 set col2 = '01-Feb-2022';
1 row updated.
SQL> commit;
Commit complete.
SQL>
SQL> Select table_name, partition_name, high_value, high_value_length, partition_position, initial_extent, next_extent From User_tab_partitions Where Table_name='TAB_PRTN_1';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION INITIAL_EXTENT NEXT_EXTENT
---------- --------------- -------------------------------------------------------------------------------- ----------------- ------------------ -------------- -----------
TAB_PRTN_1 SYS_P1463991 TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 1
TAB_PRTN_1 SYS_P1463993 TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 2 8388608 1048576
TAB_PRTN_1 SYS_P1463992 TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 3 8388608 1048576
SQL>
SQL>
SQL> insert into tab_prtn_1(col1, col2, col3) values (1, add_months(trunc(sysdate),5), sysdate+5);
1 row created.
SQL> commit;
Commit complete.
SQL> select col2, count(1) from tab_prtn_1 group by col2;
COL2 COUNT(1)
-------------------- ----------
01-FEB-22 1
01-AUG-22 1
SQL> Select table_name, partition_name, high_value, high_value_length, partition_position, initial_extent, next_extent From User_tab_partitions Where Table_name='TAB_PRTN_1';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION INITIAL_EXTENT NEXT_EXTENT
---------- --------------- -------------------------------------------------------------------------------- ----------------- ------------------ -------------- -----------
TAB_PRTN_1 SYS_P1463991 TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 1
TAB_PRTN_1 SYS_P1463993 TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 2 8388608 1048576
TAB_PRTN_1 SYS_P1463992 TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 3 8388608 1048576
TAB_PRTN_1 SYS_P1463994 TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 4 8388608 1048576
SQL> ALTER TABLE TAB_PRTN_1 SET INTERVAL();
Table altered.
SQL>
SQL> ALTER TABLE TAB_PRTN_1 SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));
Table altered.
SQL> Update tab_prtn_1
2 set col2 = '01-Jun-2022'
3 Where trunc(col2) = '01-Aug-2022';
1 row updated.
SQL> commit;
Commit complete.
SQL> select col2, count(1) from tab_prtn_1 group by col2;
COL2 COUNT(1)
-------------------- ----------
01-FEB-22 1
01-JUN-22 1
SQL>
SQL> Select table_name, partition_name, high_value, high_value_length, partition_position, initial_extent, next_extent From User_tab_partitions Where Table_name='TAB_PRTN_1';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION INITIAL_EXTENT NEXT_EXTENT
---------- --------------- -------------------------------------------------------------------------------- ----------------- ------------------ -------------- -----------
TAB_PRTN_1 SYS_P1463991 TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 1
TAB_PRTN_1 SYS_P1463993 TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 2 8388608 1048576
TAB_PRTN_1 SYS_P1463992 TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 3 8388608 1048576
TAB_PRTN_1 SYS_P1463994 TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 4 8388608 1048576
SQL>
SQL>
SQL> insert into tab_prtn_1(col1, col2, col3) values (1, add_months(trunc(sysdate),3), sysdate+5);
1 row created.
SQL> commit;
Commit complete.
SQL> Select table_name, partition_name, high_value, high_value_length, partition_position, initial_extent, next_extent From User_tab_partitions Where Table_name='TAB_PRTN_1';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION INITIAL_EXTENT NEXT_EXTENT
---------- --------------- -------------------------------------------------------------------------------- ----------------- ------------------ -------------- -----------
TAB_PRTN_1 SYS_P1463991 TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 1
TAB_PRTN_1 SYS_P1463993 TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 2 8388608 1048576
TAB_PRTN_1 SYS_P1463992 TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 3 8388608 1048576
TAB_PRTN_1 SYS_P1463994 TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 4 8388608 1048576
SQL> select col2, count(1) from tab_prtn_1 group by col2;
COL2 COUNT(1)
-------------------- ----------
01-FEB-22 1
01-JUN-22 2
SQL> insert into tab_prtn_1(col1, col2, col3) values (1, add_months(trunc(sysdate),4), sysdate+5);
1 row created.
SQL> commit;
Commit complete.
SQL> Select table_name, partition_name, high_value, high_value_length, partition_position, initial_extent, next_extent From User_tab_partitions Where Table_name='TAB_PRTN_1';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION INITIAL_EXTENT NEXT_EXTENT
---------- --------------- -------------------------------------------------------------------------------- ----------------- ------------------ -------------- -----------
TAB_PRTN_1 SYS_P1463991 TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 1
TAB_PRTN_1 SYS_P1463993 TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 2 8388608 1048576
TAB_PRTN_1 SYS_P1463992 TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 3 8388608 1048576
TAB_PRTN_1 SYS_P1463994 TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 4 8388608 1048576
SQL> select col2, count(1) from tab_prtn_1 group by col2;
COL2 COUNT(1)
-------------------- ----------
01-FEB-22 1
01-JUL-22 1
01-JUN-22 2
SQL> SPOOL OFF
March 01, 2022 - 2:08 pm UTC
When you set an empty interval, the database converts all existing interval partitions to be non-interval partitions:
create table t (
c1 int, c2 date
) partition by range ( c2 )
interval ( interval '1' month ) (
partition p0 values less than ( date'2022-01-01' )
);
insert into t values ( 1, sysdate );
select partition_name, interval, high_value
from user_tab_partitions
where table_name='T';
PARTITION_ INT HIGH_VALUE
---------- --- --------------------------------------------------------------------------------
P0 NO TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P10141 YES TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
alter table t set interval ();
alter table t set interval ( interval '1' month );
select partition_name, interval, high_value
from user_tab_partitions
where table_name='T';
PARTITION_ INT HIGH_VALUE
---------- --- --------------------------------------------------------------------------------
P0 NO TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P10141 NO TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIABecause they're NOT interval partitions anymore, the database doesn't split them when you insert new values. You have to do this manually.
You can do this online from 12.2:
alter table ...
split partition ...
online
the reset of INTERVAL to null and setting it back again to MONTHWhy are you doing this?
Venkat, March 01, 2022 - 4:44 pm UTC
Dear Chris,
Thanks a lot.
I was analyzing it and could not find the rationale behind the implementation setting it to null.
Instead of doing the splitting manually (using online approach), was thinking of any short ways to revert the table back to it old state (interval partition) as it is already in live.