Question: How to limit the access to the database so that only one user per schema are connected (one concurrent user per schema)
1. Set in init.ora or spfile.ora the parameter:
resource_limit = true
and restart Database.
You can perform this without restarting Database by issuing as SYS: “alter system set resource_limit=true;”
2. Connect to Database as SYSTEM or SYS and create the following Profile:
create profile single_user limit sessions_per_user 1;
The other parameters should be adjusted too, but here we discuss only the session_per_user.
3. Create the user/schema where only one concurrent connection should be allowed and grant other needed privileges.
create user test1 identified by test1 profile single_user; grant connect, resource to test1;
4. Test it by trying to connecting with two users “test1”:
Session 1:
connect test1/test1 Connected.
Session 2:
Connect test1/test1 ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit