ORA-604, ORA-00018 (urgent)

[samdba]

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.

[pando]

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

[samdba]

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,

[pando]

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

[samdba]

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,

[pando]

ye it will calculate auto if you increase process

I am good looking so my post looks bettah

[samdba]

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,

[pando]

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

ignore escape character \