select s.sid, s.username, t.SQL_TEXT from gv$sqltext t, gv$session s where s.sql_address=t.address order by s.sid, t.piece;
which will return a SID, Username, and 64 characters of the sql, the next row will show 64 characters and so on.
So I would like to be able to combine all t.SQL_TEXT where s.SID and s.USERNAME are the same as the previous row, as such:
SID USERNAME SQL_TEXT
1 USER1 select blah blah blah blah 64 characters long
1 USER1 second set of 64 character to 128
1 USER1 long query 64 more characters
1 USER1 still going 64 more characters more
1 USER1 select blah blah blah blah 64 characters longsecond set of 64 character to 128long query 64 more charactersstill going 64 more characters more
(Note: No spaces between SQL_TEXT strings. If they should be there, they are considered a character)
Thanks for the help!
That sounds like a job for the LISTAGG function, something like this:
SELECT s.sid, s.username
, LISTAGG (t.sql_text, NULL) WITHIN GROUP (ORDER BY t.piece) AS sql_text
FROM gv$sqltext t
, gv$session s
WHERE s.sql_address = t.address
GROUP BY s.sid, s.username
ORDER BY s.sid, s.username
LISTAGG returns a VARCHAR2, which can be up to 4000 characters long. If all of the strings to be combined are the full 64 characters long, then you can have as many as 62 of them in each group.
What is the version of your Oracle Database? Because the solution will differ based on the version. Please look into the FAQ Re: 4. How do I convert rows to columns? (Look for String Aggregation).
Said that, You need to understand the maximum size of VARCHAR2 in SQL is 4000 (Bytes/Characters). So if your SQL text length exceeds 4000 then you will have problem. So if you can explain the objective behind this requirement we could help you better.
Alternatively,using MODEL clause:
from gv$sqltext t, gv$session s
PARTITION BY (sid,username)
DIMENSION BY (piece)
MEASURES (cast(sql_text as varchar2(4000))as sqll)
RULES (sqll[any] order by piece desc = sqll[cv()]||''||sqll[cv()+1]
I was hoping there may be a version independent query as I have both 10g and 11g databases. The use for this would be to determine the sql based upon sid for a blocking session. Perhaps others know of a simpler way.
For a single SID query, using Frank's suggestion, it takes 8.44s on a relatively large 11g database. For Michael's; 9.64s.
It's a good idea to say what Oracle version(s) you're using up front, when you first post the question.
LISTAGG was new in Oracle 11.2, so it won't help you in Oracle 10.
The generic name for what you want is String Aggregation. This page:
show several ways to do string aggregation. LISTAGG is the only one that won't work in Oracle 10.
SInce you need to concatente the strings in a particular order, I recommend the SYS_CONNECT_BY_PATH technique, which was new in Oracle 9.1, and got easier to use in Oracle 10.1. Unfortunately, the oracle-base page cited above still shows the clunky, Oracle 9 way to do it. For the simpler (and more efficient) Oracle 10 form, see the comments page that accompanies that oracle-base page: