Re: Quota on TEMP

From: Ruth Gramolini <ruth.gramolini_at_gmail.com>
Date: Mon, 25 Sep 2006 14:06:17 -0400
Message-ID: <e3f4f26a0609251106j3eaa0fceod9903982000a63d0@mail.gmail.com>

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 login.

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_at_gmail.com> wrote:
>
> Deepak,
>
> > 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 **
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Ruth Gramolini
ruth.gramolini_at_gmail.com

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 25 2006 - 13:06:17 CDT