Overcome ORA-00020 error for SYS login into database with trigger in Oracle
Allow SYS/SYSTEM user to make connection without getting error of “ORA-00020: maximum number of processes (%s) exceeded” by creating a database trigger on user logon event.
Its allow SYS/SYSTEM user to login with Logon Trigger to limit the Connections Below the Processes Parameter Value.
We keep 5 session for SYS and SYSTEM user to login from maximum process defined in Parameter of instance.
Trigger would limits number of application user process connected and keep a headroom of 5 process for SYS/SYSTEM user connections.
CREATE OR REPLACE TRIGGER avoid_ORA00020_error
AFTER LOGON ON DATABASE
V_USER DBA_USERS.USERNAME%TYPE := USER;
IF (UPPER (v_user) NOT IN ('SYS', 'SYSTEM'))
SELECT COUNT(*) INTO V_COUNT FROM V$PROCESS;
SELECT VALUE INTO V_PARAM_PROCESSES FROM V$PARAMETER WHERE NAME = 'processes';
IF V_COUNT > V_PARAM_PROCESSES - 5 THEN
RAISE_APPLICATION_ERROR(-20100, 'No more connections allowed, Only SYS/SYSTEM User allowed');