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;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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