· SQL –
- Profile is set of
restrictions which can be given to user or role.
- Each user or role gets
default profile if no profile was specified at creation time.
info can be found at http://www.psoug.org/reference/profiles.html.
CREATE PROFILE MyProfile
ALTER PROFILE MyProfile
LIMIT idle_time 20; --minutes
DROP PROFILE MyProfile
CREATE PROFILE MyProfile LIMIT
- Non password restrictions
will take efect only if system parameter RESOURCE_LIMIT is set to TRUE as described
in [SQL – System].
- When IDLE_TIME is set in the users' profiles or the default
- This will kill the sessions in the database (status in
v$session now becomes SNIPED) and they will eventually disconnect.
- It does not always clean up the Unix session (LOCAL=NO sessions).
- At this time all oracle resources are released but the
shadow processes remains and OS resources are not released.
- This shadow process is still counted towards the parameters
- This process is killed and entry from v$session is released
only when user again tries to do something.
- Another way of forcing disconnect (if your users come in
via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter
"SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session.
- What is your init.ora parameter resource_limit set
to? If FALSE (the default) profiles are not enforced.
- Only when
set to TRUE will it work.
- The way
the idle time logout works is:
logs in. Session status will be active or inactive in V$SESSION
- After X
minutes in the inactive status, their session will be KILLED.
status in v$session will be turned into KILLED.
locks and resources they had will be released but their CONNECTION will remain.
very next time the client contacts the database, they will receive the error
message "your session has been killed".
- At that
point in time, their entry in V$SESSION will go away. (but not before that).
that is how the client will be notified, as soon as they attempt to use the
database, the database will tell them "sorry,
you've been logged out".
and only then their physical connection to the database will "go
to that, they are effectively logged out (no longer holding locks and such) but