This discussion is archived
3 Replies Latest reply: Jul 24, 2013 8:45 AM by Pollocks01 RSS

Library Cache Lock when Re-Registering an XSD

Pollocks01 Newbie
Currently Being Moderated
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.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:

 

  1. 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).
  2. 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?

  • 1. Re: Library Cache Lock when Re-Registering an XSD
    Pollocks01 Newbie
    Currently Being Moderated

    I think that dbms_shared_pool.purge should work but it errors when I try to run it for a given XML Schema. The name I pass it is the INT_OBJNAME from user_xml_schemas:

     

    SQL> begin
      2   dbms_shared_pool.purge('XDB.XD4jxhTfWIJIjgQwEAAH92jA==');
      3  end;
      4  /
    begin
    *
    ERROR at line 1:
    ORA-00911: invalid character
    ORA-06512: at "SYS.DBMS_UTILITY", line 156
    ORA-06512: at "SYS.DBMS_SHARED_POOL", line 72
    ORA-06512: at "SYS.DBMS_SHARED_POOL", line 115
    ORA-06512: at line 2

     

    I've tried this as SYS and also as the schema object owner. I've tried it with and without the XDB. prefix.

     

    ideas?

  • 2. Re: Library Cache Lock when Re-Registering an XSD
    Pollocks01 Newbie
    Currently Being Moderated

    Hmm - I think I resolved this myself by considering something that Odie_63 or Marco Gralike had mentioned previously........

     

    I modified my schema validate procedure to free all resources and I no longer get the library cache lock:

     

    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;          
          
          dbms_session.modify_package_state(dbms_session.free_all_resources);
    end sp_schema_validate_xml;
    /   
  • 3. Re: Library Cache Lock when Re-Registering an XSD
    Pollocks01 Newbie
    Currently Being Moderated

    ...and for completeness, dbms_session.modify_package_state(DBMS_SESSION.REINITIALIZE); also seems to work. THis is meant to be less of a performance hit than free_all_resources.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points