Changing indexing is a blocking DDL operation including in 18c. You can verify this by running:
create table t (
c1 int, c2 int
) partition by list ( c1 ) (
partition p1 values ( 1 ) indexing off,
partition p2 values ( 2 ) indexing off,
partition p3 values ( 3 ) indexing off,
partition p4 values ( 4 ) indexing on
);
create index i2 on t ( c2 ) indexing partial;
Then in a second session, inserting a row, but not committing it:
insert into t values ( 1, 1 );
Then back in the first:
alter table t modify partition p1 indexing on;
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Though this only applies to DML on the partition you're changing the indexing properties for.
This can also invalidate cursors. Exactly which cursors depends on your version and your queries.
For example, commit in the second session, then run:
select /* c2 */* from t where c2 = 1;
select sql_id, invalidations
from v$sql
where sql_text like 'select /* c2 */* from t%';
SQL_ID INVALIDATIONS
gzy3k6kscb161 0
alter table t modify partition p1 indexing on;
select /* c2 */* from t where c2 = 1;
select sql_id, invalidations
from v$sql
where sql_text like 'select /* c2 */* from t%';
SQL_ID INVALIDATIONS
gzy3k6kscb161 1
The invalidated cursors will be re-parsed. So they need to test their application to see the impact.
But you would have to actually test and observe. Indeed!