Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • 10g RAC: Troubleshooting CRS Root.sh Problems Oracle
  • Export With Query Another Example. Oracle
  • Implementing Listener Security Oracle
  • OEM-Commnds Oracle
  • Kernel Parameters for Solaris Linux/Unix
  • Running select from V$ views from remote server Linux/Unix
  • How to find the real execution plan and binds used in that explain plan in Oracle 10g?? Oracle
  • v$event_name Oracle
  • remove archfiles only when it is applied to DR rm_archfiles.sh Linux/Unix
  • login.sql Oracle
  • catall.sh Linux/Unix
  • To Find Orphan OS processes. Linux/Unix
  • normal maintenance for exp-imp and renaming table Oracle
  • compile_inv.sql Oracle
  • Database link password in user_db_links Oracle

How To Limit The Access To The Database So That Only One User Per Schema Are Connected (One Concurrent User Per Schema)

Posted on 27-May-2009 By Admin No Comments on 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 the parameter

resource_limit = true

and restart Database.

Since Oracle 9iR2 (9.2.0.1) 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

Oracle, SQL scripts

Post navigation

Previous Post: Default User Profile
Next Post: How to Make Trace Files Created by Oracle Readable by All Users ?

Related Posts

  • Example of How To Resize the Online Redo Logfiles Note:1035935.6 Oracle
  • Standby Database Behavior when a Datafile is Resized on the Primary Database Note:123883.1 Oracle
  • tblwopk.sql tablewopk.sql Oracle
  • Another Tuning Article for subheap of shared pool Oracle
  • DBMS_UTILITY.ANALYZE_SCHEMA Oracle
  • Good Site for Oracle Internals Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Oracle Material from OTN Oracle
  • DB Console Mainenance. Oracle
  • Consolidated Reference List Of Notes For Migration / Upgrade Service Requests -ID 762540.1 Oracle
  • Search and Replace vi editor command. Linux/Unix
  • ENQ: KO – FAST OBJECT CHECKPOINT tips Oracle
  • Convert multiple rows to single column Oracle
  • Another Tuning Article for subheap of shared pool Oracle
  • proc.sql Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme