GV$ QUERY FAILS WITH ORA-19202 ORA-12850

Last updated on FEBRUARY 27, 2018

Applies to:

Symptoms

When attempting to SELECT XMLTYPE(
  CURSOR(
  SELECT 'Source: ENG-OSS_OSSPMP03_BlockingSessions.sql' report, welvih.*
  FROM (WITH lk
  AS ( SELECT 'Session with SID '
  || s.SID
  || ' (Instance: '
  || l.inst_id
  || ' Prog: '
  || s.PROGRAM
  || ' User: '
  || s.username
  || ' Machine: '
  || s.machine
  || ') is '
  || DECODE(l.BLOCK, 0, 'waiting for', 'blocking')
  || ' the object: '
  || object_name
  || ' (LOCK_TYPE: '
  || DECODE(l.TYPE
  ,'RT', 'Redo Log Buffer'
  ,'TD', 'Dictionary'
  ,'TM', 'DML'
  ,'TS', 'Temp Segments'
  ,'TX', 'Transaction'
  ,'UL', 'User'
  ,'RW', 'Row Wait'
  ,l.TYPE)
  || ' LOCK_MODE: '
  || DECODE(l.lmode
  ,0, 'None'
  ,1, 'Null'
  ,2, 'Row Share'
  ,3, 'Row Excl.'
  ,4, 'Share'
  ,5, 'S/Row Excl.'
  ,6, 'Exclusive'
  ,LTRIM(TO_CHAR(lmode, '990')))
  || ' LOCK_STATUS: '
  || DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global')
  || ')'
  TEXT
  ,s.SID
  FROM gv$lock l
  JOIN gv$session s
  ON (l.inst_id = s.inst_id
  AND l.SID = s.SID
  AND s.username != 'SYS')
  JOIN gv$locked_object o
  ON (o.inst_id = s.inst_id
  AND s.SID = o.session_id)
  JOIN dba_objects d ON (d.object_id = o.object_id)
  WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
  FROM gv$lock
  WHERE request > 0)
  AND L.CTIME > 60
  ORDER BY id1, id2, ctime DESC)
  SELECT -- OK Alarm
  'osspmp03' APPLICATION
  ,'BlockingSession_<*>' OBJECT
  ,'ENG-OSS' MESSAGE_GROUP
  ,0 SEVERITY
  , -- Info(4), Warning(1), Critical(2)
  'No blocking session found' TEXT
  ,'' INSTRID
  ,'' NOTIFICATION
  ,'' SN
  ,'' TYPE
  ,'' PERF_CURRENT
  ,'' PERF_WARNING
  ,'' PERF_CRITICAL
  ,'' PERF_MAX
  FROM DUAL
  WHERE NOT EXISTS (SELECT TEXT FROM lk)
  UNION ALL
  SELECT 'osspmp03' APPLICATION
  , 'BlockingSession_'
  || SID
  OBJECT
  ,'ENG-OSS' MESSAGE_GROUP
  ,CASE WHEN TEXT LIKE '%CPDADMIN%' THEN 4 ELSE 1 END SEVERITY
  , -- Info(4), Warning(1), Critical(2)
  TEXT
  ,'' INSTRID
  ,'' NOTIFICATION
  ,'' SN
  ,'' TYPE
  ,'' PERF_CURRENT
  ,'' PERF_WARNING
  ,'' PERF_CRITICAL
  ,'' PERF_MAX
  FROM lk) welvih)).getClobVal()
  viewname
  FROM DUAL;,
the following error occurs.

ERROR
-----------------------

ERROR:
ORA-19202: Error occurred in XML processing
ORA-12850: Could not allocate slaves on all specified instances: 4 needed, 1
allocated
ORA-06512: at "SYS.XMLTYPE", line 343
ORA-06512: at line 1

The inner cursor works fine - only fails with SELECT XMLTYPE

Cause