Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • ORACLE_SID in sqlplus Oracle
  • The most important Tuning Notes Oracle
  • Implementing Listener Security Oracle
  • Wait Based Tuning Step by step with SQL statement Oracle
  • copying/removing directory with all its subdirectory Linux/Unix
  • Drop all SPM baselines for SQL handle Oracle
  • replace alphabets using sed Linux/Unix
  • Generating XML from SQLPLUS Oracle
  • ORA-00064: object is too large to allocate on this O/S during startup Oracle
  • sess_server.sql Oracle
  • Error Handling in Proc Oracle
  • Oracle Identifiers Oracle
  • Exadata Basics Oracle
  • Gather Stats manually using DBMS_STATS after disabling DBMS_SCHEDULER jobs as previous entry Oracle
  • replacing ^M character when passing files from Windows to Unix Linux/Unix

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

  • sid_wise_cursor.sql find open cursor basis on username or SID Oracle
  • Oracle10g – Using SQLAccess Advisor (DBMS_ADVISOR) with the Automatic Workload Repository Oracle
  • DBMS_UTILITY.ANALYZE_SCHEMA Oracle
  • tblwopk.sql /* Find Tables Without PK */ Oracle
  • Temporary Tablespace Information and restriction. Oracle
  • DBA_HIST_SQLSTAT contents Oracle

Leave a Reply Cancel reply

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

Categories

  • Ansible (0)
  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • MYSQL (5)
  • Oracle (393)
  • PHP/MYSQL/Wordpress (10)
  • POSTGRESQL (0)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (342)
  • SQL Server (6)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • Trace a SQL session from another session using ORADEBUG30-Sep-2025
  • SQL Server Vs Oracle Architecture difference25-Jul-2025
  • SQL Server: How to see historical transactions25-Jul-2025
  • SQL Server: How to see current transactions or requests25-Jul-2025
  • T-SQL Vs PL/SQL Syntax25-Jul-2025
  • Check SQL Server edition25-Jul-2025
  • Checking SQL Server Version25-Jul-2025
  • Oracle vs MYSQL Architecture differences (For DBAs)24-Jul-2025
  • V$INSTANCE of Oracle in MYSQL24-Jul-2025
  • Day to day MYSQL DBA operations (Compared with Oracle DBA)24-Jul-2025

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Vivek’s egrep commands to trace problem. (on linux x86-64) Linux/Unix
  • Find Multiple levels of object dependencies : depen.sql Oracle
  • Implementing Listener Security Oracle
  • True Session Wait Activity in Oracle 10g Verygood Oracle
  • How to find Unix server conguraiton including Server Model number and underlying disk sub-system ( On Solaris) Linux/Unix
  • How to find password change date for user Oracle
  • Jai Shree Ram Linux/Unix
  • sql_doing_fts.sql Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme