Home » RDBMS Server » Networking and Gateways » ORA-03114 and ORA-03135 (Oracle 10.2.0.4, windows)
ORA-03114 and ORA-03135 [message #559678] Thu, 05 July 2012 08:55 Go to next message
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 Go to previous message
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.
Previous Topic: Tnsping Error
Next Topic: database link from 11g to 10g
Goto Forum:
  


Current Time: Thu Mar 28 08:15:37 CDT 2024