How to implement a low resource privileged user on Oracle 10g using Resource Manager?
This is fairly easy.


create resource consumer groups
create a resource manager plan using the created groups
somehow arrange that sessions are mapped to a resource consumer group
in the resource directives specify if a request should be estimated first or not
make sure users have switch privileges to the required resource consumer groups


Point three is a bit vague, there are many ways to have a session map itself to a resource consumer group. One is by user definition, an other is by client machine name, yet an other is by module ....

If estimates are in place and the estimates guesses that more resources could be needed than allowed, the action is greeted with an error message explaining so.

It does need some careful design and mutual agreements between the admins and the users. Oracle Resource Management is very powerful. 

Check the docs. Managing Resources with Oracle Database Resource Manager