Oracle 12c New Features - Expert Consolidation in Oracle Database 12c - page 48

Some of the enhancements you will read about below do not require the use of Editions. This should not prevent
you from having a look at EBR because it is beautiful! Do you remember when an index could be rebuilt online for the
first time? No more requirements for a change window to rebuild indexes! The list of online operations available to the
database administrator has increased with Oracle 12c and you should know about them.
Moving a Partition without Blocking
Partitioning has seen quite a lot of improvement over the past releases. If you consider the new options allowing you
to combine partitioning schemes, interval partitioning, and referencing then you can see why Partitioning is such an
important option.
Oracle 12c has added many more enhancements, including the possibility to move a partition online. In versions
before Oracle 12c you had to wait for a compatible lock to move a partition. Consider the following example.
A simulated batch job is continuously inserting information into a partitioned table created as follows:
SQL> create table part_demo
2 partition by range (creation_date) (
3 partition p_2013 values less than (to_date('01.01.2013','dd.mm.yyyy')),
4 partition p_jan_2013 values less than (to_date('01.02.2013','dd.mm.yyyy')),
5 partition p_feb_2013 values less than (to_date('01.03.2013','dd.mm.yyyy')),
6 partition p_mar_2013 values less than (to_date('01.04.2013','dd.mm.yyyy')),
7 partition p_apr_2013 values less than (to_date('01.05.2013','dd.mm.yyyy')),
8 partition p_may_2013 values less than (to_date('01.06.2013','dd.mm.yyyy')),
9 partition p_jun_2013 values less than (to_date('01.07.2013','dd.mm.yyyy')),
10 partition p_jul_2013 values less than (to_date('01.08.2013','dd.mm.yyyy')),
11 partition p_aug_2013 values less than (to_date('01.09.2013','dd.mm.yyyy')),
12 partition p_sep_2013 values less than (to_date('01.10.2013','dd.mm.yyyy')),
13 partition p_oct_2013 values less than (to_date('01.11.2013','dd.mm.yyyy')),
14 partition p_nov_2013 values less than (to_date('01.12.2013','dd.mm.yyyy')),
15 partition p_dec_2013 values less than (to_date('01.01.2014','dd.mm.yyyy')),
16 partition p_def values less than (maxvalue))
17 enable row movement
18 as select sysdate+dbms_random.normal*100 as creation_date,
19* object_id, object_name from dba_objects
SQL> /
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'PART_DEMO')
Referential integrity constraints are then defined on the table. Simulating the batch job, the following little
snippet of code is executed.
SQL> begin
2 for i in 1..10000 loop
3 insert into part_demo (
4 creation_date,
5 object_name
6 ) values (
7 (to_date('02.05.2013','dd.mm.yyyy')+i/1440),
8 'test ' || i
9 );