?
Solved

oracle, databases, 10, ora 12518 increasing cursor sessions

Posted on 2008-10-20
7
Medium Priority
?
659 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:rutgermons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 2000 total points
ID: 22756834
select count(*) from v$sessions;

will say you the number of the current sessions.

SQL> select name,value from v$parameter where name='sessions';

NAME                                                                             VALUE
-------------------------------------------------------------------------------- -------------
sessions                                                                         170

Increase the value:

alter system set sessions=400 scope=both;

5MB RAM for every opened session - this is more important.

0
 

Author Comment

by:rutgermons
ID: 22757644
thanks schwertner

is this a wise notion to increase the sessions

do the sessions get killed after a while?

greets

r
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 22761770
"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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:rutgermons
ID: 22770524
mark
how do i check for dropped client connections?

interested,have u got scripts for this?


gr
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 22770678
"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!
0
 
LVL 48

Expert Comment

by:schwertner
ID: 22776799
Yes, Mark is correct.
Only one small remark:

SQLNET.EXPIRE_TIME=10

should be set in SQLNET.ORA file
0
 
LVL 48

Expert Comment

by:schwertner
ID: 22966949
Thank you!
Good luck!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question