RE: Interval Partitioned Table and Exchange Partition

From: Jaromir D.B.Nemec <jaromir_at_db-nemec.com>
Date: Tue, 26 Mar 2019 08:10:30 +0100
Message-ID: <005401d4e3a3$00841ea0$018c5be0$_at_db-nemec.com>

Hello Jonathan,

thanks for your insight.
I'd wait till this feature is "silently documented" and use the LOCK PARTITION in the meantime.

Kind Regards,

Jaromir

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

From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Sent: Montag, 25. März 2019 09:14
To: oracle-l_at_freelists.org; jaromir_at_db-nemec.com Subject: Re: Interval Partitioned Table and Exchange Partition

I added a primary key on part_tab to see if that made any difference - and inserted some data into the first partitioned of the partitioned table before the exchange. The code behaves on 18.3 and 19.x (LiveSQL).

The documents sometimes take (a lot of) time to catch up with the product. You're probably okay to assume it's supposed to be like this - but it's not as if including the code to do the lock first is complex of expensive if you want to play really safe.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jaromir D.B.Nemec <jaromir_at_db-nemec.com> Sent: 25 March 2019 06:03:19
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 Tue Mar 26 2019 - 08:10:30 CET