This is what I do in my shop since they allow(not me) ad hoc queries... I modified the user id that run ad hoc queries to default to a TEMP tablespace which is rather small. :) They failed most of the time and I receive no complains. :)
Bing Wong Open Systems Database Administrator x25721 This e-mail may contain material that is confidential. Any review, reliance or distribution by others or forwarding without express permission is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies received. -----Original Message----- Sent: Tuesday, October 29, 2002 11:59 AM To: Multiple recipients of list ORACLE-L many shops prohibit ad-hoc queries in busy OLTP databases and many of those have datawarehouses where ad-hoc is allowed. IMHO that's the only way prevent ad-hoc queries from causing problems in your OLTP environment. SQL> select * form users where clue > 0; no rows selected -----Original Message----- Sent: Tuesday, October 29, 2002 1:29 PM To: Multiple recipients of list ORACLE-L I just wanted to ping the list to see what other people have done to control or constrain adhoc query users??? We have a group that is struggling with the adhoc query piece that's in production. Some of the users end up firing off insane queries. The group is trying to find a way to limit the amount of records queried for, so that a wild query doesn't hose the database. Appending a ROWNUM to the WHERE clause is one idea. Using USER PROFILEs is another. Any other thoughts?? Dare I ask...this custom app also runs on SQL Server, so SQL Server ideas would also be appreciated. Many thanks!!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
