Do Not Use Parallel Query In ALTER TABLE MODIFY (column Not Null)
(Doc ID 2264469.1)
Last updated on OCTOBER 16, 2019
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
You are modifying table with mutli billion rows. Specific column was nullable and became not null
The time to change constraint takes around 40 minutes.
Mainly due to execution
SELECT /* all_rows ordered dynamic_sampling(2) */ A.rowid,:1,:2,:3 from "<user_name>"."<table_name>" A WHERE ( "<COL1>" is NULL)
this query is running without parallelism
You specifically run
ALTER SESSION FORCE PARALLEL DML PARALLEL 8
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8
But it still does not use parallelism
Any option to force parallelism?
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document