Create Materialized View hangs using remote table - even with 0 rows

Question

We're trying to create a simple materialized view based on a remote table, but it just hangs.

The view creation code looks similar to this:

CREATE MATERIALIZED VIEW MV_XYZ
REFRESH FORCE ON DEMAND
AS
  SELECT COLUMN1,
         COLUMN2,
         COLUMN3
    FROM TAB1@DBLINK1
   WHERE COLUMN4 = 1

Now, if we execute the SELECT statement separately, we get:

#Rows: 237
#Execution Time: 0.8 seconds

That's fairly weird already, but even if we change the SELECT statement to return 0 rows, the view still doesn't get created:

CREATE MATERIALIZED VIEW MV_XYZ
REFRESH FORCE ON DEMAND
AS
  SELECT COLUMN1,
         COLUMN2,
         COLUMN3
    FROM TAB1@DBLINK1
   WHERE COLUMN4 = 1
     AND 1=2 --included this, still hangs;

It appears that the result set is irrelevant to the problem itself.

What could be causing this?

Additional consideration: We had no issues creating materialized views based on other tables over the same dblink.

Answer 1

query V$session (and v$sqlarea) as these views will provide some clues as to what is going on; you can also check for blocking locks (many scripts available on-line).