Enhanced Online DDL Capabilities Oracle Database 12C release 1 (12.1)

This article presents enhanced DDL capabilities in Oracle 12C

It’s possible to use ONLINE keyword to allow execution of DML statements during the following DDL operations:

  • SET COLUMN UNUSED
  • ALTER INDEX UNUSABLE
  • DROP INDEX/CONSTRAINT

DROP INDEX/CONSTRAINT

DROP INDEX <index_name> ONLINE FORCE;
ALTER TABLE <table_name> DROP CONSTRAINT <cons_pk or cons_uq> ONLINE;

ONLINE – indicates DML operations are allowed for table or partition while dropping index

FORCE – applies only for domain indexes if its routine invocation returns error or if the index is marked IN PROGRESS

Restrictions on dropping indexes:

  • can’t drop domain index if the index or a any of its partitions are marked IN PROGRESS
  • can’t specify ONLINE when dropping a domain index, a cluster index or an index on a queue table

Restrictions on dropping constraints:

  • can’t drop constraint with CASCADE
  • can’t drop referencing constraints

ALTER INDEX UNUSABLE

ALTER INDEX <index_name> UNUSABLE ONLINE;

UNUSABLE – marks index or its partitions or its subpartitions as unusable.

ONLINE – indicates DML operations are allowed on the table or partition while marking the index UNUSABLE.

SET UNUSED

ALTER TABLE <table_name> SET UNUSED (<column_name>) ONLINE;

ONLINE – indicates DML operations are allowed on the table or partition while marking the columns UNUSED.

Restrictions:

  • can’t be specified when marking a column with a DEFERRABLE constraint

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *