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@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

On Mar 16, 5:14*pm, Martin <martin.j.ev... (AT) gmail (DOT) com> wrote:
Some additional info:

select * from v$session_wait where sid = 33

33 228 db file sequential read file# 4 0000000000000004 block# 978
00000000000003D2 blocks 1 0000000000000001 1740759767 8 User I/O 1
2399 WAITED KNOWN TIME 5170 (null) 2399084207

Martin

Martin wrote:
The package you are trying to compile is in use?

On Mar 16, 5:47*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
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

On Mar 17, 4:48*am, Martin <martin.j.ev... (AT) gmail (DOT) com> wrote:
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

On Mar 17, 1:06*pm, ddf <orat... (AT) msn (DOT) com> wrote:
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

Op 17-3-2010 18:22, Martin schreef:
How exactly do you compile your package? Compile, compile body, tool?
What part is hanging: compile or compile body?

Shakespeare

Martin wrote:
Split it up.

On Mar 17, 5:29*pm, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
compile package spec with:

sqlplus -S user/pass (AT) server (DOT) aaa.local:1522/test @pkg_spec.sql

sqlplus -S user/pass (AT) server (DOT) aaa.local:1522/test @pkg_body.sql

The body hangs.

Martin

Op 17-3-2010 18:36, Martin schreef:
In stead of running the script again, could you try 'alter package XXX
compile', 'alter package XXX compile body'?

Shakespeare