I have seen many times over the years where a production database will experience performance problems that are caused by Adhoc queries running and consuming too many resources.  These will often be from developers trying to debug a problem, or someone in Operations trying to get a custom report, or from some other source.  In extreme cases, this can be, in effect, a classic Denial of Service attack.

One way to solve the problem is to simply restrict access to the production database, particularly if you have a copy of production available for debugging/reporting purposes.  Alternatively, you can implement Database Profiles to restrict the amount of resources any one user is allowed to consume.  They are very easy to implement and can go a long way towards securing your database against an unintentional (or possibly intentional) Denial of Service attack. 

 In order for profiles to work, you must first set the init.ora parameter resource_limit to TRUE.  You can then create profiles and assign them to users.

SQL> alter system set resource_limit=TRUE;
SQL> create profile LIMITED_PROFILE limit sessions_per_user 2;
SQL> create user PROFILE_TEST identified by abcd profile LIMITED_PROFILE;
SQL> grant create session to profile_test;

Now I connect as PROFILE_TEST user, the first two connections are fine, but the third gets:

oracle@mdlinux ~]$ sqlplus profile_test/abcd
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Now that the user can only have two sessions at a given time, I will further crank down the screws by limiting the number of Logical Reads allowed per session.  In a real world scenario, you will need to come up with a reasonable limit but for testing I will set it to 10000.

SQL> alter profile LIMITED_PROFILE limit logical_reads_per_session 10000;

And when they run some crazy query, it will stop them in their tracks

SQL> select count(*) from dba_objects a, dba_objects b;
select count(*) from dba_objects a, dba_objects b
                     *
ERROR at line 1:
ORA-02394: exceeded session limit on IO usage, you are being logged off

To see what you can limit with a profile, run this query:

SQL> select unique RESOURCE_TYPE,RESOURCE_NAME from dba_profiles
SQL> order by 1,2 asc;

 RESOURCE RESOURCE_NAME
------- --------------------------------
KERNEL   COMPOSITE_LIMIT
KERNEL   CONNECT_TIME
KERNEL   CPU_PER_CALL
KERNEL   CPU_PER_SESSION
KERNEL   IDLE_TIME
KERNEL   LOGICAL_READS_PER_CALL
KERNEL   LOGICAL_READS_PER_SESSION
KERNEL   PRIVATE_SGA
KERNEL   SESSIONS_PER_USER
PASSWORD FAILED_LOGIN_ATTEMPTS
PASSWORD PASSWORD_GRACE_TIME
PASSWORD PASSWORD_LIFE_TIME
PASSWORD PASSWORD_LOCK_TIME
PASSWORD PASSWORD_REUSE_MAX
PASSWORD PASSWORD_REUSE_TIME
PASSWORD PASSWORD_VERIFY_FUNCTION
16 rows selected.

The PASSWORD resources are used to manage passwords.  The KERNEL resources are used to manage system resources.  For security reasons, you may want to set the IDLE_TIME limit.  This will prevent users from staying logged into the database for extended periods of time with no activity.  To see which users have been assigned profiles, you can query the dba_users view:

select username, profile from dba_users;

So, if you get a chance, check out Database Profiles.  They are quick, easy and can possibly help protect your database from an unpleasant situation.