Home » RDBMS Server » Performance Tuning » Limiting the resources for specific User
Limiting the resources for specific User [message #242597] Mon, 04 June 2007 05:00 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I have a requirement in an OLTP.

If a user tries to perform a DML operaion, and if that operation results to more than 1000(for example)records, I want to ristrict that operation.

Is it possible by any means(other than having a trigger).

Brayan.
Re: Limiting the resources for specific User [message #242616 is a reply to message #242597] Mon, 04 June 2007 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not possible.

Regards
Michel
Re: Limiting the resources for specific User [message #243277 is a reply to message #242616] Wed, 06 June 2007 13:17 Go to previous messageGo to next message
dbaxchangedba
Messages: 26
Registered: November 2005
Junior Member
Quote:
If a user tries to perform a DML operaion, and if that operation results to more than 1000(for example)records, I want to ristrict that operation.

Is it possible by any means(other than having a trigger).


Oracle measures IO (queries and DML) in terms of blocks and so one way to limit query and DML operations would be to enforce it through profiles. More specifically with logical_reads_per_call and / or logical_reads_per_session. For your database translate the 1000 rows to blocks using table stats and you could use it to limit the operations.
Another way of performing limitation (and the preferred way) on DML would be to use oracle's resource manager's undo pool resource limit. You could assign your users to a resource group and have limitations imposed on how much undo the resource group can use.
You haven't mentioned your database version but if you happen to be on 10G then one feature to look at is server generated alerts.

Good luck....

http://www.dbaxchange.com
Re: Limiting the resources for specific User [message #243279 is a reply to message #243277] Wed, 06 June 2007 13:32 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're right, I understand "rows" too restrictively.
"blocks" or "logical reads" can be used but take care that profile kill the session and not only the current statement.

Resource manager is a good idea but difficult to use. Remember that resource manager comes into action only when you are short of resources never before.

Regards
Michel
Previous Topic: PEFSTAT dropped and recreated
Next Topic: Normal Forms
Goto Forum:
  


Current Time: Fri Dec 09 19:28:59 CST 2016

Total time taken to generate the page: 0.25868 seconds