Re: Oracle Tuning Question

From: Jared Still <jkstill_at_bcbso.com>
Date: Wed, 14 Jun 2000 07:48:12 -0700 (PDT)
Message-Id: <10528.109225@fatcity.com>

Try this:

select

   s.username,
   s.sid,
   s.serial#,
   p.pid ppid,
   s.status,
   s.machine,
   s.osuser,

   substr(s.program,1,20) client_program,    s.process client_process,
   substr(p.program,1,20) server_program,    to_char(p.spid) spid,
   to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time,

• idle time
• days added to hours --( trunc(LAST_CALL_ET/86400) * 24 ) || ':' ||
• days separately substr('0'||trunc(LAST_CALL_ET/86400),-2,2) || ':' ||
• hours substr('0'||trunc(mod(LAST_CALL_ET,86400)/3600),-2,2) || ':' ||
• minutes substr('0'||trunc(mod(mod(LAST_CALL_ET,86400),3600)/60),-2,2) || ':' || --seconds substr('0'||mod(mod(mod(LAST_CALL_ET,86400),3600),60),-2,2) idle_time from v$session s, v$process p where s.username is not null
• use outer join to show sniped sessions in
• v$session that don't have an OS process and p.addr(+) = s.paddr
• uncomment to see only your own session --and userenv('SESSIONID') = s.audsid order by username, sid /

Jared

On Wed, 14 Jun 2000, Ali Murtaza wrote:

> Hi Listers,
> In v$sqlarea view, i want to know from which machine or terminal a
> particular sql statement was last executed. Basically with sql_text column i
> want to know the machine or terminal from where that sql statement was
> executed. Can somebody please guide me as to which columns to join with
> which views to get the above required info.
> Thanks,
> Murtaza
> --
> Author: Ali Murtaza
> INET: MURTAZA.ALI_at_ICL.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address Received on Wed Jun 14 2000 - 09:48:12 CDT