SQL & PL/SQL

Hi,

There are many answers in this forum related with my question but could not complete answer.

I have problem with process count in db. Firstly I killed sessions

alter system kill session 'xxx,yyyy';

and then queried v$session. The status of sessions were changed as killed but in v$process it showed that the count of process same as before.
So I decided to create profile and set idle time.

select * from v$resource_limit

RESOURCE_NAME	CURRENT_UTILIZATION	MAX_UTILIZATION	INITIAL_ALLOCATION	LIMIT_VALUE
processes	459	466	       500	       500
sessions	459	467	       555	       555


select * from dba_profiles

PROFILE	RESOURCE_NAME	RESOURCE_TYPE	LIMIT
myUser_PROFILE	IDLE_TIME	KERNEL	2880 (2 days)



select username,profile from dba_users

USERNAME    PROFILE
myUser      myUser_PROFILE

select 
       p.spid,
       s.username,
       s.sid,
       s.serial#,
       to_char(s.logon_time, 'Dy dd Mon HH24:MI:SS') start_time,
       s.status
       
  from V$PROCESS p, V$SESSION s
 where s.paddr = p.addr
   and s.username is not null;



SPID	USERNAME	SID	SERIAL#	START_TIME	STATUS
3880	myUser	181	102	Wed 24 Mar 19:37:10	SNIPED
5135	myUser	71	190	Wed 24 Mar 20:41:00	SNIPED
5259	myUser	422	3,794	Wed 24 Mar 20:47:31	SNIPED
5666	myUser	189	197	Wed 24 Mar 21:15:35	SNIPED
...
32109	myUser	421	3,746	Thu 25 Mar 21:57:07	SNIPED
32371	myUser	344	10,518	Thu 25 Mar 22:16:31	SNIPED
32395	myUser	65	28	Thu 25 Mar 22:18:01	SNIPED
32518	myUser	35	205	Thu 25 Mar 22:23:37	SNIPED
...
19703	myUser	40	18	Fri 26 Mar 21:03:24	SNIPED
19870	myUser	242	106	Fri 26 Mar 21:12:05	SNIPED
19965	myUser	212	11,276	Fri 26 Mar 21:19:10	SNIPED
...
23054	myUser	215	535	Sat 27 Mar 00:08:01	SNIPED
23668	myUser	17	6,237	Sat 27 Mar 00:48:25	SNIPED
26200	myUser	218	1,323	Sat 27 Mar 03:44:07	SNIPED
...
18830	myUser	388	256	Sun 28 Mar 00:40:10	INACTIVE
18894	myUser	177	461	Sun 28 Mar 00:41:35	INACTIVE
18954	myUser	357	36	Sun 28 Mar 00:45:09	INACTIVE
19124	myUser	52	325	Sun 28 Mar 00:53:38	INACTIVE
...
14697	myUser	448	2	Mon 29 Mar 00:06:05	INACTIVE
14741	myUser	447	6	Mon 29 Mar 00:07:53	INACTIVE
14786	myUser	449	3	Mon 29 Mar 00:09:26	INACTIVE
...
8678	SYS	461	10,762	Mon 29 Mar 10:45:00	ACTIVE

I think pmon should kill process related with killed session but it does not.

Please explain me how it happens,what should I do.

After while I will again get error as 'ORA-00020: maximum number of processes (%s) exceeded'

Thanks in advance

Edited by: catastrophe on Mar 28, 2010 11:24 PM