Fix : ORA-00910: specified length too long for its data type in materialized views ( mviews)

~ ORA-00910: specified length too long for its data-type - a solution
~ Issues with creating Materialized views when remote table contains XML type column.
~ Error : ORA-00910: specified length too long for its data-type

-- Solution

Please use following sample method while creating Mviews for remote tables containing a XML type column.

1) Use xmltype.getclobval() for XML  type column.
2) Don't forget to add exact column name again as alias after convertion.
3) Recheck all the columns are placed in DDL or not.
4) Use complete refresh to add fresh rows in mviews.

Example:

-- DDL of sample Mview while remote table contains XML type data.

create materialized view owner.remote_table
refresh complete on demand as
select methodid, worksheetcode, xmltype.getclobval(worksheet) worksheets,status  from owner.remote_table@dblink_name;

Useful queries for mview refresh/ Hang sessions :

-- All mview refresh method and time analysis
select MVIEW_NAME,LAST_REFRESH_DATE,REFRESH_METHOD,SUMMARY,FULLREFRESHTIM,INCREFRESHTIM
from ALL_MVIEW_ANALYSIS
where owner='&owner';

-- Mview status during refresh
select CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR "MVIEW BEING REFRESHED",
       decode(REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN') REFTYPE,
       decode(GROUPSTATE_KNSTMVR,
              1,
              'SETUP',
              2,
              'INSTANTIATE',
              3,
              'WRAPUP',
              'UNKNOWN') STATE,
       TOTAL_INSERTS_KNSTMVR INSERTS,
       TOTAL_UPDATES_KNSTMVR UPDATES,
       TOTAL_DELETES_KNSTMVR DELETES
  from X$KNSTMVR X
 WHERE type_knst = 6
   and exists (select 1
          from v$session s
         where s.sid = x.sid_knst
           and s.serial# = x.serial_knst);

-- Identify the Hung Materialized View to kill its session. 
select VS.INST_ID,VL.SID||','||VS.SERIAL#,VS.USERNAME,ao.object_name,
'alter system kill session '''||vl.SID||','||vs.SERIAL#|| ',@'||vs.inst_id ||''' IMMEDIATE;'
  from GV$LOCK VL, Gv$session VS, all_objects ao
 where vl.type = 'JI' and vl.Lmode = 6 and VS.SID=VL.SID
 and VS.INST_ID=VL.inst_id and vl.ID1=ao.object_id;

-- get the list of views refreshing right now.
SELECT CURRMVOWNER, CURRMVNAME FROM V$MVREFRESH


Thanks ... Cheers !!!