RE: Limiting Concurrent Sessions Not Working

From: Sam Bootsma <sbootsma_at_gbrownc.on.ca>
Date: Thu, 05 Jun 2003 05:23:20 -0700
Message-ID: <F001.005AA600.20030604112951@fatcity.com>

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>Bingo!

<span style='font-size:

10.0pt;font-family:Arial;color:navy'> 

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>Thanks Dick, it is working now.

<span style='font-size:

10.0pt;font-family:Arial;color:navy'> 

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>Sam

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>GBC

<span style='font-size:

10.0pt;font-family:Arial;color:navy'> 

<span

style='font-size:10.0pt;font-family:Tahoma'>-----Original Message----- From: Goulet, Dick
[mailto:[EMAIL PROTECTED]
Sent: June 4, 2003<font
size=2 face=Tahoma> 2:30 PM<font
size=2 face=Tahoma>
To: Multiple recipients of list
ORACLE-L
Subject: RE: Limiting Concurrent
Sessions Not Working

<font size=3

face="Times New Roman"> 

<font size=2 color=blue

face=Arial>Do you
have "resource_limit = true" in init.ora?  If you don't it defaults to FALSE & the profile has no effect.

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt'>Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

<p class=MsoNormal style='margin-right:0cm;margin-bottom:12.0pt;margin-left:
36.0pt'><span style='font-size:10.0pt;font-family: Tahoma'>-----Original Message-----
From: Sam Bootsma
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 04, 2003
2:10 PM
To: Multiple recipients of list
ORACLE-L
Subject: Limiting Concurrent
Sessions Not Working

<span

style='font-size:10.0pt;font-family:Arial'>Oracle7 Server Release 7.3.4.5.0 (yes, I know, old); AIX 4.3.3 

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>Hello All,

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>I am trying to limit concurrent sessions on my database.  The Oracle documentation says I can do it.  However, when I follow the steps (create profile with sessions per user set to one, then assign Oracle user to this profile) the user is not stopped from exceeding the number of concurrent sessions.  I tested this by connecting using Sql Plus.  I even tried bouncing the database, to see if that makes a difference, but to no avail. 

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>Any ideas what is happening?  Am I missing something?  Is this a problem/bug with Oracle 7.3.4?  The output below shows the steps I followed to create the profile and user.  It also shows multiple connections to the database after bouncing the instance.

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>Thanks for any suggestions!

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>Sam.

<font size=3

face="Times New Roman"> 

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>SQL> create profile sam limit sessions_per_user 1;

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>Profile created.

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>SQL> create user sam identified by sam;

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>User created.

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>SQL> alter user sam profile sam;

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>User altered.

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>SQL> grant create session to sam;

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>Grant succeeded.

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>SQL>

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>SQL> l

<span

style='font-size:10.0pt;font-family:Arial'>  1  select limit

<span

style='font-size:10.0pt;font-family:Arial'>  2  from dba_profiles

<span

style='font-size:10.0pt;font-family:Arial'>  3  where profile=''

<span

style='font-size:10.0pt;font-family:Arial'>  4* and resource_name = 'SESSIONS_PER_USER'
<span

style='font-size:10.0pt;font-family:Arial'>SQL> /

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>LIMIT

<span

style='font-size:10.0pt;font-family:Arial'>----------------------------------------

<span

style='font-size:10.0pt;font-family:Arial'>1

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>SQL>

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>SQL> select profile

<span

style='font-size:10.0pt;font-family:Arial'>  2  from dba_users

<span

style='font-size:10.0pt;font-family:Arial'>  3  where username = 'SAM';
<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>PROFILE

<span

style='font-size:10.0pt;font-family:Arial'>------------------------------

<span

style='font-size:10.0pt;font-family:Arial'>SAM

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>SQL>

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>SVRMGR> shutdown normal

<span

style='font-size:10.0pt;font-family:Arial'>Database closed.

<span

style='font-size:10.0pt;font-family:Arial'>Database dismounted.

<span

style='font-size:10.0pt;font-family:Arial'>ORACLE instance shut down.

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>SVRMGR> connect internal

<span

style='font-size:10.0pt;font-family:Arial'>Connected to an idle instance.

<span

style='font-size:10.0pt;font-family:Arial'>SVRMGR> startup

<span

style='font-size:10.0pt;font-family:Arial'>ORACLE instance started.

<span

style='font-size:10.0pt;font-family:Arial'>Total System Global Area      34122296 bytes

<span

style='font-size:10.0pt;font-family:Arial'>Fixed

Size                      

38992 bytes

<span

style='font-size:10.0pt;font-family:Arial'>Variable

Size                

31797736 bytes

<span

style='font-size:10.0pt;font-family:Arial'>Database Buffers              
2252800 bytes

<span

style='font-size:10.0pt;font-family:Arial'>Redo

Buffers                    

32768 bytes

<span

style='font-size:10.0pt;font-family:Arial'>Database mounted.

<span

style='font-size:10.0pt;font-family:Arial'>Database opened.

<span

style='font-size:10.0pt;font-family:Arial'>SVRMGR> select osuser, username

<span

style='font-size:10.0pt;font-family:Arial'>     2> from v$session;  

<span

style='font-size:10.0pt;font-family:Arial'>OSUSER         

USERNAME                     

<span

style='font-size:10.0pt;font-family:Arial'>---------------

---

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                   

    

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle         

SYS                          

<span

style='font-size:10.0pt;font-family:Arial'>12 rows selected.

<span

style='font-size:10.0pt;font-family:Arial'>SVRMGR> select osuser, username from v$session;

<span

style='font-size:10.0pt;font-family:Arial'>OSUSER         

USERNAME                     

<span

style='font-size:10.0pt;font-family:Arial'>---------------

---

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                
                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                       

<span

style='font-size:10.0pt;font-family:Arial'>oracle                                     

  

<span

style='font-size:10.0pt;font-family:Arial'>oracle         

SYS                          

<span

style='font-size:10.0pt;font-family:Arial'>sam       

     SAM                          

<span

style='font-size:10.0pt;font-family:Arial'>sam      

      SAM                          

<span

style='font-size:10.0pt;font-family:Arial'>14 rows selected.

<span

style='font-size:10.0pt;font-family:Arial'>SVRMGR>

<font size=3

face="Times New Roman"> 

<span

style='font-size:10.0pt;font-family:Arial'>Sam

<font size=2 color=navy

face=Arial>GBC

<font size=3

face="Times New Roman">  Received on Thu Jun 05 2003 - 07:23:20 CDT