Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Quota on TEMP

RE: Quota on TEMP

From: Deepak Sharma <>
Date: Fri, 22 Sep 2006 08:43:11 -0700 (PDT)
Message-ID: <>

Creating separate Temp for select user is good option (and ignore the ORA-1652s for that tablespace) and as I said earlier I do not disagree. Also, as suggested here (and also by one of my colleagues) have a constantly running monitoring job that kills those rogue queries.

I need to go into more details of the issue to explain why I need something similar to quota on Temp.

I know for sure that such rogue SQL(s) are issued by Business Objects user. In the database all we see is a generic Id (say, BUSOBJ_USER). This is similar to Oracle Apps, where we do not know the real User who connected thru the BusObj app. So, unless we know who executed the SQL, there's no way for us to tune the SQL or train the user. But identifying the BO user is a different topic altogether. We have already looked into passing the UserId using dbms_application_info, set_client etc., but since it's a 'canned' app, there's no way to set the client_identifier in the app, so that us DBAs can see the set value in v$sesion. As told to us by the BO developers, since they do not use any API, this setting of client_identifier cannot be done.

Any thoughts around that?


> Ken
> > I don't see the benefit of this feature; if the
> user needs to run
> > the query for a valid business purpose and the
> query is written
> > well (i.e. not missing a join etc.) then the temp
> space should be
> > created large enough to accommodate it. If the
> user doesn't need
> > to run the queries or doesn't know how to write
> well formed queries
> > then he shouldn't be allowed to write them to
> begin with, especially
> > in production and a request made to the reporting
> staff for the
> > data.
> In an ideal world I'll agree with you, i.e. such a
> feature is not
> necessary.
> Unfortunately, at least here in Europe, we don't
> live in an ideal
> world... Therefore sometimes such a feature could be
> very useful! Like
> hints or at least other 42 features are...
> An example: Last week I was by a customer where lot
> of reports are based
> on SQL scripts which are started through SQL*Plus.
> The scripts are
> changed on a daily/weekly basis and directly
> "tested" on production!
> Bleark, I agree. I let you imagine the problems the
> DBAs over there have
> to face...

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
Received on Fri Sep 22 2006 - 10:43:11 CDT

Original text of this message