Manage Password expiration via Profile in Oracle

===========================
General
===========================
Make user password never expire:

SELECT username, profile, expiry_date 
  FROM DBA_USERS;

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME  UNLIMITED;


Users that were already expired, will not be affected by above statements, need to unlock them, and reset their password.
Only then the password expiration_date becomes NULL;


ALTER USER XXX IDENTIFIED BY YYY;

Disconnect vs Kill Oracle sessions

===============================
General
===============================
Disconnect vs Kill session

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

killing oracle sessions


The KILL SESSION command doesn't actually kill the session. 
It merely asks the session to kill itself. 
In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. 
In these cases the session will have a status of "marked for kill". 
It will then be killed as soon as possible.

The IMMEDIATE clause does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

If the marked session persists for some time you may consider killing the process at the operating system level. 
Before doing this it's worth checking to see if it is performing a rollback. 
You can do this by running this script (session_undo.sql). 
If the TX.used_urec value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.

session_undo.sql
SELECT SESSION.username,
       SESSION.sid,
       SESSION.serial#,
       TX.used_ublk,
       TX.used_urec,
       ROLLBACK_SEGS.segment_name,
       ROLLSTAT.rssize,
       ROLLSTAT.status
FROM   V$TRANSACTION TX,
       V$SESSION SESSION,
       V$ROLLSTAT ROLLSTAT,
       DBA_ROLLBACK_SEGS ROLLBACK_SEGS
WHERE  SESSION.saddr = TX.ses_addr
  AND  TX.xidusn = ROLLSTAT.usn
  AND  ROLLBACK_SEGS.segment_id = TX.xidusn

ORDER BY TX.used_ublk DESC;


ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. 
Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process which is equivalent to killing the server process from the operating system. 
The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. 
The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.


===============================
Example 1
===============================
Disconnect sessions running idle more than 8 hours.

What is V$SESSION.last_call_et?
If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active. 

If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive. 

CREATE OR REPLACE PROCEDURE INACTIVE_SESSION_PR
AS
  -- Sessions with connection time greater than 8 hours
  CURSOR long_idle_sessions_cur IS
    SELECT sid, serial#, username
      FROM V$SESSION  
     WHERE username IS NOT NULL
       AND status='INACTIVE'
       AND UPPER(program)='JDBC THIN CLIENT'
       AND UPPER(osuser) IN('NOBODY','MY_USER')
       AND last_call_et/60/60 > 8;
  
 v_sql_str VARCHAR2(1000);
BEGIN
  FOR long_idle_sessions_rec IN long_idle_sessions_cur LOOP

    v_sql_str:='ALTER SYSTEM DISCONNECT SESSION '''||long_idle_sessions_rec.sid||','||long_idle_sessions_rec.serial#||''' IMMEDIATE';

    EXECUTE IMMEDIATE v_sql_str;

  END LOOP;
END;
/

===============================
Example 2
===============================
Kill session behind a job

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' kill_session_command

  FROM V$SESSION 

  WHERE sid =

      (SELECT DJR.sid 

         FROM DBA_JOBS_RUNNING DJR, DBA_JOBS DJ

WHERE DJ.job = DJR.job

  AND DJ.what = 'SH_REFRESH_PKG.REFRESH_PRC;'

  AND DJ.schema_user = (SELECT user FROM DUAL)

      );

SELECT 'ALTER SYSTEM KILL SESSION ' || SUBSTR(''''||v_session.sid||','||v_session.serial#||'''', 1,15)||' IMMEDIATE;' AS "Kill Session",

       'kill -9 '||v_process.spid AS "LINUX Kill",

       'orakill '||INSTANCE_NAME||' '||v_process.spid AS "orakill command", 

       v_session.machine AS "SERVER",

       v_session.osuser AS OS_USER,

       v_session.status,    

       v_session.program,

       v_session.sid,

       v_session.serial#,

       v_process.spid,

       v_session.username,       

       DBA_JOBS.schema_user,

       DBA_JOBS.job,

       DBA_JOBS.what,

       TO_CHAR(v_session.logon_time,'YYYYMMDD hh24:mi:ss') AS logon_time

FROM   v$session v_session,

       v$process v_process,

       V$INSTANCE,

       DBA_JOBS_RUNNING stuck_jobs,

       DBA_JOBS     

WHERE  v_process.addr = v_session.paddr 

  AND  v_session.type != 'BACKGROUND'

  AND  v_session.sid = stuck_jobs.sid

  AND  DBA_JOBS.job(+) = stuck_jobs.job;


========================== 

Procedure to kill sessions
==========================
It is not possible to grant "KILL SESSION" privileges

An alternative, is to create under SYS user a procedure that would kill session, and grant execute privileges on this procedure.

CREATE OR REPLACE PROCEDURE KILL_SESSION
            (p_sid    IN V_$SESSION.sid%TYPE,
             p_serial IN V_$SESSION.sid%TYPE) AS
  v_username VARCHAR2(30);
BEGIN
  SELECT username INTO v_username FROM V$SESSION WHERE sid = p_sid AND serial# = p_serial;
  IF v_username IS NOT NULL AND v_username NOT IN ('SYS','SYSTEM','OGG','WMSYS','XDB','DBSNMP') THEN
    EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION '''||p_sid||','||p_serial||'''';
  ELSE
    NULL;
  END IF;
END KILL_SESSION; 
/   

as user sys:

PROCEDURE inactive_session_pr AS

  v_str VARCHAR2(1000);

BEGIN

  FOR i IN (

    SELECT sid, serial#, username , ROUND(last_call_et/60/60) as idle_hours

      FROM V$SESSION

     WHERE username IS NOT NULL

       AND status='INACTIVE'

       AND program='JDBC Thin Client'

       AND osuser in('nobody','iu')

       AND last_call_et/60/60>8   ---- sessions connection time greater than 8 hours

  ) LOOP

    v_str:='ALTER SYSTEM DISCONNECT SESSION '''||i.sid||','||i.serial#||''' IMMEDIATE';

    EXECUTE IMMEDIATE v_str;

    BEGIN

      INSERT INTO SH_DBA.SGA_W_LOG(procedure_name, data, ts_last_modified)

      VALUES('inactive_session_pr', 'Killed Session of user '||i.username||' idle for '||i.idle_hours||' hours.'||' sid-

serial#'||' : '||i.sid||'-'||i.serial#, SYSDATE);

      commit;

    EXCEPTION

      WHEN OTHERS THEN

        NULL;

    END;

  END LOOP;

END;

/

GRANT SELECT ON V_$SESSION TO SOME_ADMIN_USER;
--GRANT ALTER SYSTEM TO SH_DBA; too much permissions
GRANT ALTER SESSION TO SH_DBA;

GRANT EXECUTE ON KILL_SESSION_PROC TO SOME_ADMIN_USER;

as user sh_dba:

create or replace PROCEDURE inactive_session_pr AS
BEGIN
  SYS.inactive_session_pr;

END;

/

===============================
Kill Sessions
===============================

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; 

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE; '

FROM GV$SESSION

WHERE user# <> 0

  AND username='SOME_USER';

===============================
Kill Sessions in RAC
===============================

ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' IMMEDIATE; 

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||','||@inst_id||''' IMMEDIATE; '

FROM GV$SESSION

WHERE user# <> 0

  AND username='SOME_USER';