RE: Interval Partitioned Table and Exchange Partition

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 25 Mar 2019 09:23:35 -0400
Message-ID: <0ae401d4e30e$0b798e70$226cab50$_at_rsiz.com>

What, perchance, happens if you insert and rollback a single dummy row for the requisite partition?

Ponder this: Every new interval partition creation via insert that happens worldwide tests that code path to exhaustion.

Lock partition, on the other hand, seems likely to be less frequent. I'm not saying it shouldn't work.

I do know clearly which method I would use to pre-create a partition into which to exchange. (Regardless of documented guidance.)

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jaromir D.B.Nemec
Sent: Monday, March 25, 2019 2:03 AM
To: oracle-l_at_freelists.org
Subject: Interval Partitioned Table and Exchange Partition

Hello All,

I'm well aware that interval partitioned table adds a partition on INSERT, but for exchange partition, the corresponding partition must be pre-created using (for example) the LOCK PARTITION statement.

This is also well documented - up to Oracle 18 see the quote and links below:

However, exchanging a partition of an interval-partitioned table that has not been materialized in the data dictionary, meaning to have an explicit entry in the data dictionary beyond the interval definition, you must manually materialize the partition using the ALTER TABLE LOCK PARTITION command.

Oracle 12.1
https://docs.oracle.com/database/121/VLDBG/GUID-497B9404-C945-4665-B9B6-C673 85785BD3.htm

Oracle 12.2
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/mainte nance-partition-tables-indexes.html#GUID-497B9404-C945-4665-B9B6-C67385785BD 3

Oracle 18

https://docs.oracle.com/en/database/oracle/oracle-database/18/vldbg/maintena nce-partition-tables-indexes.html#GUID-497B9404-C945-4665-B9B6-C67385785BD3

While demonstrating this behaviour on 12.2 - to my great surprise - I did not receive the expected error ORA-02149: Specified partition does not exist, but the partition was perfectly created. See the script below:

---

select BANNER from v$version where BANNER like 'Oracle%';

BANNER

---

---

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

CREATE TABLE part_tab (
  trans_date DATE
)
PARTITION BY RANGE (trans_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(

   PARTITION part_01 values LESS THAN (DATE'2019-01-01') );

create table part_tmp
for exchange with table part_tab;

insert into part_tmp (trans_date)
values (DATE'2019-02-01');
commit;

alter table PART_TAB exchange partition FOR(DATE'2019-02-01') with table part_tmp;

select PARTITION_NAME, HIGH_VALUE,INTERVAL from user_tab_partitions where table_name ='PART_TAB' order by PARTITION_POSITION;

PARTITION_NAME HIGH_VALUE
INTERVAL

---

---

• -------- PART_01 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO SYS_P4069 TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES

  ---

So the question is, is this a documentation bug only (outdated documentation) and it is safe to use exchange partition on interval partitioned table to allocate new partitions or should some care be taken...

Kind Regards,

Jaromir Nemec

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 25 2019 - 14:23:35 CET