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