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),
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;
-- 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.
alter table MYTAB move partition P1 compress for OLTP;
Not everybody knows that in SQL Plus the character # (called hash or pound) has a special use that I discovered it myself not much time ago. Well, it’s not really a secret, you can read about it on the official SQL Plus manual, but it’s rarely used. The sign #, in fact, is the default character used as SQLPREFIX during a SQL Plus session and can be changed executing the command
SET SQLPRE[FIX] prefix_char
But what does it do? It tells SQL Plus to execute a SQL Plus command while in the middle of entering a SQL statement or a Pl/Sql block of code.
For example you are writing a select statement
SQL> select empno, ename,
and, while writing down the list of columns to extract, you don’t remember the name of one of them. Normally you would quit writing the query, execute a desc of the table and start writing it again from scratch. But the # sign give us another possibility. All you have to do is enter a new line, digit the magic sign # and write the desc you need
Similarly as discussed in this post explaining how to enable partial unique indexes on Oracle 12c, it is possible to use partial indexes on Oracle 11g (tested on 126.96.36.199.0) as well! As you know this feature has been introduced with the Oracle release 12c but using the same method explained in the previous post we can apply it to 11g.