Oracle 10g - How to prevent one user from hogging all resources?
There is a built-in utility called Resource Manager exactly for that purpose:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#i1010776