ORA-03114 and ORA-03135 [message #559678] |
Thu, 05 July 2012 08:55 |
sathik
Messages: 196 Registered: August 2009 Location: INDIA
|
Senior Member |
|
|
Hi,
When I run the below query, the session got the below errors exactly after 1 hour. I checked profile, resource limit, and sqlnet.expire_time. The SQLNET.EXPIRE_TIME value is 10. Except expire time there is time limit in profile and resource. I dont know how this session is losting connection exactly after 1 hour. What could be the issue here and what else need to check it?
14:34:09 SQL> << DELETE query >>
ERROR:
ORA-03114: not connected to ORACLE
DELETE FROM amsoftinstall
*
ERROR at line 1:
ORA-03135: connection lost contact
SQL> select profile from dba_users where username='ASSETCENTER';
PROFILE
------------------------------
DEFAULT
SQL> select * from dba_profiles where profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
SQL> sho user
USER is "ASSETCENTER"
SQL> show parameter resource
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
resource_manager_plan string
14:37:51 SQL> select * from RESOURCE_COST;
RESOURCE_NAME UNIT_COST
-------------------------------- ----------
CPU_PER_SESSION 0
LOGICAL_READS_PER_SESSION 0
CONNECT_TIME 0
PRIVATE_SGA 0
SQL> select * from V$RESOURCE_LIMIT;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- ---------------------------------------- -------------------------
-------
processes 37 49 150 150
sessions 44 56 170 170
enqueue_locks 28 59 2492 2492
enqueue_resources 29 74 968 UNLIMITED
ges_procs 38 49 320 320
ges_ress 0 0 6042 UNLIMITED
ges_locks 0 0 7985 UNLIMITED
ges_cache_ress 5618 8665 0 UNLIMITED
ges_reg_msgs 85 41057 1050 UNLIMITED
ges_big_msgs 31 545 1050 UNLIMITED
ges_rsv_msgs 0 0 303 303
gcs_resources 119582 165469 119582 119582
gcs_shadows 119582 178769 119582 119582
dml_locks 0 83 748 UNLIMITED
temporary_table_locks 0 3 UNLIMITED UNLIMITED
transactions 0 10 187 UNLIMITED
branches 0 0 187 UNLIMITED
cmtcallbk 0 2 187 UNLIMITED
sort_segment_locks 0 6 UNLIMITED UNLIMITED
max_rollback_segments 11 11 187 65535
max_shared_servers 1 1 UNLIMITED UNLIMITED
parallel_max_servers 0 9 135 3600
22 rows selected.
SQL> select * from USER_RESOURCE_LIMITS;
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
|
|
|
Re: ORA-03114 and ORA-03135 [message #559679 is a reply to message #559678] |
Thu, 05 July 2012 08:58 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
my first guess would be Firewall between client & DB Server
This can be tested independent of Oracle.
telnet from client to DB Server & establish OS connection & process & then wait 65 minutes.
If this session gets disconnected, then you have confirmed problem is external to Oracle.
|
|
|