RE: Limiting Concurrent Sessions Not Working

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Thu, 05 Jun 2003 04:08:20 -0700
Message-ID: <F001.005AA464.20030604103020@fatcity.com>

Do you
have "resource_limit = true" in init.ora?  If you don't it defaults to FALSE & the profile has no effect.
 
Dick GouletSenior Oracle DBAOracle Certified 8i DBA

<FONT face=Tahoma

size=2>-----Original Message-----From: Sam Bootsma [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 04, 2003 2:10 PMTo: Multiple recipients of list ORACLE-LSubject: Limiting Concurrent Sessions Not Working

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Oracle7 Server Release 7.3.4.5.0 (yes, I know, old); AIX 4.3.3 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Hello All,
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; 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. 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; 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.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Thanks for any suggestions!
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Sam.
<DIV

style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: medium none; PADDING-LEFT: 0cm; PADDING-BOTTOM: 1pt; BORDER-LEFT: medium none; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 3pt dotted">
<P class=MsoNormal

style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: medium none; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: medium none; PADDING-TOP: 0cm; BORDER-BOTTOM: medium none"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SQL> create profile sam limit sessions_per_user 1;
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Profile created.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SQL> create user sam identified by sam;
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">User created.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SQL> alter user sam profile sam;
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">User altered.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SQL> grant create session to sam;
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Grant succeeded.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SQL>
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SQL> l
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">  1  select limit
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">  2  from dba_profiles
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">  3  where profile=''
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">  4* and resource_name = 'SESSIONS_PER_USER'
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SQL> /
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">LIMIT
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">----------------------------------------

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">1
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SQL>
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SQL> select profile
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">  2  from dba_users
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">  3  where username = 'SAM';
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">PROFILE
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">------------------------------

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SAM
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SQL>
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SVRMGR> shutdown normal
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Database closed.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Database dismounted.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">ORACLE instance shut down.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SVRMGR> connect internal
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Connected to an idle instance.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SVRMGR> startup
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">ORACLE instance started.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Total System Global Area      34122296 bytes
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Fixed

Size                       

38992 bytes
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Variable

Size                 

31797736 bytes
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Database Buffers              
2252800 bytes
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Redo

Buffers                     

32768 bytes
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Database mounted.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Database opened.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SVRMGR> select osuser, username
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">     2> from v$session;  
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">OSUSER         

USERNAME                      

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">---------------

---

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                    

    
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle         

SYS                           

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">12 rows selected.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SVRMGR> select osuser, username from v$session;
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">OSUSER         

USERNAME                      

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">---------------

---

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                 
                       

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                        

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle                                      

  
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">oracle         

SYS                           

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">sam       

     SAM                           

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">sam      

      SAM                           

<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">14 rows selected.
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SVRMGR>
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Sam Bootsma
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">George<FONT face=Arial size=2> <FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Brown<FONT face=Arial size=2> <FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">College
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><A href="mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]
<SPAN

style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">416-415-5000 x4933
<SPAN

style="FONT-SIZE: 12pt">  Received on Thu Jun 05 2003 - 06:08:20 CDT