 dbms_cdc publish PROBLEM !!! an Alter package compile hang (merged threads) [message #138006] |
Mon, 19 September 2005 06:20  |
tkonrath
Messages: 2
Registered: September 2005
Location: Austria
|
Junior Member |
|
|
Hi
We are trying to solve a strange problem with our web-application
(Web-Server: Tomcat/Apache, Database: Oracle 9.2.0.6).
The Problem is, that at least every day, a session from the database stops working, but the session is still available and the lock is also still there (lock-Information: Object Name: ".", Lock Type: Cursor Binds, Lock Mode: Exclusive Mode, Request Type: None). The current statement of the session is:
/* Formatted on 2005/09/13 18:14 (Formatter Plus v4.7.0) */
BEGIN
/* NOP UNLESS A TABLE OBJECT */
IF dictionary_obj_type = 'TABLE' AND SYS.dbms_cdc_publish.active > 0
THEN
SYS.dbms_cdc_publish.change_table_trigger
(dictionary_obj_owner,
dictionary_obj_name,
sysevent
);
END IF;
END;
We have no idea, why Oracle hangs here. The variable SYS.dbms_cdc_publish.active has the value 0. We have never worked with dbms_cdc_publish.
As far as we encountered it could be possible, that SNAPSHOTS or MARTERIALIZED VIEW can cause problems with dbms_cdc_publish, but we are not sure. It seems, that also the refresh of the MATERIALIZED VIEWs hang (but without an deadlock, they just stop working) but we have no clue why or how we can solve this problem.
Or could it be a problem, because we use the WWW-package from Oracle? We use this vor several things in our applikation, so may be there is a problem ...
The next strange thing is, that other sessions (for example compile a package or alter a table from another user) also hang and create the same look with the same last statement. But NO DEADLOCK or blocking lock occurred.
If you have any ideas, how we can solve the problem, please help!
Thomas
-----------------------------------------
unycom IT Services GmbH
Solutions
|
|
|
|
 Alter package compile hang [message #138153 is a reply to message #138006] |
Tue, 20 September 2005 08:11  |
tkonrath
Messages: 2
Registered: September 2005
Location: Austria
|
Junior Member |
|
|
We use a lot of packages in the database. There are a lot of dependency among packages in our database. Sometimes, when developers push a new version of a core package (changed its spec), it will invalid some other packages. We constantly have customers activities that need call packages. And sometimes, oracle was unable to recompile invalid package in time (but just sometimes!). Then the database seems to be in a deadlock, but in our SQL Client Toad, no deadlook is shown. The session, which recompiles a package just hangs with the folowing information:
Object Name: ".", Lock Type: Cursor Binds, Lock Mode: Exclusive Mode, Request Type: None). The current
statement of the session is:
/* Formatted on 2005/09/13 18:14 (Formatter Plus v4.7.0) */
BEGIN
/* NOP UNLESS A TABLE OBJECT */
IF dictionary_obj_type = 'TABLE' AND SYS.dbms_cdc_publish.active > 0
THEN
SYS.dbms_cdc_publish.change_table_trigger (dictionary_obj_owner, dictionary_obj_name, sysevent );
END IF;
END;
It just stops working.
After doing research with Google we thing it could be a problem because of libray cache locks but we are not shure.
When the next user wants to access a package and recompiles it, this session also stops working with the same statement, but does not set a look.
It seems that one session has a library cache lock and wait for library cache pin, while the other sessions hold library cache pin waiting for library cache lock, and the session tries to recompile packages will never finish its job.
But why dosn't fisnish the first session his recompile job?
Thanks for any help!
Thomas
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
