Re: hanging package compile
Date: Wed, 17 Mar 2010 10:22:46 -0700 (PDT)
On Mar 17, 1:06 pm, ddf <orat..._at_msn.com> wrote:
> 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
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 Received on Wed Mar 17 2010 - 12:22:46 CDT