Oracle-L: RE: Why compiling pkg hangs???

From: <JayMiller_at_tdwaterhouse.com>
Date: Thu, 20 Nov 2003 15:04:57 -0800
Message-ID: <F001.005D74CC.20031120150457@fatcity.com>

You can also check v$access and v$session for any active sessions accessing that package.

Jay Miller

-----Original Message-----
[mailto:Bruce.Reardon_at_comalco.riotinto.com.au] Sent: Tuesday, November 18, 2003 5:34 PM To: Multiple recipients of list ORACLE-L

This was discussed on 31-October and look in the archives for the details, but:
What did you check for locking - if dba_locks or standard utllockt.sql then it won't show.

You could use Steve Adam's script Executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm to see what packages are executing and who is executing them.

Also, can use dba_lock_internal to look at what is being blocked:

based on Oracle-L script by Diego Cutrone [mailto:diegocutrone_at_yahoo.com.ar] (Friday, 29 August 2003 7:54 AM)
COLUMN lock_id2 FORMAT A30
select to_char(SESSION_ID,'999') sid ,

    substr(LOCK_TYPE,1,30) Type, 
    substr(lock_id1,1,45) Object_Name, 
    substr(mode_held,1,4) HELD, 
    substr(mode_requested,1,4) REQ,

    lock_id2 lock_addr
FROM dba_lock_internal
WHERE
    mode_requested <> 'None'
    and mode_requested <> mode_held
;

and use inverse of this with a given object_name to find who has the internal locks.
(I know Yong will suggest this query needs a predicate in the where clause but it works for me and I haven't had time to test alterations.)

HTH,
Bruce Reardon
mailto:bruce.reardon_at_comalco.riotinto.com.au

NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed.

-----Original Message-----
Sent: Wednesday, 19 November 2003 9:15 AM To: Multiple recipients of list ORACLE-L

Hi all,

Our app is written using pl/sql's packages. When I tried to display one form and it runs forever. So I closed the form, and changed the sql in the package that generates the form, and recompiled the pkg, and the recompiling hangs. I checked the database, there is no locking there. What shall I do? (Oracle9i on Linux RedHat, 9iAS.)

Thanks.

Janet

Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Janet Linsy
INET: janetlinsy_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Reardon, Bruce (CALBBAY)
INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------