Thanks for the question, Afzaal.
Asked: June 09, 2011 - 12:36 am UTC
Last updated: June 09, 2011 - 10:46 am UTC
Version: 9.2.0
Viewed 1000+ times
You Asked
hi tom,
There is a huge production table contains barcodes and their status, multiple users used to update the status by using application, as the barcodes moves from one area to another.
I am affraid that if any user got SQL*plus or PLSQL Developer or Toad and issue a update command with no where clause deliberately (or mistakenly). what will happen? Graphs showing online area stock vanishes. System flow will stop.
my Question is that can i apply a check on this table that no user can update more than 100 records at a time. which ever the software is used for this purpose.
and Tom said...
You have a really serious problem if you have users that have sqlplus/toad/whatever access and could "accidentally" type in a statement like that.
But - you want to look at the resource manager.
What you'll want to do is establish an UNDO_POOL directive for the resource manager:
http://docs.oracle.com/cd/E11882_01/server.112/e17120/undo005.htm#ADMIN11477 and have that policy be in effect. It will not restrict someone to exactly, precisely 100 rows - but it will allow you to realize your goal - to limit the size of transactions.
While you are doing that, you can read all about the resource manager (and profiles
http://docs.oracle.com/cd/E11882_01/server.112/e16508/cmntopc.htm#CNCPT1553 ) to limit the ability of uses to completely consume some resource.