1 2 Previous Next 16 Replies Latest reply: May 29, 2012 8:17 AM by 713555 RSS

    Materialized view process running constantly /* MV_REFRESH (DEL) */


      3 days ago I created this Materialized view:

      partition by range (dato)
      partition src_mv_pre2005 values less than (to_date('2005-01-01','yyyy-mm-dd'))
      , partition src_mv_2005 values less than (to_date('2006-01-01','yyyy-mm-dd'))
      , partition src_mv_2006 values less than (to_date('2007-01-01','yyyy-mm-dd'))
      , partition src_mv_2007 values less than (to_date('2008-01-01','yyyy-mm-dd'))
      , partition src_mv_2008 values less than (to_date('2009-01-01','yyyy-mm-dd'))
      , partition src_mv_2009 values less than (to_date('2010-01-01','yyyy-mm-dd'))
      , partition src_mv_2010 values less than (to_date('2011-01-01','yyyy-mm-dd'))
      , partition src_mv_2011 values less than (to_date('2012-01-01','yyyy-mm-dd'))
      , partition src_mv_2012 values less than (to_date('2013-01-01','yyyy-mm-dd'))
      , partition src_mv_2013 values less than (to_date('2014-01-01','yyyy-mm-dd'))
      REFRESH on demand
      start with sysdate next trunc(sysdate)+1+3.75/24
      with primary key
      SELECT srcid
      , a
      , dato
      , group
      , COUNT(*) tx
      , SUM(amount) sumtx
      FROM b btx
      JOIN groups g USING(gid)
      join c on (srcid=cid and ctype='W')
      GROUP BY srcid
      , a
      , dato
      , group;

      Table b has more than 330 millions rows and table c has about 3 millions rows. However the creation of the MV itself tok about an hour.

      After the MV was created I tried to create an index on dato but I got:
      ORA-00054: resource busy and acquire with NOWAIT specified

      When I checked the active sessions, I see this:

      /* MV_REFRESH (DEL) */ delete from "X"." SRC_MV"

      So, now I am not able to create any index, drop the MV or even kill the session that is running the /* MV_REFRESH (DEL) */.

      Why is this DELETE statement running, when I said REFRESH on demand? Is this running since the MV was created?
      On the other hand, the database is shutdown/startup everyday at 8pm/10pm, and there has not been any problem with this shutdown/startup, so I can't understand why this Delete statement is still running.

      The MV has now a compilation error status, why is not possible to drop it then?

      Can anybody help me?

      Thanks in advance,
        1 2 Previous Next