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 1SQL> 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 2SQL> 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.