RE: How to restrict the user connections
- From: "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Mar 2004 06:40:37 -0700
Hi Juan,
Perhaps those users have restricted session granted to them?
I did some further reading and according to the Oracle 9i Admin,
license_max_sessions has been deprecated. So even if this does solve the
original posters problem, it won't work in some future release.
-------------------------------
Alan Davey
Senior Programmer/Analyst
Oracle 9i OCA
w) 973-267-5990 x458
w) 212-295-3458
-----Original Message-----
From: Juan Cachito Reyes Pacheco [mailto:jreyes@xxxxxxxxxxxxxxxx]
Sent: 19 March 2004 08:13
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: How to restrict the user connections
Hi Alan, I think you are misundestanding something
If you understand this you will understand it,
why if I set to 2 license max sessions, I can get four connections?
SQL> alter system set license_max_sessions=2;
Sistema modificado.
SQL> SELECT USERNAME,PROGRAM FROM V$SESSION WHERE USERNAME IS NOT NULL;
USERNAME PROGRAM
------------------------------ --------------------------------------------
---------------
ADM
SQL> SELECT USERNAME,PROGRAM FROM V$SESSION WHERE USERNAME IS NOT NULL;
USERNAME PROGRAM
------------------------------ --------------------------------------------
---------------
ADM
CACHITO
SQL> SELECT USERNAME,PROGRAM FROM V$SESSION WHERE USERNAME IS NOT NULL;
USERNAME PROGRAM
------------------------------ --------------------------------------------
---------------
ADM
ADM
CACHITO
SQL> SELECT USERNAME,PROGRAM FROM V$SESSION WHERE USERNAME IS NOT NULL;
USERNAME PROGRAM
------------------------------ --------------------------------------------
---------------
ADM
FON
ADM
CACHITO
look and this
----- Original Message -----
From: "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, March 19, 2004 8:44 AM
Subject: RE: How to restrict the user connections
> Hi Juan,
>
> By "autonomus function" do you mean 'pragma autonomous_transaction'?
>
> If so, then it works.
>
> First dos prompt:
> connect as system
> SQL> alter system set license_max_sessions=2;
>
> System altered.
>
> --leave sql/plus session active.
>
> second dos prompt:
> connect as adavey
>
> --leave active.
>
> third dos prompt:
> connect as scott/tiger
> ERROR:
> ORA-00019: maximum number of session licenses exceeded
>
> Back to second dos prompt:
> SQL> ed
> Wrote file afiedt.buf
>
> 1 create or replace procedure test as
> 2 pragma autonomous_transaction;
> 3 begin
> 4 null;
> 5* end;
> SQL> /
>
> Procedure created.
>
> SQL> exec test;
>
> PL/SQL procedure successfully completed.
>
> I'm not familiar with developer, but from my experience with JDeveloper
and
> Toad, the various windows are all working via one user session.
>
> I believe we need more information from the original poster to see exactly
> what his requirements are.
>
>
> -------------------------------
> Alan Davey
> Senior Programmer/Analyst
> Oracle 9i OCA
> w) 973-267-5990 x458
> w) 212-295-3458
>
>
>
> -----Original Message-----
> From: Juan Cachito Reyes Pacheco [mailto:jreyes@xxxxxxxxxxxxxxxx]
> Sent: 18 March 2004 15:40
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: How to restrict the user connections
>
>
> Hi Alan, this is true if he runs a single session client,
> This is not true if he for example is using developer , then every window
> can be a
> distinct session.
> Every user can have more than a session. For example what happen if he
runs
> an
> autonomus function?
> :)
> ----- Original Message -----
> From: "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Thursday, March 18, 2004 2:44 PM
> Subject: RE: How to restrict the user connections
>
>
> > Hi Juan,
> >
> > license_max_sessions is the correct answer until Senthil answer's Tim's
> > question as to why it isn't good enough.
> >
> > You can go the trigger route, but unless there is a compelling reason to
> do
> > so (only allow specific users), it requires more time spent programming
> and
> > debugging (hmm, there are 20 current users and I now I can't log in as
my
> > dba account to perform/fix xxx).
> >
> > -----------------------------------------
> > Alan Davey
> > Senior Analyst/Project Leader
> > Oracle 9i OCA; 3/4 OCP
> > w) 973.267.5990 x458
> > w) 212.295.3458
> >
> >
> >
> > -----Original Message-----
> > From: Juan Cachito Reyes Pacheco [mailto:jreyes@xxxxxxxxxxxxxxxx]
> > Sent: Thursday, March 18, 2004 12:00 PM
> > To: oracle-l@xxxxxxxxxxxxx
> > Subject: Re: How to restrict the user connections
> >
> >
> > Hi Alan,
> >
> > LICENSE_MAX_SESSIONS specifies the maximum number of concurrent user
> > sessions allowed. When this limit is reached, only users with the
> RESTRICTED
> > SESSION privilege can connect to the database. Users who are not able to
> > connect receive a warning message indicating that the system has reached
> > maximum capacity.
> >
> > LICENSE_MAX_USERS specifies the maximum number of users you can create
in
> > the database. When you reach this limit, you cannot create more users.
You
> > can, however, increase the limit.
> >
> > I think none of both are a solution, because license max user limits the
> > number of user created, and license max sessions limits the maximum
number
> > of concurrent user sessions allowed.
> >
> > I think you 'll have to use a trigger.
> >
> > ----- Original Message -----
> > From: "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
> > To: <oracle-l@xxxxxxxxxxxxx>
> > Sent: Thursday, March 18, 2004 11:29 AM
> > Subject: RE: How to restrict the user connections
> >
> >
> > > Hi,
> > >
> > > I replied to your email too quickly.
> > >
> > > You can also set license_max_sessions dynamically via alter system.
So
> if
> > > you only need to set it for certain times, then that is another
option.
> > >
> > > HTH,
> > >
> > > -----------------------------------------
> > > Alan Davey
> > > Senior Analyst/Project Leader
> > > Oracle 9i OCA; 3/4 OCP
> > > w) 973.267.5990 x458
> > > w) 212.295.3458
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: Davey, Alan
> > > Sent: Thursday, March 18, 2004 9:40 AM
> > > To: 'oracle-l@xxxxxxxxxxxxx'
> > > Subject: RE: How to restrict the user connections
> > >
> > >
> > > Hi,
> > >
> > > Create a logon trigger that checks the number of sessions, and if more
> > than
> > > 20 exists, it will randomly select one and kill it. The idea being
that
> > > that user has probably been on long enough and needs to learn to share
> > > resources.
> > >
> > > Alternatively, you can set license_max_sessions in your init.ora file.
> > >
> > > -----------------------------------------
> > > Alan Davey
> > > Senior Analyst/Project Leader
> > > Oracle 9i OCA; 3/4 OCP
> > > w) 973.267.5990 x458
> > > w) 212.295.3458
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: Senthil Kumar [mailto:senthilkumard@xxxxxxxxxxxxxxx]
> > > Sent: Thursday, March 18, 2004 9:28 AM
> > > To: oracle-l@xxxxxxxxxxxxx
> > > Subject: How to restrict the user connections
> > >
> > >
> > > Hi List,
> > >
> > > How do I restrict the number of user connections. I want only 20 users
> to
> > > access the database on a particular time.
> > >
> > > How do I do this.
> > >
> > > TIA
> > > Senthil.
> > >
> > >
> > > ----------------------------------------------------------------
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > ----------------------------------------------------------------
> > > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at //www.freelists.org/archives/oracle-l/
> > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > -----------------------------------------------------------------
> > >
> > >
> > > "This information in this e-mail is intended solely for the addressee
> and
> > > may contain information which is confidential or privileged. Access
to
> > this
> > > e-mail by anyone else is unauthorized. If you are not the intended
> > > recipient, or believe that you have received this communication in
> error,
> > > please do not print, copy, retransmit, disseminate, or otherwise use
the
> > > information. Also, please notify the sender that you have received
this
> > > e-mail in error, and delete the copy you received."
> > >
> > >
> > > ----------------------------------------------------------------
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > ----------------------------------------------------------------
> > > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at //www.freelists.org/archives/oracle-l/
> > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > -----------------------------------------------------------------
> > >
> > >
> > > "This information in this e-mail is intended solely for the addressee
> and
> > > may contain information which is confidential or privileged. Access
to
> > this
> > > e-mail by anyone else is unauthorized. If you are not the intended
> > > recipient, or believe that you have received this communication in
> error,
> > > please do not print, copy, retransmit, disseminate, or otherwise use
the
> > > information. Also, please notify the sender that you have received
this
> > > e-mail in error, and delete the copy you received."
> > >
> > >
> > > ----------------------------------------------------------------
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > ----------------------------------------------------------------
> > > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at //www.freelists.org/archives/oracle-l/
> > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > -----------------------------------------------------------------
> >
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
> >
> > "This information in this e-mail is intended solely for the addressee
and
> > may contain information which is confidential or privileged. Access to
> this
> > e-mail by anyone else is unauthorized. If you are not the intended
> > recipient, or believe that you have received this communication in
error,
> > please do not print, copy, retransmit, disseminate, or otherwise use the
> > information. Also, please notify the sender that you have received this
> > e-mail in error, and delete the copy you received."
> >
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
> "This information in this e-mail is intended solely for the addressee and
> may contain information which is confidential or privileged. Access to
this
> e-mail by anyone else is unauthorized. If you are not the intended
> recipient, or believe that you have received this communication in error,
> please do not print, copy, retransmit, disseminate, or otherwise use the
> information. Also, please notify the sender that you have received this
> e-mail in error, and delete the copy you received."
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
"This information in this e-mail is intended solely for the addressee and
may contain information which is confidential or privileged. Access to this
e-mail by anyone else is unauthorized. If you are not the intended
recipient, or believe that you have received this communication in error,
please do not print, copy, retransmit, disseminate, or otherwise use the
information. Also, please notify the sender that you have received this
e-mail in error, and delete the copy you received."
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts: