Avoid ORA-00020 error for SYS login into database with trigger in Oracle

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.

Trigger
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
DECLARE
V_USER DBA_USERS.USERNAME%TYPE := USER;
V_COUNT NUMBER;
V_PARAM_PROCESSES NUMBER;
BEGIN
IF (UPPER (v_user) NOT IN ('SYS', 'SYSTEM'))
THEN
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');
END IF;
END IF;
END;
/

This entry was posted in Oracle on by .

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.