ORA-604, ORA-00018 (urgent)

Hi there,

Oracle 8.0.5/ Apps 11.0.3, NT 4.0.

Today, users are getting following errors at random when they execute modules in the application.

ORA-604: Error occurred at recursive SQL 1 Level.
ORA-00018: Maximum number of sessions exceeded.

ORA-01000: Maximum open cursors exceeded.

I checked count(*) from v$session. It was 63 (including background processes)

Foll. are other init.ora parameters.

PROCESSES = 75
SESSION = 87
TRANSACTION = 95

OPEN_CURSORS = 300
SESSION_CAHCHED_CURSORS = 150
SESSION_MAX_OPEN_FILES = 0

It is on Production.

Pl. help...

Thanks in Adv.

do select * from v$resource_limit and paste here but you MUST have not shutdown the datatabse because the data in that v$ is from instance startup

Hi Pando,

Foll. is the output from v$resource_limit. I don't know how to indent the output in editor of this forum.

processes 49 62 75 75
sessions 59 87 87 87
lm_procs 0 0 0 0
lm_ress 0 0 0 UN
lm_locks 0 0 0 UN
enqueue_locks 129 221 1306 1306
enqueue_resourc 131 243 5000 5000
dml_locks 4 95 500 500
temp_table_locks0 5 UN UN
transactions 48 65 95 95
sort_seg_locks 0 0 UN UN
max_rbs 9 9 31 31
distr_trans 0 1 23 23
mts_max_servers 0 0 20 20
para_max_server 0 0 6 6

Thanks,

Code:

---


this is from the crm application here (production)

select * from v$resource_limit

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes                                      93            102        450        450
sessions                                        93            114        500        500
enqueue_locks                                  80            124      6030      6030
enqueue_resources                              85            142      1500      1500
_lm_procs                                        0              0          0          0
lm_ress                                          0              0          0  UNLIMITED
lm_locks                                        0              0          0  UNLIMITED
lm_cache_ress                                    0              0          0  UNLIMITED
dml_locks                                        3              36      2200      2200
temporary_table_locks                            0              13  UNLIMITED  UNLIMITED
transactions                                    3              14        550        550
sort_segment_locks                              2              12  UNLIMITED  UNLIMITED
max_rollback_segments                          11              11        111        111
distributed_transactions                        0              0        137        137
mts_max_servers                                  0              0        20        20
parallel_max_servers                            0              0          6          6

---

look your max_utilization of sessions, it reached to 87 so obviously your count(*) from v$session wasnt correct, when you run the query the number of session had already decreased

so just increase you init.ora parameter

Hi pando,

How did you paste your output to look it so nice?

In my init.ora, there is no parameter 'SESSIONS', PROCESSES is defined as 75. Should I increase this value so that as per forumla SESSIONS = (1.1*processes)+5, it will calculate SESSIONS automatically?

Thanks,

ye it will calculate auto if you increase process

I am good looking so my post looks bettah :D

Pando,

I am good looking so my post looks bettah

Pl. tell me where you have placed your photograph so that your thread first l**k at your photo and then do indenting as per your l##k.

Just kidding. Finally, tell me how did you do that?

Thanks,

\[\code\]
post here
\[\/code\]

ignore escape character \