Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Migration and Modernization
E-Business Suite
PeopleSoft Enterprise
JD Edwards World
JD Edwards EnterpriseOne
User Productivity Kit Pro (UPK) and Tutor
Governance, Risk & Compliance (GRC)
Master Data Management (MDM)
Oracle CRM On Demand
On Demand: SaaS and Managed Applications
AutoVue Enterprise Visualization
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums



General Questions

sqlplus session terminates abnormally

Hi, I wonder if anyone could help me please? We have a problem where users of a Forms Application on OAS experience an abnormal session termination after a period of inactivity. The idle period seems to vary from about 15mins to half an hour and the resulting error messages are different, depending on what time you requery. Below are some of the error messages.  FRM-40733 - PL/SQL Built-in DBMS_ERROR_CODE failed FRM-92102 - A network error has occured...... FRM-40655:SQL error forced rollback clear form and re-enter transaction  When the Forms session has abnormally terminated, its backend database session becomes inactive and clocks up idle time but does not actually disconnect.  Furthermore, a sqlplus session to the backend database from the appserver also times out with end of line on communication channel, while a similar session (or a TOAD session) from a client PC does not.  We have logged a call with oracle but things are not going as quickly as we need it to be.  Does anyone have an idea of what possible things I could look at?   thanks

Hi,  it looks tome like some network settings could be the problem, maybe a firewall, which closes netwerk session after a period of being idle. I don't think it is the database because the client keeps a connection (TOAD), but the appserver not. So I would contact the netwerk boys and ask them if there is a different between the two connections. But it is a guess.  Herald ten Dam

I have a similar situation occur today - the user ran a report & got an error on her machine that the app could not connect via the ODBC connection.  I have dug out the ASH report for her session, and also have looked at the v$active_session_history table, to see if there were any clues about the reason for the termination - but cannot see anything obvious in terms of an 'abnormal connection' flag or such like.  All thoughts welcome!

Sorry - should add that it ran for about 2 hours fine. I checked the user profile, and their are no limits on her connection time / CPU usage etc.

Thank you both for your comments, much appreciated. I think I would agree with you that it is probably a network setting, that is what it looks like to me. I will pursue that and let you know.  In the mean time, if there are any other thougts I will appreciate them.  Furthermore please note that I had also checked the users' profile and all resources are unlimited.  thanks for your help.

I am off down to check my user's SQLNET.LOG file (C:\oracle\product\10.2.0\client_1\BIN\SQLNET.LOG) on their client machine, to see if it offers any clues...

Yes, I did this too and copied the sqlnet.ora from my pc to the app server but it appeared to help at first but didn't really as the sesssion got chopped.  thanks

I think you have misunderstood (although maybe I have!) - the SQLNET.LOG file shows connection errors from the client side (example below). I think you meant the SQLNET.ORA  Example of SQLNET.LOG:   *********************************************************************** Fatal NI connect error 12170.    VERSION INFORMATION: 	TNS for 32-bit Windows: Version - Production 	Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version - Production   Time: 26-MAY-2010 14:57:53   Tracing not turned on.   Tns error struct:     ns main err code: 12535     TNS-12535: TNS:operation timed out     ns secondary err code: 12560     nt main err code: 505     TNS-00505: Operation timed out     nt secondary err code: 60     nt OS err code: 0   Client address: <unknown>

ok thanks, I'll have a look at the sqlnet.log.  cheers

Did you get any clue as I get same error for one user only

Related Links

impdp TABLE_EXISTS_ACTION APPEND fail with unique constraint error
Table not visible in the database IDE, but exists when checked from DOS prm
Getting error by using expdp with table partitions
Oracle Database Sessions
A fundamental question on .profile file and ORACLE env variables
10G control file loss
Rman is running a long time
please give command for table import
Not able to open EDITOR
Dynamic Index Disable
Error while rebuilding index
How to Remove Foreign Key Constraint
Migrating the DB from Linux to Solaris