Solved

Find sql text and user by pid and sid

Posted on 2006-07-13
8
2,295 Views
Last Modified: 2008-01-09

 Hi Guys,

Does anyone has a script of how to find most offensive SQL statements by sid and pid?


Thanks,
Mia
0
Comment
Question by:milanushka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
8 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 25 total points
ID: 17100575
To get text by sid:

select sql_text
from v$session a,
      v$sqltext b
where a.sql_hash_value = b.hash_value and
     a.sid = <sid>
order by piece;

To get text by pid:

select sql_text
from v$process a,
     v$session b,
     v$sql_text c
where a.addr = b.paddr and
     b.sql_hash_value = c.hash_value and
    a.spid = <pid>
order by piece;
0
 

Author Comment

by:milanushka
ID: 17100609
Thank you very much.
I have a scrip that shows most offencive sql. Is there a way to modify that script to show sid or pid

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT *
FROM   (SELECT Substr(a.sql_text,1,100) sql_text,
               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
               a.buffer_gets,
               a.disk_reads,
               a.executions,
               a.sorts,
               a.address
        FROM   v$sqlarea a
        ORDER BY 2 DESC)
WHERE  rownum <= 100;

SET PAGESIZE 14


Thanks again

Mia
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17101211
SELECT *
FROM   (SELECT Substr(a.sql_text,1,100) sql_text,
               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
               a.buffer_gets,
               a.disk_reads,
               a.executions,
               a.sorts,
               a.address,b.sid,c.spid
        FROM   v$sqlarea a ,V$SESSION b, V$PROCESS WHERE a.hash_value =  b.hash_value and a.addr = b.paddr
        ORDER BY 2 DESC)
WHERE  rownum <= 100;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 25 total points
ID: 17101233
Use this
SELECT *
FROM   (SELECT Substr(a.sql_text,1,100) sql_text,
               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
               a.buffer_gets,
               a.disk_reads,
               a.executions,
               a.sorts,
               a.address,b.sid,c.spid
        FROM   v$sqlarea a ,V$SESSION b, V$PROCESS c WHERE a.hash_value =  b.sql_hash_value and c.addr = b.paddr
        ORDER BY 2 DESC)
WHERE  rownum <= 100
/
0
 

Author Comment

by:milanushka
ID: 17101242
Thank you very much!
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17112331
Please remember  to accept answer
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question