I have a question on select count(), which may betray my lack of
database knowledge. Here goes.

I have java code driving a transaction which goes like this:

select count(*) from table

(Java code in the same transaction)
if count < 50 then ...

insert into table values(blahblah)
commit;

it seems that if many of these transactions are running concurrently,
count could exceed 50 unless oracle locks the table for inserts or
deletes.

Am I getting this wrong ? Are there better solutions ?

After exactly 51 inserts into a freshly created table, count will exceed 50.
:-)

I don`t see what you`re getting at.

Notes: Committing after each insert will slow down your application.
If you don`t commit, others won`t see the rows inserted. So
another process' "count" will not be affected, until you commit those
inserts.
You shouldn`t do a count iteratively in a loop, until 50 rows
have been inserted. Rather, count in your java code to keep trace of how
many you`ve inserted.

What are you trying to achieve?

Syltrem


"JP Belanger" <jpbelang (AT) hotmail (DOT) com> a écrit dans le message de
news:b399d6ce.0408131002.26efbf79 (AT) posting (DOT) google.com...

jpbelang (AT) hotmail (DOT) com (JP Belanger) wrote in message news:<b399d6ce.0408131002.26efbf79 (AT) posting (DOT) google.com>...

If you are trying to restrict number of rows to <= 50, there is something
wrong with database design. It'll help if you post what you are trying
to achieve (in simple words)...

afilonov (AT) yahoo (DOT) com (Alex Filonov) wrote in message news:<336da121.0408161150.2c0d78d9 (AT) posting (DOT) google.com>...
Are you saying I was unclear ?

I keep track of sessions in a database. Exceeding a fixed number of
sessions should not be allowed.

The reason I do not keep the cound in the Java application was that I
wanted to keep the application stateless, to allow for simple load
balancing. I thought the database could help me, but at the same
time, I think helping me, the database would sacrifice a lot of
performance.

jpbelang (AT) hotmail (DOT) com (JP Belanger) wrote in message news:<b399d6ce.0408170627.12849129 (AT) posting (DOT) google.com>...
You mean, each session is registering itself in some table? Maybe you
can use v$session virtual table?
Another way is to create a table with fixed number of rows. Would
create a lot of headache: you need to update a row when session
is registering itself and update again when it's de-registering
(and you need a monitor which would detect dead session and
de-register them). Another problem with this design is that session needs
to connect to database to try to register itself...

Maybe not. Depends on what kind of operations do you do against the
database.
BTW, do you really need to restrict the number of sessions?
And, is it possible to use restrictions built-in into Oracle, like
restrictions on number of sessions, processes or transactions?

"JP Belanger" <jpbelang (AT) hotmail (DOT) com> wrote

number of logons.
Very simple and reliable.
Jim

jpbelang (AT) hotmail (DOT) com (JP Belanger) wrote in message news:<b399d6ce.0408170627.12849129 (AT) posting (DOT) google.com>...

If you want a maximum number of session, just set the processes
parameter in init.ora appropiately.
If you want a maximum number of sessions per user, look up the CREATE
PROFILE command in the Oracle documentation.
You are now trying to hack yourself out, where the database can indeed
help you.

Sybrand Bakker
Senior Oracle DBA

afilonov (AT) yahoo (DOT) com (Alex Filonov) wrote in message news:<336da121.0408171322.516568f2 (AT) posting (DOT) google.com>...

I need to read up

Essentially had the same thoughts you had about this one.

Hehe. It was required for our application's licensing. Of course,
when you then ask what we should do with excess session, the answer,
as it very often is, was "let them go through anyways".

We may need to support many more sessions (100 000's). So I'll have
to read more on this.

Thanks for everybody's help.