Re: Quota on TEMP

  • From: "Ruth Gramolini" <ruth.gramolini@xxxxxxxxx>
  • To: john.kanagaraj@xxxxxxxxx
  • Date: Mon, 25 Sep 2006 14:06:17 -0400

I haven't been following this whole thread, but this may help.  It is a sql
script which allows you to see who is logged on with a generic application

set linesize 132
set pagesize 60
select substr(client_info,1,33) "Client",
       lpad(sid,3,' ') "SID",
       substr(status,1,1) "S",
       lockwait "Lockwait",
       substr(terminal,1,10) "Terminal",
       substr(to_char(logon_time,'hh24:mi'),1,7) "Logon Time",
       substr(username,1,8) "User",
       substr(osuser,1,8) "OSuser",
       substr(program,1,15) "Program"
from v$session
where type = 'USER'
order by 1,2

HTH, Ruth

On 9/22/06, John Kanagaraj <john.kanagaraj@xxxxxxxxx> wrote:


> 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

One CAN identify an Oracle Apps user if the right profile options are
set. But I do agree that identifying a BO user is an issue. The
nearest you can get to it what my BO Admin calls the "Governor log"
which when configured, lists all the users who executed some
particular report.

As for this problem, you need to observe what type of segment it is
from V$SORT_USAGE.SEGTYPE. If you see a HASH join that consume TEMP
quickly, you might to investigate if a BO "relation" as missed or
incorrectly defined. BO uses point-click and sometimes this occurs
when the "point" is not accompanied by a "click" :)

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is always inevitable; Discouragement is invariably optional

** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **

Ruth Gramolini

Other related posts: