When running a Java program which uses XSQL to insert/delete/update to Oracle, it does not return and Oracle sessions holds a transaction lock (TADDR in V$SESSION), and the corresponding SQL could be a SQL selecting a sequence number(this code is in the before trigger for the affected table) or an INSERT statement. There is no errors in Oracle trace/alert log, nor in listener log.
First of all, our developer assured me that the transaction will either commit or rollback in the Java client program, so I assume it won't fall through some where in the code that has left the transaction pending forever. This program has been tested/running ok for years without problems until recently.
When I query V$SESSION_WAIT, it only has SQL*Net Meesage from client and the wait time keeps increasing. There are no rows in DBA_WAITERS, DBA_BLOCKERS. There are no resources used up such as files can't grow, etc.
There is no CPU time consumed for the session after the initial spike and Oracle's own processes are not doing anything either.
The Java program log shows the rows being passed to XSQL but never get any results back.
Could somebody give me suggestions on how to troubleshoot this kind of strange problem as far as being a DBA is concerned?