Re: hanging package compile
From: ddf <oratune_at_msn.com>
Date: Wed, 17 Mar 2010 06:06:08 -0700 (PDT)
Message-ID: <5453e004-0852-415c-b7e2-2b3c0635e3f8_at_m27g2000prl.googlegroups.com>
On Mar 17, 4:48 am, Martin <martin.j.ev..._at_gmail.com> wrote:
> On Mar 16, 5:47 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
>
>
>
>
>
> > Martin wrote:
> > > 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/..._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
>
> > The package you are trying to compile is in use?
>
> I don't see how. I have stopped and started the database and there is
> only me using the database and I'm not doing anything. Also, the
> package did not exist in the database before I started compiling it.
>
> Martin- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 17 Mar 2010 06:06:08 -0700 (PDT)
Message-ID: <5453e004-0852-415c-b7e2-2b3c0635e3f8_at_m27g2000prl.googlegroups.com>
On Mar 17, 4:48 am, Martin <martin.j.ev..._at_gmail.com> wrote:
> On Mar 16, 5:47 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
>
>
>
>
>
> > Martin wrote:
> > > 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/..._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
>
> > The package you are trying to compile is in use?
>
> I don't see how. I have stopped and started the database and there is
> only me using the database and I'm not doing anything. Also, the
> package did not exist in the database before I started compiling it.
>
> Martin- Hide quoted text -
>
> - Show quoted text -
No code posted so we can't see what Oracle is trying to access and yet you expect us to know why your database is 'throwing fits' with this package body ... the crystal balls are out of service, the Ouija boards are of little use and parlor tricks won't answer that question. Post more information if you really want an answer, and if you can't post the actual code you could provide SOME information on what this package does and which tables/views it accesses (especially data dictionary views) as the optimizer has changed between 11.1 and 11.2 and what was once valid code may not be any more.
David Fitzjarrell Received on Wed Mar 17 2010 - 08:06:08 CDT