-
Value of bind variables ?
I have this huge query running in production that is using up all the temp tablespace. I put the complete query together using the v$sqltext view and it is using bind valirables. How do I get the values of these bind variables ? v$sql_cursor does not have that sql address anymore, so I am not able to retrive the data from v$sql_bind_data. And this is on Oracle 9.2.0.5. Any suggestions appreciated.
Here is the query I ran to figure out what is using the temp tablespace :
Code:
SELECT a.TABLESPACE, b.SID, b.status, b.serial#, c.address, SUM(a.blocks) ,c.sql_text, c.piece FROM
v$sort_usage a,v$session b,v$sqltext c, v$sql_cursor sqlc , v$sql_bind_data sqlbd
WHERE a.session_addr = b.saddr
AND b.sql_address = c.address
AND c.address = sqlc.parent_handle AND
sqlbd.cursor_num = sqlc.curno
GROUP BY a.TABLESPACE, b.SID, b.status, b.serial#, c.address, c.sql_text, c.piece
ORDER BY a.TABLESPACE,b.SID,b.serial#,c.address, c.piece;
and here is the query I was trying to run to get the values for the bind variables :
Code:
SELECT SQL.sql_text line, sqlbd.cursor_num, sqlbd.position, sqlbd.datatype, 'xx' || sqlbd.VALUE || 'xx'
FROM v$sql SQL, v$sql_cursor sqlc , v$sql_bind_data sqlbd
WHERE SQL.address = '3E601000' AND
SQL.address=sqlc.parent_handle AND
sqlbd.cursor_num = sqlc.curno
Thanks.
-
trace the query and get the binds from the trace file - you cannot get them from the database
-
This query was executed about 5 hours before via the application. So I do not know who or what the user did to be able to recreate the user activity to get the trace file.
Is there any other way to get this data for a past session ?
Thanks.
-
Maybe you could infer it from the result set, if the query completed.
-
you can only get bind values for your own session
-
Originally Posted by davey23uk
you can only get bind values for your own session
Not true.
You can enable level 12 extended trace for another session also.
If the query is completed, then you can't get bind variable values.
How ever, if the query is still running, it is easy enable level 12 trace.
Tamil
-
i mean from the database itself
-
Originally Posted by tamilselvan
...enable level 12 trace.
... or more succinctly, level 4 of course.
-
How do I implement bind variables on the SQL statements implemented on ASP pages?If anyone can give me an example of how to do that it would be helpful
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|