Re: hanging package compile

From: Shakespeare <>
Date: Wed, 17 Mar 2010 18:29:10 +0100
Message-ID: <4ba1116c$0$22945$>

Op 17-3-2010 18:22, Martin schreef:
> On Mar 17, 1:06 pm, ddf<> wrote:
>> On Mar 17, 4:48 am, Martin<>  wrote:
>>> On Mar 16, 5:47 pm, "Gerard H. Pille"<>  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
>>>>> 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:
>>>>> 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

> I don't expect you to know what the problem is without further
> information - with, or without your crystal ball.

> As I said in my original posting I am not an Oracle expert just
> someone using it so I was unsure what information to provide but hoped
> someone else might know what would be useful to start diagnosing the
> problem.

> I cannot post all the package in question and in any case it is nearly
> 18000 lines long. I don't think it does much that complicated or
> elaborate but there is a lot of it. Here is a summary of it use:

> 102 externally accessible + 82 internal procedures
> 33 externally accessible + 23 internal functions
> some functions return SYS_REFCURSOR types
> some reference cursors returned are generated from dynamic sql
> Some fns/procs take lobs
> A few constants
> A number of simple types declared where fields are types based on
> columns in tables we created
> A number of cursors declared in pkg body where they are used more than
> once (all on our tables, with some joins and sometimes for update)

> A lot of calls to functions and procedures in other packages (all the
> other packages were compiled successfully prior to attempting to
> compile the package in question which hangs).

> It uses some global views (on our tables) compiled before the package
> in question.

> Quite a lot of views are used but they are all on tables we created. I
> cannot see any data dictionary stuff.

> It would be very difficult to describe what the package does. It
> performs no manipulation of any system tables other than what may
> happen via using Oracle supplied packages. It works on tables I
> created, mostly inserting and updating and a few deletes. All tables
> have an insert/update trigger which at a minimum sets a timestamp and
> user column to the current time and user. Some triggers also call the
> dbms_alert package and a few raise application errors on certain types
> of activity. Some of the procedures in the package are called by the
> dbms_scheduler package. A couple of procedures lock a table for the
> duration of the procedure. The package is created with definer rights.

> There is one cyclic dependency that occurs before the package in
> question (which cannot easily be avoided right now - basically a
> trigger uses a constant in a package and the package uses the table
> the trigger is on and the sql defining the table and trigger is in one
> file) but we issue DBMS_UTILITY.compile_schema after that package
> which corrects it.

> Also, I left the compile running for over a day without timeout and
> examining the database via sql developer shows all the package fns/
> procs are visible but sqlplus has not returned and an oracle instance
> is very busy.

> I've downgraded to 11.1 for now but if anyone has an ideas how to home
> in on the problem I will go back to 11.2 to investigate.
> Thanks

How exactly do you compile your package? Compile, compile body, tool? What part is hanging: compile or compile body?

Shakespeare Received on Wed Mar 17 2010 - 12:29:10 CDT

Original text of this message