Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Please Help - user details of the sql statement being run not being found

RE: Please Help - user details of the sql statement being run not being found

From: Martin Kendall <m.kendall_at_ntlworld.com>
Date: Thu, 29 Nov 2001 05:04:28 -0800
Message-ID: <F001.003D04B2.20011129043528@fatcity.com>

Viraj,

Do please read the manual on Outer Join theory.

Also be consistent with your Column Alias usage.

Martin

-----Original Message-----
Luthra
Sent: 29 November 2001 06:35
To: Multiple recipients of list ORACLE-L not being found

Okay I am answering my own problem, but for the :-

1st Query, I modified the query to :-

SELECT     r.username, b.sql_text, executions , serial#
        r.user_id,machine,terminal,program,a.username,osuser, sid,
        process,ROUND(((disk_reads * 100) / DECODE(executions, 0, 1,
         executions)))
FROM       sys.dba_users r, v$session a, v$sqlarea , v$sqltext b
WHERE      parsing_user_id=user_id

AND b.address=a.sql_address(+)
AND a.sql_hash_value = b.hash_value
and parsing_user_id=user_id
ORDER BY executions desc
/

Now I am getting the required fields, like serial#, r.user_id,machine,terminal,program,a.username,osuser, sid

Is the above query correct? Please help and respond

For the 2nd query, I modified the query to :-

 SELECT     b.sql_text, executions ,serial#,machine,terminal,
            program,a.username,osuser, sid, process,
           FLOOR(buffer_gets / DECODE(executions, 0, 1, executions))
FROM       v$session a, v$sqlarea , v$sqltext b
where a.sql_address = b.address
AND a.sql_hash_value = b.hash_value
ORDER BY executions desc

This also provides me the required output. But I want to ask you whether the above is correct? Please respond.

Thanks and Regards,

Raja

On Wed, 28 Nov 2001 20:15:22
 Viraj Luthra wrote:
>Hello all,
>
>I am running the following query :-
>
>SELECT a.username, sql_text, executions ,
> a.user_id,machine,terminal,program,
> a.username,osuser, sid, process,
> ROUND(((disk_reads * 100) / DECODE(executions, 0, 1,
> executions)))
>FROM sys.dba_users a, v$session, v$sqlarea
>WHERE parsing_user_id=user_id AND address=sql_address(+)
>ORDER BY executions desc
>
>
>But when I run this query, I dont get the username, user_id, machine,
terminal, program, osuser, sid, process of all records.
>
>Why is this, that is why I get these fields populated in some records and
others I dont get?
>
>Also I am running another query:-
>
>SELECT a.username,osuser, sid, process, serial#,length(sql_text),
> sql_text,
> executions,
> FLOOR(buffer_gets / DECODE(executions, 0, 1, executions))
> FROM sys.dba_users a, v$session, v$sqlarea
> WHERE parsing_user_id=user_id AND address=sql_address(+)
> ORDER BY executions desc
>
>Here also I dont get the username, osuser, sid, process, serial# populated
all the time, Why?
>
>Please help.
>
>Thanks and Regards,
>
>Raja
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Viraj Luthra
> INET: viraj999_at_lycos.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).
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Viraj Luthra
  INET: viraj999_at_lycos.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Martin Kendall
  INET: m.kendall_at_ntlworld.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).
Received on Thu Nov 29 2001 - 07:04:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US