Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Reader.

Asked: May 04, 2017 - 2:18 pm UTC

Last updated: May 11, 2017 - 5:26 am UTC

Version: 12.1.0.2 / 11g

Viewed 1000+ times

You Asked

Assumption:
1) INSERTS are continuously applied on table A

Requirement:
Need to exchange partition with another table but do not want to loose any data ( I am fine with the "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired" exception as there would be no data loss with the same)

Test Run:

drop TABLE a;

drop TABLE b;

drop sequence sq_tst1;

drop sequence sq_tst2;

CREATE TABLE a
(
id NUMBER,
dt DATE,
val VARCHAR2(10))
PARTITION BY RANGE(dt) (
partition e1 values less than (MAXVALUE)
);

create table b as select * from a where 1=2;

The below PL SQL blocks need to be executed in parallel and mid-way somewhere we can execute the exchange partition (as ALTER TABLE a EXCHANGE PARTITION e1 with table b;)

P1:

BEGIN
for i in 1 .. 1000000
loop
INSERT INTO a VALUES (sq_tst1.NEXTVAL, SYSDATE - (1000000 + i)/3600, 'Test');
COMMIT;
--DBMS_LOCK.SLEEP(1);

end loop;
end;
/

P2:

BEGIN
for i in 1 .. 1000000
loop
INSERT INTO a VALUES (sq_tst2.NEXTVAL, SYSDATE - (1000000 + i)/3600, 'Test');
COMMIT;
--DBMS_LOCK.SLEEP(1);

end loop;
end;
/

Is there a scenario wherein there could be a data loss? How much time should the exchange partition take ( in a worst case scenario)?



and Connor said...

You wont get data loss, eg

--
-- session 1
--
SQL> create sequence sq_tst1 cache 1000;

Sequence created.

SQL>
SQL> CREATE TABLE a
  2  (
  3  id NUMBER,
  4  dt DATE,
  5  val VARCHAR2(10))
  6  PARTITION BY RANGE(dt) (
  7  partition e1 values less than (MAXVALUE)
  8  );

Table created.

SQL>
SQL> create table b as select * from a where 1=2;

Table created.

SQL>
SQL> BEGIN
  2  for i in 1 .. 100000
  3  loop
  4  INSERT INTO a VALUES (sq_tst1.NEXTVAL, SYSDATE - (1000000 + i)/3600, 'Test');
  5  COMMIT;
  6  end loop;
  7  end;
  8  /
--
-- whilst running, in session 2
--
SQL> alter session set ddl_lock_timeout = 5;

Session altered.

SQL> alter table a exchange partition e1 with table b;

Table altered.


--
-- Session 1
--
PL/SQL procedure successfully completed.

SQL> select count(*) from a;

  COUNT(*)
----------
     66462

1 row selected.

SQL> select count(*) from b;

  COUNT(*)
----------
     33538

1 row selected.



So we still have our 100,000 rows exactly. Note that (as always) we never guarantee you wont drop a sequence value

SQL> select min(id), max(id) from b;

   MIN(ID)    MAX(ID)
---------- ----------
         1      33538

SQL> select min(id), max(id) from a;

   MIN(ID)    MAX(ID)
---------- ----------
     33540     100001



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.