Oracle Script For Getting Long Active Sessions

I'm not sure that there is a problem.

A session in V$SESSION has a status of "INACTIVE" if it is not executing a SQL statement at that particular instant in time. If a user opens a dedicated session in a client-server application and is using the application intensively, it would not be at all unusual for the session to be "INACTIVE" 99% of the time because the vast majority of the time is spent waiting for the human to read and process the data on the screen. The fact that a session is "INACTIVE" doesn't imply that it wasn't "ACTIVE" a few seconds ago or that it won't be "ACTIVE" again a few seconds from now. The LAST_CALL_ET column in V$SESSION shows you the number of seconds since a session was last in an "ACTIVE" status if the session is currently in an "INACTIVE" status. That may give you some idea whether you are dealing with sessions that have been active for a while or not.

If you have a three-tier application, your middle tier servers will generally maintain a pool of database connections. The middle tier opens a number of connections and keeps them open more or less indefinitely. When users need to interact with the database, they get a connection from the pool, run a query, and return the connection to the pool. At peak workloads, connection pooling can concentrate a large number of application users into a relatively smaller number of database connections which will mean that the sessions spend a larger fraction of their time in an "ACTIVE" status. But during periods of low usage, the middle tier probably maintains a bunch of "INACTIVE" sessions.

In either of these cases, having a number of "INACTIVE" sessions is not a bad thing. And "cleaning up" those inactive sessions will cause errors in the applications that have opened them.

If you have a large number of inactive sessions because the client processes have failed (i.e. the client application crashed and the database is unaware of that fact), you could use the sqlnet.expire_time parameter on the server's sqlnet.ora file to periodically send a probe packet to the client machine. That may negatively impact performance but it will allow the database to more quickly discover that the client process has failed.

If you have a large number of inactive sessions because your middle tier servers have created too many connections to the database in their connection pools, you would need to talk with the middle tier admins to ask them to reduce the number of connections they open in their connection pools. If they have recently added more servers to the middle tier farm, they may need to reduce the number of connections each server maintains in its connection pool.

If your problem is that too much RAM is being allocated to all the dedicated server connections that need to be open (particularly common if you've got a lot of client server applications), you could configure Oracle to support shared server connections since that reduces the amount of memory that each connection requires.

When you issue an ALTER SYSTEM KILL SESSION, Oracle merely marks the session as killed and does the actual work of killing the session asynchronously. That may take just a couple seconds, it may take many hours if the session has an uncommitted transaction that did a lot of work that now has to be rolled back or if the session needs to stay around in a killed state in order to notify the client that their session was terminated. It may, therefore, require a substantial amount of waiting before a user could be dropped.

You could do something like this where your loop will wait indefinitely for all the sessions to disappear.

DECLARE
  l_cnt integer;
BEGIN
  EXECUTE IMMEDIATE 'alter user abc_user account lock';
  FOR x IN (SELECT *
              FROM v$session
             WHERE username = 'ABC_USER')
  LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || x.sid || ',' || x.serial# || '''';
  END LOOP;

  -- Wait for as long as it takes for all the sessions to go away
  LOOP
    SELECT COUNT(*)
      INTO l_cnt
      FROM v$session
     WHERE username = 'ABC_USER';
    EXIT WHEN l_cnt = 0;
    dbms_lock.sleep( 10 );
  END LOOP;

  EXECUTE IMMEDIATE 'drop user abc_user cascade';
END;

Generally, though, I'd question the problem you're trying to solve. I can't think of many times that it would make sense to drop a user that has active sessions at the time you decide to issue the drop. In the vast majority of cases, the presence of active sessions strongly implies that the account should not be dropped.