Oracle SESSIONS Parameter value needs to be increased (Cause : ORA-00018 )

[Former Member]

Hi,

Noticed the following error in SM21 and also found out that that many background jobs got canceled.

Database error 18 at COM
> ORA-00018: maximum number of sessions exceeded.
Function COMMIT on connection R/3 failed.
Error at DB Commit, return code 001024.
Database error 18 at COM
Perform rollback

While looking for a solution for the issue, understood that we need to change the SESSIONS parameter to a

higher value.

(Solution Note : 398927 - ORA errors because dimensions of parameters are too small)

(As per the above note, we are trying to sort out the issue, which also says a lot of things in which one could be the reason for the error. But we finally got to know that SESSIONS parameter is the problem and should be made to a higher value.)

Currently our BW Production Server, the settings are as follows:

■　PROCESSES = 224

■　SESSIONS = 251

(For your information:

PROCESSES Formula u21D2 ABAP work processes * 2 + #J2EE server processes *<max-connections> +

PARALLEL_MAX_SERVERS + 40

SESSIONS Formula u21D2 2 * PROCESSES

As per the above thing, SESSIONS should be 224 * 2 which should be 448.

Hence we are planning to change the same to 448.

Question:

-

---

1. What other things(OS Side, DB side, + Oracle Parameters etc) should be considered

in order to change the SESSIONS to 448.

2. Is there any problems anticipated by changing the SESSIONS parameter.

Thanks & Best Regards

Raghunahth L

[Former Member · Accepted solution]

Hello Raghunahth,

1. the Oracle documentation speaks about two dependencies for the SESSIONS parameter: ENQUEUE_RESOURCES and TRANSACTIONS:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams229.htm#REFRN10197

However I never changed these other parameters, I don't think they are relevant for SAP systems.

2. no problems anticipated, you simply have to raise the sessions parameter if you encounter ORA-00018 on SAP systems

Regards,

Mark

[former_member213250 · Accepted solution]

Hello Raghunath

Mentioned error : ORA-00018: maximum number of sessions exceeded, this error occurs cause of

high number of parllel processing. On your system the set SESSION limit might be reached hence the

the error. I would suggest to increase the SESSIONS parameter to sufficient enough in parameter file,

and restart oracle

Hope it helps

BR

Venkat

[Former Member]

Processes: 224, Sessions = 448, I think it is still less than recommended, because programs like tp, R3Trans make direct calls to Database, keeping J2EE in mind I would set Processes = 350, Sessions =700 so that you are allowing more room to connect to Database.

Thanks

SM