Oracle Database Discussions

Hi,

I'm running 10.2.0.4 Enterprise Edition and I have a need to set about 90 columns in a large (80GB, 142 million rows) range-partitioned table to NOT NULL. This database runs a vendor app that maintains its own data dictionary, and for that reason a constraint will not serve our needs -- the app expects sys.col$ to have a non-zero value for null$ for this column. Some of the columns are indexed, some are not, none have any primary/foreign keys.

Downtime is coming for the app in a couple months in order to upgrade the application. I can use some of that downtime to implement the NOT NULL change, but I would really like to reduce the amount of time necessary to make this change. To spec it out I ran "alter table owner.table modify (colname NOT NULL ENABLE)" for each column on a full-sized copy of the database running on identical hardware -- the whole thing took about 17 hours, and I could not come up with any syntax to make this run in parallel -- each column conversion ran in a single thread (with 16 dual-core CPUs and fast storage this should fly if I can make it run in parallel). The session holds an EXCLUSIVE TM lock on the table for the duration of each MODIFY statement, and SELECT statements against the table end up waiting so I can't just do each column one at a time in the middle of the night over the next several weeks.

I've tried 'alter session force parallel dml parallel NUM; alter session force parallel query parallel NUM; alter session force parallel ddl parallel NUM;" and that didn't make it run it parallel. I've done the same, with an "ALTER TABLE owner.table PARALLEL NUM" in there as well, no luck.

I know these columns do not contain any NULLs since I've modified the columns to NOT NULL repeatedly on copies of the database.

Modifying the column to NOT NULL ENABLE NOVALIDATE runs quickly, but doesn't flag the column as NOT NULL in describe output (or sys.col$ I assume).

What am I missing? How can I make the ALTER TABLE owner.tablename MODIFY (colname NOT NULL ENABLE) run in parallel?

I can use DBMS_REDEFINITION in parallel and copy the entire 80GB table in about one hour, or four with the indexes rebuilt as well. Is something like that the right solution? Create a new table, with the NOT NULL settings in place, start the DBMS_REDEFINITION.START_REDEF_TABLE, generate a ton of redo, then FINISH_REDEF_TABLE? That seems sick but might work. I hate the idea of moving 80GB plus indexes back and forth just to find a way to quickly flip the NOT NULL bit on a table I already know has no NULLs.

Edited by: BrianP on Mar 22, 2012 1:54 PM, change subject

Edited by: BrianP on Mar 22, 2012 2:34 PM edited again to note I did try 'alter session force parallel ddl parallel NUM;'