direct path insert: does partition pruning syntax (as opposed to explicit partition specification) require exclusive TM lock?

test_append_locks_part_for_vs_pruning.log

21:42:42 SQL> set time off
SQL> set timi off
SQL> 
SQL> col banner for a100
SQL> col object_name for a15
SQL> col payload for a150
SQL> col subobject_name for a15
SQL> col trace_filename for a40
SQL> 
SQL> set lines 300
SQL> 
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

1 row selected.

SQL> 
SQL> drop table dropme_target purge;

Table dropped.

SQL> drop table dropme_source purge;

Table dropped.

SQL> 
SQL> whenever sqlerror exit failure
SQL> 
SQL> create table dropme_target (part_key int)
  2  partition by range (part_key)
  3  (
  4      partition p0 values less than (1),
  5      partition p1 values less than (2),
  6      -- can't do MAXVALUE because I want it
  7      -- to be interval-partitioned going forward
  8      partition p3 values less than (3)
  9  );

Table created.

SQL> 
SQL> -- most common case - we're going to insert neither in first,
SQL> -- nor in last partition
SQL> create table dropme_source as select 1 part_key from dual;

Table created.

SQL> 
SQL> -- aka 10704
SQL> alter session set events 'trace[ksq] disk medium';

Session altered.

SQL> 
SQL> -- test 1: RANGE table, "partition for" syntax
SQL> alter session set tracefile_identifier = 'range_part_for';

Session altered.

SQL> insert /*+append*/ into dropme_target partition for (1) select * from dropme_source;

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> -- test 2: RANGE table, pruning
SQL> alter session set tracefile_identifier = 'range_pruning';

Session altered.

SQL> insert /*+append*/ into (select * from dropme_target where part_key = 1) select * from dropme_source;

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> -- convert the table to interval-partitioned
SQL> alter table dropme_target set interval (1);

Table altered.

SQL> 
SQL> -- test 3: INTERVAL table, "partition for" syntax
SQL> alter session set tracefile_identifier = 'interval_part_for';

Session altered.

SQL> insert /*+append*/ into dropme_target partition for (1) select * from dropme_source;

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> -- test 4: INTERVAL table, pruning
SQL> alter session set tracefile_identifier = 'interval_pruning';

Session altered.

SQL> insert /*+append*/ into (select * from dropme_target where part_key = 1) select * from dropme_source;

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> break on trace_filename skip 1
SQL> 
SQL> select
  2      trc.trace_filename,
  3      uo.object_name,
  4      uo.subobject_name,
  5      to_char(object_id,'0XXXXXXX') object_id_hex,
  6      trc.payload
  7  from user_objects uo
  8  join v$diag_trace_file_contents trc on 1 = 1
  9      and (trc.session_id, trc.serial#) = (select sid, serial# from v$session where sid = userenv('sid'))
 10      and trc.payload like '%TM-' || to_char(uo.object_id, 'FM0XXXXXXX') || '%'
 11  where uo.object_name = 'DROPME_TARGET'
 12  order by trc.timestamp;

TRACE_FILENAME                           OBJECT_NAME     SUBOBJECT_NAME  OBJECT_ID PAYLOAD
---------------------------------------- --------------- --------------- --------- ------------------------------------------------------------------------------------------------------------------------------------------------------
ORCLCDB_ora_1408_range_part_for.trc      DROPME_TARGET                    0001215F 2022-06-04 14:42:42.598*:ksq.c@9175:ksqgtlctx(): *** TM-0001215F-00000000-989BDBE4-00000000 mode=3 flags=0x401 why=173 timeout=21474836 ***
                                         DROPME_TARGET   P1               00012161 2022-06-04 14:42:42.598*:ksq.c@9175:ksqgtlctx(): *** TM-00012161-00000000-989BDBE4-00000000 mode=6 flags=0x401 why=173 timeout=21474836 ***

ORCLCDB_ora_1408_range_pruning.trc       DROPME_TARGET                    0001215F 2022-06-04 14:42:42.627*:ksq.c@9175:ksqgtlctx(): *** TM-0001215F-00000000-989BDBE4-00000000 mode=6 flags=0x401 why=173 timeout=21474836 ***
                                         DROPME_TARGET                    0001215F 2022-06-04 14:42:42.643*:ksq.c@9175:ksqgtlctx(): *** TM-0001215F-00000000-989BDBE4-00000000 mode=6 flags=0x401 why=173 timeout=0 ***

ORCLCDB_ora_1408_interval_part_for.trc   DROPME_TARGET                    0001215F 2022-06-04 14:42:42.659*:ksq.c@9175:ksqgtlctx(): *** TM-0001215F-00000000-989BDBE4-00000000 mode=3 flags=0x401 why=173 timeout=21474836 ***
                                         DROPME_TARGET   P1               00012161 2022-06-04 14:42:42.659*:ksq.c@9175:ksqgtlctx(): *** TM-00012161-00000000-989BDBE4-00000000 mode=6 flags=0x401 why=173 timeout=21474836 ***

ORCLCDB_ora_1408_interval_pruning.trc    DROPME_TARGET                    0001215F 2022-06-04 14:42:42.690*:ksq.c@9175:ksqgtlctx(): *** TM-0001215F-00000000-989BDBE4-00000000 mode=6 flags=0x401 why=173 timeout=21474836 ***


7 rows selected.

SQL> 
SQL> exit

test_append_locks_part_for_vs_pruning.sql

cl scr

spool test_append_locks_part_for_vs_pruning.log replace

set echo on
set time off 
set timi off

col banner for a100
col object_name for a15
col payload for a150
col subobject_name for a15
col trace_filename for a40

set lines 300

select banner from v$version;

drop table dropme_target purge;
drop table dropme_source purge;

whenever sqlerror exit failure

create table dropme_target (part_key int)
partition by range (part_key)
(
    partition p0 values less than (1),
    partition p1 values less than (2),
    -- can't do MAXVALUE because I want it 
    -- to be interval-partitioned going forward
    partition p3 values less than (3) 
);

-- most common case - we're going to insert neither in first,
-- nor in last partition
create table dropme_source as select 1 part_key from dual;

-- aka 10704
alter session set events 'trace[ksq] disk medium';

-- test 1: RANGE table, "partition for" syntax
alter session set tracefile_identifier = 'range_part_for';
insert /*+append*/ into dropme_target partition for (1) select * from dropme_source;
commit;

-- test 2: RANGE table, pruning
alter session set tracefile_identifier = 'range_pruning';
insert /*+append*/ into (select * from dropme_target where part_key = 1) select * from dropme_source;
commit;

-- convert the table to interval-partitioned
alter table dropme_target set interval (1);

-- test 3: INTERVAL table, "partition for" syntax
alter session set tracefile_identifier = 'interval_part_for';
insert /*+append*/ into dropme_target partition for (1) select * from dropme_source;
commit;

-- test 4: INTERVAL table, pruning
alter session set tracefile_identifier = 'interval_pruning';
insert /*+append*/ into (select * from dropme_target where part_key = 1) select * from dropme_source;
commit;

break on trace_filename skip 1

select    
    trc.trace_filename,
    uo.object_name,
    uo.subobject_name,
    to_char(object_id,'0XXXXXXX') object_id_hex,
    trc.payload
from user_objects uo
join v$diag_trace_file_contents trc on 1 = 1
    and (trc.session_id, trc.serial#) = (select sid, serial# from v$session where sid = userenv('sid'))    
    and trc.payload like '%TM-' || to_char(uo.object_id, 'FM0XXXXXXX') || '%'
where uo.object_name = 'DROPME_TARGET'
order by trc.timestamp;

exit