[oracle] How to kill all active and inactive oracle sessions for user

I am trying the below script to kill all active and inactive oracle sessions for user at once but it doesn't work. The script executes successfully but does not kill sessions for user.

BEGIN
  FOR r IN (select sid,serial# from v$session where username = 'USER')
  LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid 
      || ',' || r.serial# || '''';
  END LOOP;
END;

BEGIN
  FOR r IN (select sid,serial# from v$session where username='user')
  LOOP
      EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  || ',' || r.serial# || '''';
  END LOOP;
END;
/

It works for me.

BEGIN
  FOR r IN (select sid,serial# from v$session where username='user')
  LOOP
      EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  || ',' 
        || r.serial# || ''' immediate';
  END LOOP;
END;

This should work - I just changed your script to add the immediate keyword. As the previous answers pointed out, the kill session only marks the sessions for killing; it does not do so immediately but later when convenient.

From your question, it seemed you are expecting to see the result immediately. So immediate keyword is used to force this.

inactive session the day before kill

begin_x000D_
    for i in (select * from v$session where status='INACTIVE' and (sysdate-PREV_EXEC_START)>1)_x000D_
    LOOP_x000D_
        EXECUTE IMMEDIATE(q'{ALTER SYSTEM KILL SESSION '}'||i.sid||q'[,]'  ||i.serial#||q'[']'||' IMMEDIATE');_x000D_
    END LOOP;_x000D_
end;

Execute this script:

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' 
FROM v$session 
where username='YOUR_USER';

It will printout sqls, which should be executed.

For Oracle 11g this may not work as you may receive an error like below

Error report:
SQL Error: ORA-00026: missing or invalid session ID
00026. 00000 -  "missing or invalid session ID"
*Cause:    Missing or invalid session ID string for ALTER SYSTEM KILL SESSION.
*Action:   Retry with a valid session ID.

To rectify this, use below code to identify the sessions

SQL> select inst_id,sid,serial# from gv$session or v$session

NOTE : v$session do not have inst_id field

and Kill them using

alter system kill session 'sid,serial,@inst_id' IMMEDIATE;