August 31, 2016 - Oracle Findings

With the release 12c Oracle introduced the “online partition movement” making the operation a non-blocking DDL command. This means that it’s possible to run any DML on a partition while the same is being “moved” using the new ONLINE option.
Let’s see how that works on our usual test table populated with 1 million rows in the first partition

-- Create table
drop table mytab;
create table mytab(id       number(10),
                   run_date date,
                   status   varchar2(100))
partition by range(run_date)
(partition P1 values less than (date '2016-01-02'),
 partition P2 values less than (date '2016-01-03')
);

-- Populate data
insert into MYTAB select rownum, date '2016-01-01', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 1000000;
insert into MYTAB select rownum, date '2016-01-02', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 100;
commit;

-- Create one local and one global index
create index MYTAB_IDX_L on MYTAB(id) local;
create index MYTAB_IDX_G on MYTAB(status);

-- Create table

drop table mytab;

create table mytab(id number(10),

run_date date,

status varchar2(100))

partition by range(run_date)

(partition P1 values less than (date '2016-01-02'),

partition P2 values less than (date '2016-01-03')

);

-- Populate data

insert into MYTAB select rownum, date '2016-01-01', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 1000000;

insert into MYTAB select rownum, date '2016-01-02', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 100;

commit;

-- Create one local and one global index

create index MYTAB_IDX_L on MYTAB(id) local;

create index MYTAB_IDX_G on MYTAB(status);

We can now take a look at how move partition works normally when not running it in ONLINE mode.
Let’s execute

alter table MYTAB move partition P1 compress for OLTP;

1	alter table MYTAB move partition P1 compress for OLTP;