In Oracle 12C ALTER TABLE … MOVE PARTITION has been extended with new clause ONLINE.

ALTER TABLE <table name> 
MOVE PARTITION <partition name> ONLINE

ONLINE clause indicates that DML on the table partition will be allowed while moving the table partition.

Global indexes are maintained during the move partition, so a manual index rebuild is no longer required.

Continue reading →

In 12C Oracle has extended 11G reference partitioning. Now It’s possible to use interval partitioned tables as parent tables for reference partitioning.

Requirements and features

• Requires compatibility set to 12.0.0.0 or higher
• ALTER TABLE SET INTERVAL can be set for parent but not for reference-partitioned child tables
• ALTER TABLE SET STORE IN can be set for parent but not for reference-partitioned child tables
• ALTER TABLE SPLIT PARTITION that transforms interval partitions to conventional partitions in parent table construct the corresponding transformation in the child table, creating partitions in the child table as necessary.

Let’s create simple example. Parent table parent_tbl is using RANGE INTERVAL partitioning and child table child_tbl are reference partitioned. Such combination was not possible in previous releases 11g.

create table parent_tbl
(
  id1 number, 
  id2 number,
  constraint parent_tbl_pk primary key(id1)
)
partition by range(id2) INTERVAL(10)
(
  partition p1 values less than (10)
);

create table child_tbl
(
  id1 number, 
  id2 number not null,
  constraint child_tbl_fk foreign key(id2) references parent_tbl
)
partition by REFERENCE(child_tbl_fk);

Continue reading →

In Oracle 12c new word CASCADE can be specified for TRUNCATE and EXCHANGE command for “master” partitioned tables which are referenced by enabled ON DELETE CASCADE referential constraint.

Truncate Cascade for partitioned tables

If you specify TRUNCATE … CASCADE for “master” partitioned table then Oracle Database truncates all child reference tables that reference master partitioned table with an enabled ON DELETE CASCADE referential constraint. This is a recursive operation that will truncate all child reference tables, grandchild reference tables, and so on, using the specified options.

Following options are available for “master” partitioned tables:

1. In this case child, grandchild tables don’t need to be partitioned

TRUNCATE <table_name> CASCADE

2. In this case child, grandchild tables must be reference partitioned tables

ALTER TABLE <table_name> 
TRUNCATE PARTITION <partition_name> CASCADE

ALTER TABLE <table_name> 
TRUNCATE SUBPARTITION <subpartition_name> CASCADE

Continue reading →

In Oracle 12c new word CASCADE can be specified for TRUNCATE command.

TRUNCATE <table_name> CASCADE

If you specify CASCADE, then Oracle Database truncates all child tables that reference TABLE with an enabled ON DELETE referential constraint. This is a recursive operation that will truncate all child tables, grandchild tables, and so on, using the specified options.

Continue reading →

Partial indexing enables to create local and global indexes on a subset of partitions, subpartitions of a table. When a table is created or altered, a default indexing property can be specified for the table or its partitions.

New attribute INDEXING ON|OFF can be specified on table(it becomes default attribute for its partitions and subpartitions), partition(it becomes default attribute for its subpartitions) or subpartition level. The attribute is important only for partial indexing.

• INDEXING ON (default) – tells to consider data for partial indexing
• INDEXING OFF – tells to ignore data for partial indexing.

New attribute INDEXING PARTIAL|FULL can be specified during index creation

• INDEXING FULL (default) – in this case data from all table partitions/subpartitions are used during index creation no matter of partition/subpartition attribute INDEXING ON/OFF.
• INDEXING PARTIAL – in this case only data from partitions/subpartitions with INDEXING ON are used during index creation and data from partitions/subpartitions with INDEXING OFF are ignored.

Continue reading →

This article presents enhancements for maintenance operations on multiple partitions in Oracle 12C.

Following multiple operations using just one single command are now possible in Oracle 12C:

• ADD multiple partitions and subpartitions
• DROP multiple partitions and subpartitions
• MERGE multiple partitions and subpartitions
• TRUNCATE multiple partitions

This feature is beautiful extension I really love it 🙂

Continue reading →

It’s fantastic new feature in 12C. In this release, a data file can now be moved online while it is open and being accessed.

alter database move datafile 
'<file_to_move_old_location>' to '<file_to_move_new_location';

Example of potential use of it:

• Move the data files from one type of storage to another
• Move data files that are accessed infrequently to lower cost storage
• Make a tablespace read-only and move its data files to write-once storage
• Move a database into Oracle Automatic Storage Management (Oracle ASM)

Continue reading →

In Oracle 12C you can create multiple indexes on the same set of columns as long as some characteristic is different like:

• Unique versus nonunique
• B-tree versus bitmap
• Different partitioning strategies
  • Indexes that are not partitioned and indexes that are partitioned
  • Indexes that are locally partitioned and indexes that are globally partitioned
  • Indexes that differ in partitioning type (range or hash)

It’s very helpful feature if you want to quickly migrate to different type of index or use them during different period of time.

NOTE – to create such indexes you need to use INVISIBLE clause.

Continue reading →