How to Enable / Disable Restricted Session in Oracle Database


In restricted session mode only the users with restricted session privilege can connect to database , other normal users cannot connect with database unless we disable the restricted session mode.

Already Connected users can perform thier DML/DDL activities , but no new session will create for the users .

So before putting the database in restricted session mode , it must be verified that all sessions are killed using V$SESSION view.

Let’s See how we can put database in restricted session mode:

SQL> alter system enable restricted session ;
System altered.

We can also grant any user with restricted session privilege so that user can connect to database in restricted session to, we can grant restricted session privilege to that user in this way:

SQL> select logins from v$instance ;


SQL> grant restricted session to d1 ;

Grant succeeded.

Revoking privileges back from the user 

SQL> revoke restricted session from d1 ;

Revoke succeeded.

Disabling Restricted session , so other normal users can also connect to the database 

SQL> alter system disable restricted session ;

System altered.

How useful was this post?

Click on a star to rate it!

Average rating 4 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

Scroll to Top