Oracle Database 11g Enterprise Edition Release 184.108.40.206.0 - 64bit Production PL/SQL Release 220.127.116.11.0 - Production CORE 18.104.22.168.0 Production TNS for Linux: Version 22.214.171.124.0 - Production NLSRTL Version 126.96.36.199.0 - Production
If I "use" an xsd in one session, then if I try to re-register the xsd in another session, the re-register session is indefinitely waiting on a library cache lock:
"use" the xsd is one session: create or replace procedure sp_schema_validate_xml(pi_xml IN XMLType, pi_xml_schema IN VARCHAR2) is v_count number; v_err_msg varchar2(4000); v_xml xmltype; begin --do level 1 schema validation if pi_xml.isSchemaValid(pi_xml_schema) = 0 then --level 1 vldtn failed. do level 2 begin --do level 2 vldtn v_xml := pi_xml.createSchemaBasedXML(pi_xml_schema); v_xml.schemaValidate(); end; end if; end sp_schema_validate_xml; / create or replace directory pipedoc as '/home/oracle/testfiles/HUD/ERS_HUD_VM01/PIPE'; begin sp_schema_validate_xml(pi_xml => xmltype(bfilename('PIPEDOC', 'DecryptedFile-36805759.xml'),0), pi_xml_schema => 'PIPE/Power/V4.0/PIPEDocument.xsd' ); end; / Now try to re-register the XSD in another session: create or replace directory XSD as '/home/oracle/testfiles/HUD/ERS_HUD_VM01/PIPE/XSDs/Power'; create or replace function f_exists_xsd(pi_list_item IN varchar2) return boolean is v_count pls_integer; begin select count(1) into v_count from user_xml_schemas where schema_url = pi_list_item; if v_count = 0 then return false; else return true; end if; end f_exists_xsd; / declare v_xsd varchar2(100) := 'PIPE/Power/V4.0/PIPEDocument.xsd'; begin if f_exists_xsd(v_xsd) = true then dbms_xmlschema.deleteschema(v_xsd, dbms_xmlschema.DELETE_CASCADE_FORCE); end if; dbms_xmlschema.registerSchema(schemaURL => v_xsd, schemaDoc => xmltype(bfilename('XSD', 'PIPEDocument.xsd'), 0), local => TRUE, genTypes => FALSE, genbean => FALSE, genTables => FALSE, force => FALSE, owner => user); end; / The session which is trying to re-register the XSD will wait indefinitely with a "library cache lock", regardless of whether the other session issues a commit or not. The lock only gets released when the other session disconnects. I consider this to be an Oracle product defect but of course we'll have to workaround this because Oracle Support hasn't been forthcoming with help recently.
I found a query previously which successfully identified the blocking session so that we could terminate the process:
select distinct ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,KSUSEMNM module, ob.kglnaown obj_owner, ob.kglnaobj obj_name ,lk.kgllkcnt lck_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req , w.state, w.event, w.wait_Time, w.seconds_in_Wait from x$kgllk lk, x$kglob ob,x$ksuse ses , v$session_wait w where lk.kgllkhdl in (select kgllkhdl from x$kgllk where kgllkreq >0 ) and ob.kglhdadr = lk.kgllkhdl and lk.kgllkuse = ses.addr and w.sid = ses.indx order by seconds_in_wait desc
My issue is two-fold:
- The above query only works on instances which got upgraded from 10gR2 and not on those which were built fresh on 11Gr2 as seemingly the x$kgllk view and related synonyms are dropped in 11G (See Oracle support Doc ID 878623.1).
- Even if I can find alternate views to use, I don't like the thought of my deployment/upgrade script having to "blindly" kill a session in order to succeed in re-registering the XSD.
How can I go about properly locking and freeing the XSD so as to avoid the library cache lock? Alternatively, is there a way to flush the library cache?