Re: sqlloader direct path causes library cache lock?

From: Sidney Chen <huanshengchen_at_gmail.com>
Date: Tue, 25 Jun 2013 17:28:39 +0800
Message-ID: <CAM_ddu_uMHfu5StK7=n7X9vzPiYJhkGh9cUtnC4cQYhrqfxh6A_at_mail.gmail.com>

Normally, I use the query to determine the holder on which object, Not sure if the direct path load will hold the table or index in exclusive mode.

set line 180
col object_name for a30
col username for a20
select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3,
'CLUSTER',

                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21,


'LOB',

                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA
RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, 'MATERIALIZED VIEW',
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      62, 'EVALUATION CONTEXT',
                     'UNDEFINED') object_type,
       lob.KGLNAOBJ object_name,
       pn.KGLPNMOD lock_mode_held,
       pn.KGLPNREQ lock_mode_requested,
       ses.sid,
       ses.serial#,
       ses.username,

   vsw.event
  FROM

       x$kglpn pn,
       v$session ses,
       x$kglob lob,
       v$session_wait vsw

  WHERE
   pn.KGLPNUSE = ses.saddr and
   pn.KGLPNHDL = lob.KGLHDADR
   and lob.kglhdadr = vsw.p1raw
   and vsw.event like 'library cache%'
order by object_name, lock_mode_held desc /

Regards
Sidney Chen

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 25 2013 - 11:28:39 CEST