OraFAQ Forum: Server Administration » Maximum session exceeded

Home » RDBMS Server » Server Administration » Maximum session exceeded (Oracle Database 10.1.0.2.0 - 64bi,Hpux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Maximum session exceeded [message #406249]

Wed, 03 June 2009 01:01

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.

Report message to a moderator

Re: Maximum session exceeded [message #406277 is a reply to message #406249]

Wed, 03 June 2009 02:03

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.

Report message to a moderator

Re: Maximum session exceeded [message #406278 is a reply to message #406277]

Wed, 03 June 2009 02:10

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.

Report message to a moderator

Re: Maximum session exceeded [message #406280 is a reply to message #406278]

Wed, 03 June 2009 02:12

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

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

Report message to a moderator

Re: Maximum session exceeded [message #406283 is a reply to message #406282]

Wed, 03 June 2009 02:40

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.

Report message to a moderator

Re: Maximum session exceeded [message #406293 is a reply to message #406283]

Wed, 03 June 2009 03:33

vinniora
Messages: 56
Registered: October 2008
Location: Mumbai

Member

>> select count(*) from v$session

241

Report message to a moderator

Re: Maximum session exceeded [message #406300 is a reply to message #406293]

Wed, 03 June 2009 03:53

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.

Report message to a moderator

Re: Maximum session exceeded [message #406307 is a reply to message #406300]

Wed, 03 June 2009 04:17

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

Report message to a moderator

Re: Maximum session exceeded [message #406309 is a reply to message #406307]

Wed, 03 June 2009 04:21

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

So increase shared_server_sessions, and see if it works then.

Report message to a moderator

Re: Maximum session exceeded [message #406313 is a reply to message #406309]

Wed, 03 June 2009 04:42

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???

Report message to a moderator

Re: Maximum session exceeded [message #406332 is a reply to message #406313]

Wed, 03 June 2009 06:16

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

One way to find out would be to try it.

Report message to a moderator

Re: Maximum session exceeded [message #406367 is a reply to message #406249]

Wed, 03 June 2009 12:01

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".

Report message to a moderator

Re: Maximum session exceeded [message #406407 is a reply to message #406367]

Wed, 03 June 2009 22:43

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

Report message to a moderator

Re: Maximum session exceeded [message #406410 is a reply to message #406249]

Wed, 03 June 2009 23:07

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

Report message to a moderator

Re: Maximum session exceeded [message #406412 is a reply to message #406410]

Wed, 03 June 2009 23:19

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)...

Report message to a moderator

Re: Maximum session exceeded [message #406413 is a reply to message #406249]

Wed, 03 June 2009 23:23

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

Report message to a moderator

Re: Maximum session exceeded [message #406423 is a reply to message #406413]

Wed, 03 June 2009 23:48

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

Report message to a moderator

Re: Maximum session exceeded [message #406540 is a reply to message #406423]

Thu, 04 June 2009 09:05

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

Also, post SQLNET.ORA.

Thanks

Report message to a moderator

Re: Maximum session exceeded [message #406545 is a reply to message #406540]

Thu, 04 June 2009 09:18

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.

Report message to a moderator

Re: Maximum session exceeded [message #406548 is a reply to message #406249]

Thu, 04 June 2009 09:32

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

vinniora
Messages: 56
Registered: October 2008
Location: Mumbai

Member

thank you a lot sir for sorting me out.

Report message to a moderator

Re: Maximum session exceeded [message #406610 is a reply to message #406609]

Thu, 04 June 2009 23:54

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?

Report message to a moderator

Re: Maximum session exceeded [message #406617 is a reply to message #406610]

Fri, 05 June 2009 00:10

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

Report message to a moderator

Re: Maximum session exceeded [message #406618 is a reply to message #406249]

Fri, 05 June 2009 00:18

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!

Report message to a moderator

Previous Topic:	Partition Maintenance
Next Topic:	altering undo tablepace

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 17:26:19 CST 2024