RE: Quota on TEMP

  • From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
  • To: Christian.Antognini@xxxxxxxxxxxx, kennaim@xxxxxxxxx
  • Date: Fri, 22 Sep 2006 08:43:11 -0700 (PDT)

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 

Other related posts: