If you are planning to use Oracle 9i, then use Resource Manager. From the Resource Manager, you can control how much max. time you want to give a query to execute. Resource Manager does also allow to control other resources like CPU, Memory etc. Setting up profile will not help.
If you are not using Oracle 9i, does this mean you are out of luck ? Well no, if you are using unix then you can use Unix Signal processing, if you are not using Unix, you can also use CYGWIN on Windows. If you are not sure how to do Signal processing, let me know, I have written a query automated tool which allows to users to run their query which uses the same feature. In my query tool, users drops their query in network in some particular folder, cronjobs looks for any new files in that folder and execute them and send user the result using email. I have also specified maximum time to execute query , so if query takes more than the my TIMEOUT limit, query are terminated and a email is sent to user saying that our query has been terminated.


Thanks
Sanjay