Can't create index, LOCK on materialized view

Question

I created a materialized view in Oracle. I used logs to enable/use fast refresh. Main table contains ~12 billion entries, joining table ~8 million. Materialized view contains 366 million entries. After the creation of the table which took 4h, we can't create a index.

create index Idx_TABLE_USERID
on SCHEMANAME.TABLE(USERID);

We get the error:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Why there is a lock on the table? Does he fill the logs?

Chris Saxon Chris Saxon · Accepted Answer · 2019-04-17T13:38:37

By default create index is a blocking DDL statement. Which means it can't start if there are outstanding transactions against the base table.

You can verify this by running:

create table t (
  c1 int
);

insert into t values ( 1 );

And then in a second session doing:

create index i on t ( c1 );

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Luckily create index has an online option. Specify this and the statement will become non-blocking. So if the in the second session you run:

create index i on t ( c1 ) 
  online;

it'll sit there waiting for you to commit/rollback the insert from session one. But as this is now non-blocking, other sessions can still insert/update/delete rows in this table.

It's not clear whether you're trying to create the index on one of the base tables or the materialized view. But in either case you're getting the error because another transaction holds the lock you need to create the index.

Can't create index, LOCK on materialized view

1 Answers