Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Some OS level threshold for performance. Linux/Unix
  • Adding addidional hard drive and attach it to a linux box. Linux/Unix
  • Best approach for Oracle database patching sequence to latest/required patchset along with CPU/PSU/any-other-one-off patch ID 865255.1 Oracle
  • oracle 10g on linux Linux/Unix
  • Parallel DML Oracle
  • Good Site for Oracle Internals Oracle
  • CTAS with LONG Column for 7.x and 8 and 8i Oracle
  • To Find Orphan OS processes. Linux/Unix
  • move_arch_files.ksh Linux/Unix
  • Export Oracle data and Compress at same time Oracle
  • Passing from Unix to PLSQL using bind variables Linux/Unix
  • On solaris 10, “S” link is not part of $ORACLE_HOME/bin/oracle as default. ( For 9.2.0.8) Oracle
  • normal maintenance for exp-imp and renaming table Oracle
  • get_aix_vmstat.ksh Oracle
  • Disbaling DBA_SCHEDULER_JOBS 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

  • Find_stale_dr.sql finding stale physical DR.. Oracle
  • Index Range Scan Oracle
  • Building Our Own Namespaces with “Create Context” Oracle
  • To find explain plan for a statement that occurred in past. Oracle
  • create trigger syntax Oracle
  • Physical Standby switchover with session active 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 (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • 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

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • How to remove blank lines using vi editor command Linux/Unix
  • fdisk -l explaination about Primary-Logical-Extended Partitions Linux/Unix
  • Very clear article about oracle dataguard Oracle
  • Optimizer SORT Operations Oracle
  • Good link for LIO in Oracle ( Logical IOs) Oracle
  • TNSNAMES entries details Oracle
  • perf_today.sql Oracle
  • findinfo.sql (SQL for getting CPU and Active session info) Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme