rutgermons
asked on
oracle, databases, 10, ora 12518 increasing cursor sessions
folks
how can i check the amount of sessions alloctated to the oracle databases,the amount in use and how do i increase them(via a script)
also what is the impact of this?
all help will do
r
how can i check the amount of sessions alloctated to the oracle databases,the amount in use and how do i increase them(via a script)
also what is the impact of this?
all help will do
r
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"is this a wise notion to increase the sessions?"
That depends. If your application needs more sessions than are currently allowed, then yes. But, pay attention to the amount of RAM in the server, because each additional session will use some additional RAM. No, it is not 5MB per session in my experience (which is mainly with Oracle on WIndows or Linux). In my experience, it is usually 1MB per session.
"do the sessions get killed after a while?"
That depends. If you have resource limits activated in your database, and if you have a limt set on "connect_time", then yes, sessions will get killed automatically when they reach that limit. Otherwise, no, they will not get closed automatically. Resource limits are not activated by default in Oracle, so unless you have activated them manually, the sessions will not get closed automatically. Or, your application could close sessions when they reach a certain age or limit, but we have no idea what kind of application you have or whether your application closes sessions automatically or not. Or, you can configure SQL*Net to watch for dropped client connections and kill the sessions in the server when this happens.
That depends. If your application needs more sessions than are currently allowed, then yes. But, pay attention to the amount of RAM in the server, because each additional session will use some additional RAM. No, it is not 5MB per session in my experience (which is mainly with Oracle on WIndows or Linux). In my experience, it is usually 1MB per session.
"do the sessions get killed after a while?"
That depends. If you have resource limits activated in your database, and if you have a limt set on "connect_time", then yes, sessions will get killed automatically when they reach that limit. Otherwise, no, they will not get closed automatically. Resource limits are not activated by default in Oracle, so unless you have activated them manually, the sessions will not get closed automatically. Or, your application could close sessions when they reach a certain age or limit, but we have no idea what kind of application you have or whether your application closes sessions automatically or not. Or, you can configure SQL*Net to watch for dropped client connections and kill the sessions in the server when this happens.
ASKER
mark
how do i check for dropped client connections?
interested,have u got scripts for this?
gr
how do i check for dropped client connections?
interested,have u got scripts for this?
gr
"how do i check for dropped client connections?"
You can't, but SQL*Net can. You have to add an entry like this to your listener.ora file, then restart the listener:
SQLNET.EXPIRE_TIME=10
I think this instructs SQL*Net to check all client connections every 10 minutes, and close any that have been dropped by the client without doing a logoff. I think this does not clear the database session immediately, but I think one of Oracle's background processes (PMON or SMON) will then notice the dropped connection and clear it.
If you want the server to close unused (or inactive) sessions faster, you will have to use resource limits and set the profile value: "idle_time" to whatever interval you feel is reasonable for your application/users. This will cause the server to close client connections after they have been inactive for whatever time period you specify, but this may make some users unhappy!
You can't, but SQL*Net can. You have to add an entry like this to your listener.ora file, then restart the listener:
SQLNET.EXPIRE_TIME=10
I think this instructs SQL*Net to check all client connections every 10 minutes, and close any that have been dropped by the client without doing a logoff. I think this does not clear the database session immediately, but I think one of Oracle's background processes (PMON or SMON) will then notice the dropped connection and clear it.
If you want the server to close unused (or inactive) sessions faster, you will have to use resource limits and set the profile value: "idle_time" to whatever interval you feel is reasonable for your application/users. This will cause the server to close client connections after they have been inactive for whatever time period you specify, but this may make some users unhappy!
Yes, Mark is correct.
Only one small remark:
SQLNET.EXPIRE_TIME=10
should be set in SQLNET.ORA file
Only one small remark:
SQLNET.EXPIRE_TIME=10
should be set in SQLNET.ORA file
Thank you!
Good luck!
Good luck!
ASKER
is this a wise notion to increase the sessions
do the sessions get killed after a while?
greets
r