Limiting Concurrent Sessions Not Working

From: Sam Bootsma <sbootsma_at_gbrownc.on.ca>
Date: Thu, 05 Jun 2003 03:51:15 -0700
Message-ID: <F001.005AA3E6.20030604101023@fatcity.com>

Oracle7 Server Release 7.3.4.5.0 (yes, I know, old); AIX 4.3.3

Hello All,

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.

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.

Thanks for any suggestions!

Sam.

<span

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

SQL> create profile sam limit sessions_per_user 1;

Profile created.

SQL> create user sam identified by sam;

User created.

SQL> alter user sam profile sam;

User altered.

SQL> grant create session to sam;

Grant succeeded.

SQL>

SQL> l

 1 select limit

 2 from dba_profiles

 3 where profile=''

 4* and resource_name = 'SESSIONS_PER_USER'

SQL> /

LIMIT

<span style='font-size:10.0pt;

font-family:Arial'>----------------------------------------

1

SQL>

SQL> select profile

 2 from dba_users

 3 where username = 'SAM';

PROFILE

<span style='font-size:10.0pt;

font-family:Arial'>------------------------------

SAM

SQL>

SVRMGR> shutdown normal

Database closed.

Database dismounted.

ORACLE instance shut down.

SVRMGR> connect internal

Connected to an idle instance.

SVRMGR> startup

ORACLE instance started.

Total System Global Area 34122296 bytes

Fixed

Size

38992 bytes

Variable

Size

31797736 bytes

Database
Buffers
2252800 bytes

Redo

Buffers

32768 bytes

Database mounted.

Database opened.

SVRMGR> select osuser, username

 2> from v$session;

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

oracle

SYS

12 rows selected.

SVRMGR> select osuser, username from v$session;

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

oracle

SYS

<span style='font-size:10.0pt;

font-family:Arial'>sam             SAM

<span style='font-size:10.0pt;

font-family:Arial'>sam             SAM

14 rows selected.

SVRMGR>

Sam Bootsma

George Brown College

[EMAIL PROTECTED]

416-415-5000 x4933

 Received on Thu Jun 05 2003 - 05:51:15 CDT