Oracle-L: RE: Limiting Concurrent Sessions Not Working

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Wed, 04 Jun 2003 10:30:21 -0800
Message-ID: <F001.005AA470.20030604103021@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 Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
Sent: Wednesday, June 04, 2003 2:10 PM
To: Multiple recipients of list ORACLE-L

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.

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='SAM'

4* and resource_name = 'SESSIONS_PER_USER'

SQL> / LIMIT

SQL> SQL> select profile

2 from dba_users

3 where username = 'SAM';

PROFILE

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

------------------------------

oracle

oracle SYS

12 rows selected.

SVRMGR> select osuser, username from v$session;

OSUSER USERNAME

------------------------------

oracle

oracle SYS

sam SAM

14 rows selected.

SVRMGR> Sam Bootsma

George Brown College

sbootsma_at_gbrownc.on.ca

416-415-5000 x4933

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Goulet, Dick
INET: DGoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Jun 04 2003 - 13:30:21 CDT