Skip to Main Content
  • Questions
  • Exchange partition and concurrent dml on other partitions

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, tamir .

Asked: August 18, 2016 - 9:18 am UTC

Last updated: September 02, 2016 - 7:52 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi,
After searching on the internet and on your site and not finding any straight answer, i'm posting my question -
Is exchange of partition part_a on table1 will affect concurrent dml on other partitions, on the same table1?
Will the concurrent dml will be locked or stopped?
Is there any dml lock on the other partitions while issuing exchange partition command?

I'm asking this because my client prefer running delete command:
delete from table1 partition (part_a);
which takes a lot of time instead of running exchange partition (or truncate) command, fearing of locks on the other partitions...

Can you help me figure it out.
Thanks in advance,
Tamir.


and Chris said...

No, there are no locks on the other partitions when you run an exchange partition (at least in 11.2.0.4 and above).

Inserts on the target partition or table will block the exchange and are blocked by it.

You can verify this in your database by doing the following:

- Create a "big" table
- Create a partitioned table to exchange this into. Create a global index on this.
- Exchange the partitions, updating global indexes.
- Check v$lock.

The global index isn't strictly necessary. But it makes the exchange take longer. So it's easier to see the locks :)

create table t1 (
  x int,
  y int
);

create table t2 (
  x int,
  y int
) partition by list (x) (
  partition p1 values (1),
  partition p2 values (2)
);

insert /*+ append */into t1
  with rws as (
    select 1 x, rownum y from dual 
   connect by level <= 1000
  )
  select r1.x, r1.y from rws r1, rws;

commit;

-- repeat the following a few times so you have plenty of rows in t1
insert /*+ append */ into t1 
  select * from t1;

commit;

Once this is all setup, start three sessions.

Session 1
SQL> select sys_context('userenv', 'sid') from dual;

SYS_CONTEXT('USERENV','SID')
------------------------------------------------------------------------------

65

Elapsed: 00:00:00.49
SQL> alter table t2 exchange partition p1 with table t1 update global indexes;

Table altered.

Elapsed: 00:00:15.61


Session 2
SQL> select sys_context('userenv', 'sid') from dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------

138

Elapsed: 00:00:00.48
SQL> insert into t2 values (2, 2);

1 row created.

Elapsed: 00:00:00.37


Session 3 - check the locks!
select l.sid, uo.object_name, uo.subobject_name,
       l.lmode, l.request, l.block
from   V$LOCK l
join   user_objects uo 
on     uo.object_id = l.id1
where  sid in (138, 65)
order  by 1, 2, 3;

SID  OBJECT_NAME  SUBOBJECT_NAME  LMODE  REQUEST  BLOCK  
65   T1                           6      0        0      
65   T2           P1              6      0        0      
65   T2                           3      0        0      
138  T2           P2              3      0        0      
138  T2                           3      0        0

You can see that the only partition the exchange locks is P1. And neither of the sessions are blocked (block = 0). So both can continue at the same time.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.