dbms_cdc_publish hang

From: tkonrath <tkonrath_at_hotmail.com>
Date: 13 Sep 2005 09:30:31 -0700
Message-ID: <1126629031.452360.259670@o13g2000cwo.googlegroups.com>

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.

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
(If I am in the wrong newsgroup, please excuse and post me the right
newsgroup, thx) Received on Tue Sep 13 2005 - 11:30:31 CDT