You Asked
Hi,
When querying v$sqltext the sql statement is broken up into "pieces". I want to write a query that will show all of the text as one statement and not broken up by pieces. So, insead of showing:
piece sql_text
----- --------
0 select user
1 from dual
0 select sysdate
1 from dual
I want my query to return:
sql_statement
--------------
select user from dual
select sysdate from dual
Thanks.
and Tom said...
varchar's are limited to 4000 bytes, so this solution will be limited to those. you could possibly rewrite using clobs if you like:
ops$tkyte@ORA920> create or replace function sql_query( p_address in v$sqltext.address%type,
2 p_hash_value in v$sqltext.hash_value%type )
3 return varchar2
4 as
5 l_text long;
6 begin
7 for x in ( select sql_text from v$sqltext where address = p_address and hash_value = p_hash_value order by piece )
8 loop
9 l_text := l_text || x.sql_text;
10 exit when length(l_text) > 4000;
11 end loop;
12 return substr( l_text, 1, 4000 );
13 end;
14 /
Function created.
ops$tkyte@ORA920> select a.*, sql_query(address,hash_value) sql_query
2 from (
3 select address, hash_value, count(*) cnt
4 from v$sqltext
5 group by address, hash_value
6 having count(*) > 1
7 order by 3 desc
8 ) a
9 where rownum <= 10
10 /
ADDRESS HASH_VALUE CNT
-------- ---------- ----------
SQL_QUERY
--------------------------------------------------------------------------------
5CA92E6C 4027377770 26
select '"'||name||'" '||decode(type#,1,decode(charsetform,2,'NVARCHAR2('||decode
(bitand(property,8388608),8388608,decode(spare3,0,1,spare3)||')','BYTECOUNT=>'||
decode(length,0,1,length)||')'),'VARCHAR2('||decode(bitand(property,8388608),838
8608,decode(spare3,0,1,spare3)||' char)',decode(length,0,1,length)||' byte)')),2
,decode(scale,null,decode(precision#,null,'NUMBER','FLOAT('||precision#||')'),'N
UMBER('||decode(precision#,null,'38', precision#)||','||scale||')'),8,'LONG',9,'
VARCHAR('||(decode(length,0,1,length))||')',12,'DATE',23,'RAW('||length||')',24,
'LONG RAW',69,'ROWID',96,decode(charsetform,2,'NCHAR('||decode(bitand(property,8
388608),8388608,decode(spare3,0,1,spare3)||')','BYTECOUNT=>'||decode(length,0,1,
length)||')'),'CHAR('||decode(bitand(property,8388608),8388608,decode(spare3,0,1
,spare3)||' char)',decode(length,0,1,length)||' byte)')),97,'VARCHAR('||(decode(
length,0,1,length))|| ')',105,'MLSLABEL',106,'MLSLABEL',112,decode(charsetform,2
,'NCLOB','CLOB'),113,'BLOB',114,'BFILE',115,decode(charsetform,2,'NCFILE','CFILE
'),178,'TIME('||scale||')',179,'TIME('||scale||') WITH TIME ZONE',180,'TIMESTAMP
('||scale||')',181,'TIMESTAMP('||scale||') WITH TIME ZONE',231,'TIMESTAMP('||sca
le||') WITH LOCAL TIME ZONE',182,'INTERVAL YEAR('||precision#||') TO MONTH',183,
'INTERVAL DAY('||precision#||') TO SECOND('||scale||')',208,'UROWID('||spare1||'
)','UNDEFINED')||decode(null$,0,'',' NOT NULL')||decode(bitand(property, 32), 0,
',', ' HIDDEN,'),name, type#, intcol#, null$, bitand(property, 32) from col$ wh
ere obj#=:1 and (bitand(property,32) = 0 or bitand(property, 4194304) <> 0) orde
r by col#
...............
Alternatively, if you assume some reasonable maximum for the number of pieces (eg: 62.5 * 64 = 4000, so 62 is "reasonable")
ops$tkyte@ORA920> select *
2 from (
3 select address, hash_value, count(*) cnt,
4 max(decode(piece,0,sql_text))||
5 max(decode(piece,1,sql_text))||
6 max(decode(piece,2,sql_text))||
7 max(decode(piece,3,sql_text))||
8 max(decode(piece,4,sql_text))||
9 max(decode(piece,5,sql_text))||
10 max(decode(piece,6,sql_text))||
11 max(decode(piece,7,sql_text))||
12 max(decode(piece,8,sql_text))||
13 max(decode(piece,9,sql_text))||
14 max(decode(piece,10,sql_text))||
15 max(decode(piece,11,sql_text))||
16 max(decode(piece,12,sql_text))||
17 max(decode(piece,13,sql_text))||
18 max(decode(piece,14,sql_text))||
19 max(decode(piece,15,sql_text))||
20 max(decode(piece,16,sql_text))||
21 max(decode(piece,17,sql_text))||
22 max(decode(piece,18,sql_text))||
23 max(decode(piece,19,sql_text))||
24 max(decode(piece,20,sql_text))||
25 max(decode(piece,21,sql_text))||
26 max(decode(piece,22,sql_text))||
27 max(decode(piece,23,sql_text))||
28 max(decode(piece,24,sql_text))||
29 max(decode(piece,25,sql_text))||
30 max(decode(piece,26,sql_text))||
31 max(decode(piece,27,sql_text))||
32 max(decode(piece,28,sql_text))||
33 max(decode(piece,29,sql_text))||
34 max(decode(piece,30,sql_text))||
35 max(decode(piece,31,sql_text))||
36 max(decode(piece,32,sql_text))||
37 max(decode(piece,33,sql_text))||
38 max(decode(piece,34,sql_text))||
39 max(decode(piece,35,sql_text))||
40 max(decode(piece,36,sql_text))||
41 max(decode(piece,37,sql_text))||
42 max(decode(piece,38,sql_text))||
43 max(decode(piece,39,sql_text))||
44 max(decode(piece,40,sql_text))||
45 max(decode(piece,41,sql_text))||
46 max(decode(piece,42,sql_text))||
47 max(decode(piece,43,sql_text))||
48 max(decode(piece,44,sql_text))||
49 max(decode(piece,45,sql_text))||
50 max(decode(piece,46,sql_text))||
51 max(decode(piece,47,sql_text))||
52 max(decode(piece,48,sql_text))||
53 max(decode(piece,49,sql_text))||
54 max(decode(piece,50,sql_text))||
55 max(decode(piece,51,sql_text))||
56 max(decode(piece,52,sql_text))||
57 max(decode(piece,53,sql_text))||
58 max(decode(piece,54,sql_text))||
59 max(decode(piece,55,sql_text))||
60 max(decode(piece,56,sql_text))||
61 max(decode(piece,57,sql_text))||
62 max(decode(piece,58,sql_text))||
63 max(decode(piece,59,sql_text))||
64 max(decode(piece,60,sql_text))||
65 max(decode(piece,61,sql_text))||
66 max(decode(piece,62,sql_text)) sql_text
67 from v$sqltext
68 group by address, hash_value
69 order by 3 desc
70 )
71 where rownum = 1
72 /
ADDRESS HASH_VALUE CNT
-------- ---------- ----------
SQL_TEXT
-------------------------------------------------------
5EE4FB8C 2093455374 42
select * from ( select address, hash_value, count(*)
cnt, max(decode(piece,0,sql_text))||
max(decode(piece,1,sql_text))||
max(decode(piece,2,sql_text))||
max(decode(piece,3,sql_text))||
max(decode(piece,4,sql_text))||
max(decode(piece,5,sql_text))||
max(decode(piece,6,sql_text))||
max(decode(piece,7,sql_text))||
max(decode(piece,8,sql_text))||
max(decode(piece,9,sql_text))||
max(decode(piece,10,sql_text))||
max(decode(piece,11,sql_text))||
max(decode(piece,12,sql_text))||
max(decode(piece,13,sql_text))||
max(decode(piece,14,sql_text))||
max(decode(piece,15,sql_text))||
max(decode(piece,16,sql_text))||
max(decode(piece,17,sql_text))||
max(decode(piece,18,sql_text))||
max(decode(piece,19,sql_text))||
max(decode(piece,20,sql_text))||
max(decode(piece,21,sql_text))||
max(decode(piece,22,sql_text))||
max(decode(piece,23,sql_text))||
max(decode(piece,24,sql_text))||
max(decode(piece,25,sql_text))||
max(decode(piece,26,sql_text))||
max(decode(piece,27,sql_text))||
max(decode(piece,28,sql_text))||
max(decode(piece,29,sql_text))||
max(decode(piece,30,sql_text))||
max(decode(piece,31,sql_text))||
max(decode(piece,32,sql_text))||
max(decode(piece,33,sql_text))||
max(decode(piece,34,sql_text))||
max(decode(piece,35,sql_text))||
max(decode(piece,36,sql_text))||
max(decode(piece,37,sql_text))||
max(decode(piece,38,sql_text))||
max(decode(piece,39,sql_text))||
max(decode(piece,40,sql_text))||
max(decode(piece,41,sql_text))||
max(decode(piece,42,sql_text))||
max(decode(piece,43,sql_text))||
max(decode(piece,44,sql_text))||
max(decode(piece,45,sql_text))||
max(decode(piece,46,sql_text))||
max(decode(piece,47,sql_text))||
max(decode(piece,48,sql_text))||
max(decode(piece,49,sql_text))||
max(decode(piece,50,sql_text))||
max(decode(piece,51,sql_text))||
max(decode(piece,52,sql_text))||
max(decode(piece,53,sql_text))||
max(decode(piece,54,sql_text))||
max(decode(piece,55,sql_text))||
max(decode(piece,56,sql_text))||
max(decode(piece,57,sql_text))||
max(decode(piece,58,sql_text))||
max(decode(piece,59,sql_text))||
max(decode(piece,60,sql_text))||
max(decode(piece,61,sql_text))||
max(decode(piece,62,sql_text)) sql_text from
v$sqltext group by address, hash_value order by 3
desc ) where rownum = 1
1 row selected.
ops$tkyte@ORA920>
Rating
(9 ratings)
Is this answer out of date? If it is, please let us know via a Comment