hanging package compile

From: Martin <martin.j.evans_at_gmail.com>
Date: Tue, 16 Mar 2010 10:14:21 -0700 (PDT)
Message-ID: <a15a9d45-c94c-4e00-9e8f-4ffd83ad0a6d_at_k17g2000yqb.googlegroups.com>

We have a large schema and packages that compile find on Oracle 11.1. When I try the same procedure to build our schema and packages on a new Oracle 11.2 on a new box it hangs during the compilation of a package:

sqlplus -S xxx/xxx_at_a.b.c:1521/sid @xxx_body.sql

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request

shows nothing and

select * from v$sql where users_executing > 0

shows:

BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; select TIME_WAITED_MICRO from V$SYSTEM_EVENT where event = 'Shared IO Pool Memory'
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'

I'm not an expert Oracle DBA. Any ideas?

Martin Received on Tue Mar 16 2010 - 12:14:21 CDT