ORA-00018: maximum number of sessions exceeded

From: Tim Onions <tim.onions@xxxxxxxxxxxxxxxxxx>
To: "Oracle-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 10 Nov 2004 12:17:44 -0000

Dear All

Win2000, Oracle8.1.7.4, init.ora proceses=450.

I'm following up a DB crash due to ORA-00018. Seems V$sessions and V$license
only report "user" sessions not "all" sessions that the process/sessions
parameters work off so even though we monitor v$sessions and never see it
above 350 (which is when Windows runs out of memory anyway) we actually bust
500 (process*1.1+5). Metalink suggest monitoring "true" session usage via:

select count(*) from x$ksuse where bitand(ksspaflg,1)!=0;

And sure enough there are times when there are >30% more sessions
"connected" than v$session would have us believe.

Digging further I find a lot of "simple" selects as being run "recursive" as
"sys" via the  x$ksuse (and hence bumping up the number of true sessions).
So my question is why?

An example query that I found by mapping sql/hash from x$ksuse to v$sql is:

SELECT job_count FROM ssjr WHERE company_id=64412

Which is run over JDBC thin by our monitoring tool via a user that does not
own the table ssjr (it is owned by another and granted to the tool with a
public synonym). For some reason the monitoring tool has set up its user
with SELECT ANY TABLE priv too.

So (eventually) the question: Why does Oracle have to run this simple query
as recursive? The user/schema columns in x$ksuse are SYS not the monitor
tool user or table owner. I suspect that select any table might be something
to do with it, or maybe the public synonym but could not find anything on
metalink to back this up. I cannot easlily remove select any table priv to
prove this as then the monitoring tool will fail although if this is the
culprit I will have it rebuilt using grants to specific objects (planned
downtime etc etc).

Many thanks in advance

Tim

--
//www.freelists.org/webpage/oracle-l

ORA-00018: maximum number of sessions exceeded

Other related posts: