Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 25, 2003 - 2:40 pm UTC

Last updated: October 03, 2018 - 3:50 am UTC

Version: 9

Viewed 100K+ times! This question is

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

Comments

silly design

Mikito Harakiri, December 01, 2003 - 12:01 pm UTC

They have v$sql.sql_fulltext clob in 10g. Why wasn't it there from the very beginning is a mystery to me.

Can you explain v$sql, v$sqlarea, v$sqltext, V$DB_OBJECT_CACHE

pjp, June 18, 2004 - 4:11 am UTC

Hi Tom,

I am trying to understand Oracle Tunning concepts. While going thru conecepts of tunning liabrary cache I have came across following views.

Will you pl explain in simple terms the conecpt of these views, when to use these views and also pl answer specific questions on these views

1. v$sql :=> Oracle Docuementation => V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered.It is saying sql_text is contain first thousand characters of the SQL text for the current cursor.

Q : What is the meaning of child of origninal SQL text entered ? What is reason this view is having only first 1000 characters ? Why in documentation it is saying ....for current cursor it should say for sql statement. What does mean cursor here ?

2. V$SQLAREA => Oracle Documentation => V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

Q : Is there any difference from v$SQL other than having group by clause ?

3. V$SQLTEXT

4. v$db_object_cache => if i do select name, type from this view it is giving following result

GRANT SELECT ON "SMP NOT LOADED
GRANT SELECT ON "SMP CURSOR
GRANT INSERT ON "V5A NOT LOADED
GRANT INSERT ON "V5A CURSOR
GRANT SELECT ON "VDX NOT LOADED
GRANT SELECT ON "VDX CURSOR
GRANT INSERT ON "VDX NOT LOADED
GRANT INSERT ON "VDX CURSOR
GRANT INSERT ON "TDT NOT LOADED
GRANT INSERT ON "TDT CURSOR
GRANT DELETE ON "TFC NOT LOADED
GRANT DELETE ON "TFC CURSOR

Q : why in name it is not showing full sql statement ? What does it mean not loaded ?

Once again thanks for explaining use and conecepts of these views in simple terms so novice like me can understand.

regards & thanks
pjp



Tom Kyte
June 18, 2004 - 10:43 am UTC

1) v$sql shows a row per statement in the shared pool -- even if the statement text is duplicated.


consider, two users -- A and B. both own a table T. Both log in and issue:

select * from t;

that is the same query TEXT for both -- however, they are two entirely different queries. That query will appear twice in v$sql -- once will be "A"'s version of it and once will be "B"s. V$SQL_AREA aggregates up by sql_text, so in there - there would be one version.

the duplicates are known as "child cursors".

the first 1000 is "just because that is the way it is". queries can be very long (megabytes in size in fact). v$sqltext_with_newlines contains the entire query if you need it.


2) v$sqlarea is just an aggregation of v$sql. it adds things up.

I recommend never using it, it is slower and hides information. v$sql is what you want to use.

3) they truncate ddl like that to hide passwords. think "alter user foobar identified by barfoo" and so on. You get a flavor of the statement but not the entire thing.

Small Query ....still ....

pjp, June 21, 2004 - 7:54 am UTC

Hi Tom,

Thanks for your clear and logical answer as usual. Most of things are now clear to me. Just small query still from my earlier question ..

v$sql :=> Oracle Docuementation => V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered.It is saying sql_text is contain first thousand characters of the SQL text for the current cursor.

Q : What is the meaning of cursor here ? Can you explain term sql statement and cursor in simple way ?

Most of the times I am getting confused with these terminology in documentation. ( I think I know that is the meaning of cursor in pl/sql )

Thanks for answering such foolish questions.

regards
pjp

Tom Kyte
June 21, 2004 - 8:42 am UTC

q) they abuse that term, ignore it. it is just a sql statement. a cursor is a programatic thing. it is just what it is -- a shared sql statement in the shared pool. you could create a cursor in your application that will point to it.

object relational addendum

Jean-Pierre Dubois, November 29, 2005 - 8:28 am UTC

First please excuse in advance my english, it has nothing to do with professionalism but with my knowledge ;-)

Actually you can combine the v$sqltype with you String-Concatenation answer (see </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2196162600402 <code>

I just changed a little bit the function to avoid the ora-06502 problem, when varchar are bigger than 4000:

CREATE OR REPLACE function to_string (nt_in in ntt_varchar2
,delimiter_in in varchar2 default ',')
return varchar2
is
v_idx pls_integer;
v_str varchar2(32767);
v_dlm varchar2(10);

begin
v_dlm := delimiter_in;
v_idx := nt_in.first;

while v_idx is not null loop
v_str := v_str || v_dlm || nt_in(v_idx);
v_idx := nt_in.next(v_idx);
end loop;

return case greatest(4000, length(v_str))
when 4000 then v_str
else '##partial##'||substr(v_str,1,3989)
end;

end to_string;
/

After that you just have to start a query like:

select *
from (
select address
, to_string(CAST (COLLECT (sql_text) AS ntt_varchar2),
null
) AS sql_text
from (
select address
, piece
, sql_text
from v$sqltext
order by address, piece
)
group by address
)
where upper(sql_text) like '%SELECT%PARTNER%'
or sql_text like '##partial##%';


Best Regards
Jean-Pierre



Bind variable value from v$sqltext

A reader, October 21, 2008 - 8:39 am UTC

Hi Tom,

Thanks for sharing your knowledge about Oracle databases and its functionalities.

I had a quick question for you. I have identified few SQLs in one of our production databases using below SQL

select a.sid , a.username, s.sql_text
from v$session a , v$sqltext s
where a.sql_address =s.address
and a.sql_hash_value= s.hash_value
order by a.username , a.sid , s.piece;

As an output of above query I got SQL TEXT but the problem is bind variable in SQL_TEXT column with abouve query output. Is there any way to get value of bind variables used with sql_text column for above query without tracing the sessions?

Basically I want to capture all SQL statements using above query and then want to run explain plan for <statement from SQL_TEXT column from above query) to see execution plans for the statements....


Cheers

Tom Kyte
October 21, 2008 - 3:54 pm UTC

... Basically I want to capture all SQL statements using above query and then want
to run explain plan for <statement from SQL_TEXT column from above query) to
see execution plans for the statements....
...

why not just query v$sql_plan?

Bind variable value from v$sqltext

A reader, October 21, 2008 - 8:42 am UTC

Hi Tom,

Sorry for not mentioning database version in previous question. I am on 9.2.0.4.

Cheers,

alternative to get last run SQL QUERY

ram, March 05, 2014 - 4:08 pm UTC

Hi tom,
I have a question is there an alternative to get the last run sql text using a function instead of getting the sql_text from V$ Views....
like in case of exception we have SQLCODE and SQLERRM functions to trap the error code and message and use them to display those messages in the output or insert into a logging table.
Is there a similar function for getting the last run query when no exception is raised. I have to submit the last run SQL if the validation fails to let the client know this particular SQL query failed because of bad data which in turn the client can check from his side by simply running the query.

for example

BEGIN

SELECT COUNT(a.RCRD_ID) INTO invalid_count FROM TABLEG a WHERE a.RCRD_ID NOT LIKE 'T3' or a.RCRD_ID is null;
IF invalid_count > 0 THEN
INSERT INTO VALIDATION_ERRORS_LOG (TABLE_NAME, COLUMN_NAME, NO_OF_ERROR_RECORDS, SEGMENT_ID,COMMENTS_AND_SQLCODE, VALIDATION_DATE)
VALUES ('TABLEG', 'RCRD_ID', invalid_count,'T003',SQLCODE, current_timestamp);
ELSE null;
END IF;
end;
in place SQLCODE in the above insert statement I want to use a particular function if there is one in oracle to display the last run SQL TEXT. I dont want to hardcode the sql by copying and pasting as the subsequent SQLs are getting fairly larger. Also I cannot raise excpetions because the validation has to run against all the tables first. there nearly 100 million records to validate so Im doing the count to get invalid records.

sql bind and sql text

Abdullah, May 24, 2015 - 7:25 pm UTC

Dear Tom,
i have confused about sql bind and sql text, the relationship between these two.

so may you classify me both of them

i wish to answer my question if you can.

Thanks.

ORA-01489

Gerrit Haase, October 02, 2018 - 12:04 pm UTC

With Oracle 12.1.0.2 ... living in a 64-bit world, SQLPlus has too many limits... getting this error:

ERROR at line 65:
ORA-01489: result of string concatenation is too long

SQLDeveloper ... the same.
Connor McDonald
October 03, 2018 - 3:50 am UTC

Using v$sqltext is sortta obsolete nowadays.

You can get the entire sql text from V$SQL or V$SQLSTATS

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here