Home » RDBMS Server » Server Administration » v$ views (SQL DEVELOPER, 1.5.1, Windows server 2003)
v$ views [message #517057] Fri, 22 July 2011 03:14 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,

Please provide answers to these questions

1) Which v$view gives information about the parameters (bind variables) passed to the called stored procedure

Regards,
Ritesh
Re: v$ views [message #517062 is a reply to message #517057] Fri, 22 July 2011 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
None.

Regards
Michel
Re: v$ views [message #517070 is a reply to message #517062] Fri, 22 July 2011 05:57 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

What does v$sql and v$sql_bind_capture views contain?


Regards,
Ritesh
Re: v$ views [message #517074 is a reply to message #517070] Fri, 22 July 2011 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bind variables of the current statements in SGA at parse time.

Regards
Michel
Re: v$ views [message #517089 is a reply to message #517074] Fri, 22 July 2011 07:13 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Below is a small demo proving that Oracle will only capture bind values used in the WHERE clause and all the other bind variables under SELECT list are ignored.



SQL> conn scott/tiger
Connected.
SQL> variable a number;
SQL> variable b number;
SQL> variable x number;
SQL> exec :a :=0; :b := 0;  := 7369;
PL/SQL procedure successfully completed.

SQL> select job, nvl(empno, :a), nvl(comm, :b) from emp where empno = :x;

JOB       NVL(EMPNO,:A) NVL(COMM,:B)
--------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
CLERK              7369            0

SQL> select sql_id, sql_text from v$sql where sql_text like 'select job, nvl(empno,%';

SQL_ID
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
3d1079n1gakc1
select job, nvl(empno, :a), nvl(comm, :b) from emp where empno = 

SQL> column name format a10
SQL> column VALUE_STRING format a20
SQL> select name, position, datatype_string, last_captured, value_string from v$sql_bind_capture
  2  where sql_id = '3d1079n1gakc1' order by position;

NAME         POSITION DATATYPE_STRING LAST_CAP VALUE_STRING
--------------------------------------------------------------------------------
:A                  1 NUMBER
:B                  2 NUMBER
:x                  3 NUMBER          03/06/08 7369




Regards,
Ritesh

[Updated on: Fri, 22 July 2011 07:17]

Report message to a moderator

Re: v$ views [message #517092 is a reply to message #517089] Fri, 22 July 2011 07:32 Go to previous message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, I forgot to mention that too.
Oracle stores only bind variables that are relevant to optimize the execution plan.

Regards
Michel
Previous Topic: Managing Tablespaces
Next Topic: Free space in standby database
Goto Forum:
  


Current Time: Fri Sep 19 15:00:40 CDT 2014

Total time taken to generate the page: 0.13350 seconds