Block new session after certain number of session in single schema.

CREATE TABLE SCHEMA_NUMBER_OF_SESSION
(
   USER_NAME           VARCHAR2 (100),
   NUMBER_OF_SESSION   NUMBER (10),
   ALLOWED_SESSION  NUMBER (10)
);

CREATE OR REPLACE TRIGGER TR_LOGON_CHECK
   AFTER LOGON
   ON SCHEMA_NAME.SCHEMA
DECLARE
   V_NUMBER_OF_USER    NUMBER;
   V_ALLOWED_SESSION   NUMBER;
BEGIN
   SELECT NUMBER_OF_SESSION, ALLOWED_SESSION
     INTO V_NUMBER_OF_USER, V_ALLOWED_SESSION
     FROM SCHEMA_NUMBER_OF_SESSION
    WHERE USER_NAME = USER;

   IF V_NUMBER_OF_USER >= V_ALLOWED_SESSION
   THEN
      RAISE_APPLICATION_ERROR (-20100, 'New Session are not allowed.');
   ELSE
      UPDATE SCHEMA_NUMBER_OF_SESSION
         SET NUMBER_OF_SESSION = NUMBER_OF_SESSION + 1
       WHERE USER_NAME = USER;
   END IF;
 
END TR_LOGON_CHECK;


CREATE OR REPLACE TRIGGER TR_LOGOFF_UPDATE
   BEFORE LOGOFF
   ON SCHEMA_NAME.SCHEMA
DECLARE
   V_NUMBER_OF_USER    NUMBER;
   V_ALLOWED_SESSION   NUMBER;
BEGIN
   UPDATE SCHEMA_NUMBER_OF_SESSION
      SET NUMBER_OF_SESSION = NUMBER_OF_SESSION - 1
    WHERE USER_NAME = USER;
END TR_LOGOFF_UPDATE;