Benoit, you actually had a partial answer, everything you said was correct, but after next reboot, the account locked. After several hours of more research, the following issue was discovered, with the appropriate resolution.
Enterprise Manager had the default SYSMAN password stored for its credentials, and was trying to lock in with that password in rapid succession (300 times a minute or so according to the Event Log). Hence, after several rapid tries with the same wrong password, Oracle locks the account.
Setting a new password in SQL*PLUS or SQL Developer doesn't reset the password used to login to Enterprise Manager, to reset the password in enterprise manager, you need to open a command prompt and do the following:
emctl config oms sso -remove
emctl stop oms
emctl start oms
During the above commands, it'll prompt you for the current SYSMAN password, which you can enter, and it should remove the SYSMAN password used by your Enterprise Manager Console, replacing it with the one you just entered.
I suspect they're part of a connection pool & therefore haven't idled out because they're being used frequently.
INACTIVE
in v$session
merely means there isn't a SQL statement being executed at the exact moment you check v$session
.
If they're part of a connection pool they're doing their job properly by being logged in for long periods of time. The whole point of connection pooling is to remove the need for lots of logons/logoffs & keep persistent sessions for fast query startup - there's a much larger overhead logging in to execute one query, then disconnecting every time.
To get the last activity time for each session:
select username, UPPER(program), logon_time,
floor(last_call_et / 60) "Minutes since active", status
from v$session
where UPPER(program) LIKE '%W3%'
order by last_call_et;
I'd advise against killing sessions unless you know that doing so will not cause problems on the application side (trying to use a session that's been killed, for example).
It may be the case that you're looking at an incorrectly configured connection pool which creates hundreds of connections once the app starts up - the connection pool may be an order of magnitude bigger than it needs to be. I suggest reaching out to the developers/application support staff & taking a look at how the connection pool is configured.
Having done a bit of research, w3wp.exe is the IIS Application Pool Process - you almost certainly want to talk to your IIS webserver admins to help get to the bottom of the connection pooling configuration.
Best Answer
You mentioned system copy. Whenever you change the ipaddress or hostname, DBConsole goes haywire, and even the official solution is to recreate it as described in:
How to configure Dbconsole After the Ipaddress or hostname of the Machine has Been Changed or if the hostname ipaddress is going to be changed? (Doc ID 1333938.1)
The usual steps to do this: