GV$ QUERY FAILS WITH ORA-19202 ORA-12850
Last updated on FEBRUARY 27, 2018
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation in this document applies to any platform.
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
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms