Home » RDBMS Server » Server Administration » Maximum session exceeded (Oracle Database 10.1.0.2.0 - 64bi,Hpux)
Maximum session exceeded [message #406249] Wed, 03 June 2009 01:01 Go to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
Hi all,From past three days one of the user is getting an error 00018 maximum session exceeded,but querying 'select * from V$resource_limit;' result as shown below

resource_name current_util max_utilin initial_allocation limit
processes 67 85 500 500
session 263 300 555 555

The profile alloted to the user is default with unlimited session.Please provide the solution.Thnx in advance.
Re: Maximum session exceeded [message #406277 is a reply to message #406249] Wed, 03 June 2009 02:03 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
$ oerr ora 00018
00018, 00000, "maximum number of sessions exceeded"
// *Cause:  All session state objects are in use.
// *Action: Increase the value of the SESSIONS initialization parameter.
Re: Maximum session exceeded [message #406278 is a reply to message #406277] Wed, 03 June 2009 02:10 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
but v$querying resource_limit it shows following result
resource_name current_util max_utilin initial_allocation limit
processes 67 85 500 500
session 263 300 555 555
here current_utilization is 263 but i have alloted 555 then why so it is happening like that.
Re: Maximum session exceeded [message #406280 is a reply to message #406278] Wed, 03 June 2009 02:12 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
what does "show parameter sessions" show?

[Updated on: Wed, 03 June 2009 02:14]

Report message to a moderator

Re: Maximum session exceeded [message #406282 is a reply to message #406280] Wed, 03 June 2009 02:22 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
show parameter session
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
session_cached_cursors integer 0
session_max_open_files integer 10
sessions integer 555
shared_server_sessions integer 250

as u can see session are 555
Re: Maximum session exceeded [message #406283 is a reply to message #406282] Wed, 03 June 2009 02:40 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What does select count(*) from v$session give you?

And format the output with code tags (the left {} button in the Formatting tools), so that it is readable.
Re: Maximum session exceeded [message #406293 is a reply to message #406283] Wed, 03 June 2009 03:33 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
>> select count(*) from v$session

241
Re: Maximum session exceeded [message #406300 is a reply to message #406293] Wed, 03 June 2009 03:53 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hmm.. According to this the limit could also be shared_server_sessions, if you use shared connections.
Re: Maximum session exceeded [message #406307 is a reply to message #406300] Wed, 03 June 2009 04:17 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
for shared server here is the result by using the query
select * from v$resource_limit
resource_name :- max_shared_servers
current_utilization :- 10
max_utilization :- 20
initial_allocation :-50
limit :-20



Re: Maximum session exceeded [message #406309 is a reply to message #406307] Wed, 03 June 2009 04:21 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So increase shared_server_sessions, and see if it works then.
Re: Maximum session exceeded [message #406313 is a reply to message #406309] Wed, 03 June 2009 04:42 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
I don't think so,but as our database is running on shared server mode it may happen that UGA memmory is not able to increase as shared pool size is set up to a limit.So by increasing the sga_max_size might help.what u say sir???
Re: Maximum session exceeded [message #406332 is a reply to message #406313] Wed, 03 June 2009 06:16 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One way to find out would be to try it.
Re: Maximum session exceeded [message #406367 is a reply to message #406249] Wed, 03 June 2009 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Does the application make many, many new connection requests?
Please post last 20 - 30 lines of listener.ora

Oracle 10 introduced new behaviour to listener.
You could be getting bit by this "improvement".
Re: Maximum session exceeded [message #406407 is a reply to message #406367] Wed, 03 June 2009 22:43 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
>> listener.ora contents
LISTENER1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ppms1)(PORT = 1522))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.1.0)
(PROGRAM = extproc)
)
)

SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ppms)
(ORACLE_HOME = /u01/app/oracle/product/10.1.0)
(SID_NAME = ppms)
)
)

TRACE_LEVEL_LISTENER1 = ADMIN

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ppms1)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)

TRACE_LEVEL_LISTENER = ADMIN
Re: Maximum session exceeded [message #406410 is a reply to message #406249] Wed, 03 June 2009 23:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Does the application make many, many new connection requests?
Please post last 20 - 30 lines of listener.log
Re: Maximum session exceeded [message #406412 is a reply to message #406410] Wed, 03 June 2009 23:19 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
yes it makes a lot new request.
here is the last few lines of listner.trc


[02-JUN-2009 07:37:09:508] nsevfnt: cxd: 0x1bd070 stage 208: NS events set:
[02-JUN-2009 07:37:09:508] ntusread: entry
[02-JUN-2009 07:37:09:508] ntusread: Read bytes 4
[02-JUN-2009 07:37:09:508] ntusread: exit
[02-JUN-2009 07:37:09:508] nsbequeath: NSE=12586
[02-JUN-2009 07:37:09:508] nsbequeath: error reading REDIR/NSE msg
[02-JUN-2009 07:37:09:508] nserror: nsres: id=32, op=72, ns=12586, ns2=0; nt[0]=0, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[02-JUN-2009 07:37:09:508] nsdo: cid=32, opcode=67, *bl=0, *what=13, uflgs=0x0, cflgs=0x3
[02-JUN-2009 07:37:09:508] nsdo: rank=64, nsctxrnk=0
[02-JUN-2009 07:37:09:508] nsdo: nsctx: state=2, flg=0x104004, mvd=0
[02-JUN-2009 07:37:09:508] nsdo: gtn=199, gtc=199, ptn=10, ptc=2011
[02-JUN-2009 07:37:09:508] nscon: sending NSPTRS packet
[02-JUN-2009 07:37:09:508] nspsend: plen=8, type=11
[02-JUN-2009 07:37:09:508] nttwr: entry
[02-JUN-2009 07:37:09:508] nttwr: socket 41 had bytes written=8
[02-JUN-2009 07:37:09:508] nttwr: exit
[02-JUN-2009 07:37:09:508] nspsend: 8 bytes to transport
[02-JUN-2009 07:37:09:508] nsdo: nsctxrnk=0
[02-JUN-2009 07:37:09:508] nsevunreg: cid=10, sgt=0, rdm=0
[02-JUN-2009 07:37:09:508] nsevunreg: 7 registered connection(s)
[02-JUN-2009 07:37:09:508] nsevwait: 0 posted event(s)
[02-JUN-2009 07:37:09:508] nsevwait: 7 registered connection(s)
[02-JUN-2009 07:37:09:508] nsevwait: 1 pre-posted event(s)
[02-JUN-2009 07:37:09:508] nsevwait: waiting for transport event (1 thru 32)...
[02-JUN-2009 07:37:09:508] nsevwait: 1 newly-posted event(s)
[02-JUN-2009 07:37:09:508] nsevwait: 1 pre-posted event(s)
[02-JUN-2009 07:37:09:508] nsevdansw: at STAGE 9
[02-JUN-2009 07:37:09:508] nsevunreg: cid=32, sgt=0, rdm=0
[02-JUN-2009 07:37:09:508] nsevunreg: 6 registered connection(s)
[02-JUN-2009 07:37:09:508] nstimarmed: no timer allocated
[02-JUN-2009 07:37:09:508] nttctl: entry
[02-JUN-2009 07:37:09:508] nttctl: entry
[02-JUN-2009 07:37:09:508] nsdo: cid=32, opcode=98, *bl=0, *what=0, uflgs=0x40, cflgs=0x2
[02-JUN-2009 07:37:09:508] nsdo: rank=64, nsctxrnk=0
[02-JUN-2009 07:37:09:508] nsdo: nsctx: state=2, flg=0x104000, mvd=0
[02-JUN-2009 07:37:09:509] nsdo: nsctxrnk=0
[02-JUN-2009 07:37:09:509] nsclose: closing transport
[02-JUN-2009 07:37:09:509] nttdisc: entry
[02-JUN-2009 07:37:09:509] nttdisc: Closed socket 41
[02-JUN-2009 07:37:09:509] nttdisc: exit
[02-JUN-2009 07:37:09:509] nsclose: global context check-out (from slot 32) complete
[02-JUN-2009 07:37:09:509] nsmfr: 1912 bytes at 0x6000000000281300

here is the few lines of listner1[04-JUN-2009 09:48:19:792] ntprd: entry
[04-JUN-2009 09:48:19:793] ntprd: exit
[04-JUN-2009 09:48:19:793] ntprd: entry
[04-JUN-2009 09:48:19:793] ntprd: exit
[04-JUN-2009 09:48:19:793] nsbequeath: NSE=12586
[04-JUN-2009 09:48:19:793] nsbequeath: error reading REDIR/NSE msg
[04-JUN-2009 09:48:19:793] nserror: nsres: id=4, op=72, ns=12586, ns2=0; nt[0]=0, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[04-JUN-2009 09:48:19:793] nsdo: cid=4, opcode=67, *bl=0, *what=13, uflgs=0x0, cflgs=0x3
[04-JUN-2009 09:48:19:793] nsdo: rank=64, nsctxrnk=0
[04-JUN-2009 09:48:19:793] nsdo: nsctx: state=2, flg=0x4004, mvd=0
[04-JUN-2009 09:48:19:793] nsdo: gtn=170, gtc=170, ptn=10, ptc=2011
[04-JUN-2009 09:48:19:793] nscon: sending NSPTRS packet
[04-JUN-2009 09:48:19:793] nspsend: plen=8, type=11
[04-JUN-2009 09:48:19:793] nttwr: entry
[04-JUN-2009 09:48:19:793] nttwr: socket 13 had bytes written=8
[04-JUN-2009 09:48:19:793] nttwr: exit
[04-JUN-2009 09:48:19:793] nspsend: 8 bytes to transport
[04-JUN-2009 09:48:19:793] nsdo: nsctxrnk=0
[04-JUN-2009 09:48:19:793] ntpdisc: entry
[04-JUN-2009 09:48:19:793] ntpdisc: exit
[04-JUN-2009 09:48:19:793] nsbeqaddr: connect handshake is complete
[04-JUN-2009 09:48:19:793] nstimarmed: no timer allocated
[04-JUN-2009 09:48:19:793] nttctl: entry
[04-JUN-2009 09:48:19:794] nttctl: entry
[04-JUN-2009 09:48:19:794] nsdo: cid=4, opcode=98, *bl=0, *what=0, uflgs=0x40, cflgs=0x2
[04-JUN-2009 09:48:19:794] nsdo: rank=64, nsctxrnk=0
[04-JUN-2009 09:48:19:794] nsdo: nsctx: state=2, flg=0x4000, mvd=0
[04-JUN-2009 09:48:19:794] nsevunreg: cid=4, sgt=0, rdm=0
[04-JUN-2009 09:48:19:794] nsevunreg: 2 registered connection(s)
[04-JUN-2009 09:48:19:794] nsdo: nsctxrnk=0
[04-JUN-2009 09:48:19:794] nsclose: closing transport
[04-JUN-2009 09:48:19:794] nttdisc: entry
[04-JUN-2009 09:48:19:794] nttdisc: Closed socket 13
[04-JUN-2009 09:48:19:794] nttdisc: exit
[04-JUN-2009 09:48:19:794] nsclose: global context check-out (from slot 4) complete
[04-JUN-2009 09:48:19:794] nsmfr: 1912 bytes at 0x60000000001ebf80
[04-JUN-2009 09:48:19:794] nsmfr: 816 bytes at 0x60000000001ebc30
[04-JUN-2009 09:48:19:794] nsglhe: exit
[04-JUN-2009 09:48:19:794] nsevwait: 2 registered connection(s)
[04-JUN-2009 09:48:19:794] nsevwait: 0 pre-posted event(s)
[04-JUN-2009 09:48:19:794] nsevwait: waiting for transport event (1 thru 3)...

Re: Maximum session exceeded [message #406413 is a reply to message #406249] Wed, 03 June 2009 23:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>here is the last few lines of listner.trc
not listener.trc
but listener.log
Re: Maximum session exceeded [message #406423 is a reply to message #406413] Wed, 03 June 2009 23:48 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
Sorry for the previous reply. Here is the log of first listener

04-JUN-2009 10:15:45 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50863)) * establish * ppms * 0
04-JUN-2009 10:15:45 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50864)) * establish * ppms * 0
04-JUN-2009 10:15:45 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50865)) * establish * ppms * 0
04-JUN-2009 10:15:45 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50866)) * establish * ppms * 0
04-JUN-2009 10:15:45 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50867)) * establish * ppms * 0
04-JUN-2009 10:15:45 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50868)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50869)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50870)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50871)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50872)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50873)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50874)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50875)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50876)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50877)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50878)) * establish * ppms * 0
04-JUN-2009 10:15:46 * service_update * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50879)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50880)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50881)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50882)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50883)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50884)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50885)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50886)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50887)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50888)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50889)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50890)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50891)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50892)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50893)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50894)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50895)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50896)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50897)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50898)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50899)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50900)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50901)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50902)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50903)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50904)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50905)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50906)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50907)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50908)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50909)) * establish * ppms * 0
04-JUN-2009 10:15:46 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50910)) * establish * ppms * 0
04-JUN-2009 10:15:47 * (CONNECT_DATA=(SERVICE_NAME=ppms)(CID=(PROGRAM=)(HOST=ppms1)(USER=ppms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.5)(PORT=50911)) * establish * ppms * 0

Here is the log of second listner

04-JUN-2009 07:28:22 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64192)) * establish * ppms * 0
04-JUN-2009 07:30:09 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64221)) * establish * ppms * 0
04-JUN-2009 07:34:22 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64250)) * establish * ppms * 0
04-JUN-2009 07:38:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64284)) * establish * ppms * 0
04-JUN-2009 07:40:22 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64313)) * establish * ppms * 0
04-JUN-2009 07:48:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64385)) * establish * ppms * 0
04-JUN-2009 07:49:22 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64394)) * establish * ppms * 0
04-JUN-2009 07:55:22 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64458)) * establish * ppms * 0
04-JUN-2009 07:58:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64480)) * establish * ppms * 0
04-JUN-2009 08:01:22 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64518)) * establish * ppms * 0
04-JUN-2009 08:07:22 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64568)) * establish * ppms * 0
04-JUN-2009 08:08:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64575)) * establish * ppms * 0
04-JUN-2009 08:13:22 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64651)) * establish * ppms * 0
04-JUN-2009 08:17:24 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64708)) * establish * ppms * 0
04-JUN-2009 08:18:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64715)) * establish * ppms * 0
04-JUN-2009 08:22:22 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64778)) * establish * ppms * 0
04-JUN-2009 08:24:09 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64792)) * establish * ppms * 0
04-JUN-2009 08:28:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64826)) * establish * ppms * 0
04-JUN-2009 08:28:22 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64827)) * establish * ppms * 0
04-JUN-2009 08:34:22 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64885)) * establish * ppms * 0
04-JUN-2009 08:38:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64922)) * establish * ppms * 0
04-JUN-2009 08:43:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=64974)) * establish * ppms * 0
04-JUN-2009 08:48:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65015)) * establish * ppms * 0
04-JUN-2009 08:49:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65024)) * establish * ppms * 0
04-JUN-2009 08:58:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65135)) * establish * ppms * 0
04-JUN-2009 08:58:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65136)) * establish * ppms * 0
04-JUN-2009 09:04:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65187)) * establish * ppms * 0
04-JUN-2009 09:08:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65233)) * establish * ppms * 0
04-JUN-2009 09:12:10 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65275)) * establish * ppms * 0
04-JUN-2009 09:13:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65280)) * establish * ppms * 0
04-JUN-2009 09:18:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65326)) * establish * ppms * 0
04-JUN-2009 09:19:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65336)) * establish * ppms * 0
04-JUN-2009 09:20:24 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65360)) * establish * ppms * 0
04-JUN-2009 09:25:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65406)) * establish * ppms * 0
04-JUN-2009 09:28:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65439)) * establish * ppms * 0
04-JUN-2009 09:31:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65467)) * establish * ppms * 0
04-JUN-2009 09:38:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=65527)) * establish * ppms * 0
04-JUN-2009 09:40:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49175)) * establish * ppms * 0
04-JUN-2009 09:46:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49233)) * establish * ppms * 0
04-JUN-2009 09:48:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49248)) * establish * ppms * 0
04-JUN-2009 09:52:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49301)) * establish * ppms * 0
04-JUN-2009 09:58:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49347)) * establish * ppms * 0
04-JUN-2009 09:58:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49348)) * establish * ppms * 0
04-JUN-2009 10:04:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49412)) * establish * ppms * 0
04-JUN-2009 10:04:40 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49427)) * establish * ppms * 0
04-JUN-2009 10:06:10 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49439)) * establish * ppms * 0
04-JUN-2009 10:08:19 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49450)) * establish * ppms * 0
04-JUN-2009 10:12:10 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49502)) * establish * ppms * 0
04-JUN-2009 10:12:10 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49503)) * establish * ppms * 0
04-JUN-2009 10:13:23 * (CONNECT_DATA=(SID=ppms)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=tibco))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.21.253.7)(PORT=49511)) * establish * ppms * 0





Re: Maximum session exceeded [message #406540 is a reply to message #406423] Thu, 04 June 2009 09:05 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Also, post SQLNET.ORA.

Thanks
Re: Maximum session exceeded [message #406545 is a reply to message #406540] Thu, 04 June 2009 09:18 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It looks like BlackSwan was on the right track, though.

Lot's of simultaneous connections by that "ppms" user.
Re: Maximum session exceeded [message #406548 is a reply to message #406249] Thu, 04 June 2009 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>04-JUN-2009 10:15:46 * service_update * ppms * 0
Above is somewhat key to this situation.
The listener was modified starting with V10.
Part of what happens with "service update" above is the DB informs listener how many current sessions exist within DB & MAX sessions allowed.
After this point in time the listener keeps track of new connection requests being passed onto the DB AND decrementing its own counter of available processes left in DB.
When the listener's counter hits ZERO the listener itself starts returning ORA-00018 errors.
It does not know that many of the sessions have terminated & the DB could actually handle more/new session.
After some time passes, the listener gets another "service update" & starts passing connection requests on to the DB.
It is doubtful you will ever see "too many" session within DB.

There are two possible solutions.
1) modify application to use connection pooling & stop pummeling DB with new connection requests at a very high rate.
2) increase PROCESSES parameter so the nag-o-gram error message is not generated by the listener.

Hope This Helps!

[Updated on: Thu, 04 June 2009 09:33]

Report message to a moderator

Re: Maximum session exceeded [message #406609 is a reply to message #406548] Thu, 04 June 2009 23:48 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
thank you a lot sir for sorting me out.
Re: Maximum session exceeded [message #406610 is a reply to message #406609] Thu, 04 June 2009 23:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vinniora wrote on Thu, 04 June 2009 21:48
thank you a lot sir for sorting me out.


Exactly how do you implement complete solution?
Re: Maximum session exceeded [message #406617 is a reply to message #406610] Fri, 05 June 2009 00:10 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
Actually sir ,i discussed your solution with my senior dba he is working on modify application to use connection pooling as told by u and also we have increased sga_target as UGA is not able to increase in memory as session is increasing,after increasing the sga_target we are not facing the problem.Sir can you provide me the link regarding 'understanding listener.log' as your solution is execllent.Thnx again
Re: Maximum session exceeded [message #406618 is a reply to message #406249] Fri, 05 June 2009 00:18 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Sir can you provide me the link regarding 'understanding listener.log'
I have no link; only 1st hand experience.
What you will see in listener.log are connection failures proceeding "service update" records in listener.log
& after it connection requests will succeed.


Good Luck!
Previous Topic: Partition Maintenance
Next Topic: altering undo tablepace
Goto Forum:
  


Current Time: Mon Dec 09 20:23:34 CST 2024