| Limiting the resources for specific User [message #242597] |
Mon, 04 June 2007 05:00  |
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 #243277 is a reply to message #242616] |
Wed, 06 June 2007 13:17  |
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 |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
