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.

Is the remote table actually a table, or a view? How long have you left it to see if it does eventually complete? You could look at support doc ID 1313218.1 for some things to check, but if you can't see the performance views (according to earlier comment) you might need to get your DBA involved to help you. — Alex Poole
Yeah, I don't think it's possible to diagnose the issue without the views. I'll try to summon a DBA to help with this. — Mt. Schneiders

Roger Cornejo Roger Cornejo · Accepted Answer · 2017-01-06T16:19:30

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).

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

1 Answers