Coskan’s Approach to Oracle

March 2, 2007

How to get session info ?

Filed under: How To — coskan @ 12:54 pm

To get the info of your session you can use the SYS_CONTEXT function with USERENV namespace.

usage of SYS_CONTEXT  is like ;

SYS_CONTEXT(‘namespace’, ‘parameter’ [, length ])

The context namespace must already have been created, and the associated parameter and its value must also have been set using the DBMS_SESSION.set_context procedure. The parameter is not case sensitive. (Refer to the manual for context creation)

There are examples from the usage of sys_context with userenv namespace

code listing 4

references: Oracle® Database SQL Quick Reference10g Release 1 (10.1)

How to find value of bind variables used by SQLs

Filed under: CBO, How To — coskan @ 10:02 am

There is one good useful feature in Oracle 10g to catch the value of bind variables without a trace.

This feature is the view V$SQL_BIND_CAPTURE. This view gives you information about the values binded to a specific sql_id with the capture time info. When we use this view with V$SESSION we can capture the values that are binded to the working sqls. This view is populated only when you set STATISTICS_LEVEL parameter to ALL/TYPICAL . Bind capturing is disabled when the STATISTICS_LEVEL initialization parameter is set to BASIC.

Here is an example of the usage;
col sid format a4
col username format a5
col sql_hash_value format 99999999
col sqlid format a14
col sql_child_number format 9
col name format a4
col value_string format a8
col last_captured format a9

select s.sid,
s.username,
–sq.sql_text,
s.sql_hash_value,
s.sql_id,
s.sql_child_number,
spc.name,
spc.value_string,
last_captured
from v$sql_bind_capture spc, v$session s,v$sql sq
where s.sql_hash_value = spc.hash_value
and s.sql_address = spc.address
and sq.sql_id=s.sql_id
and spc.was_captured=’YES’
and s.type<>’BACKGROUND’
and s.status=’ACTIVE’;

SID USERN SQL_HASH_VALUE SQL_ID SQL_CHILD_NUMBER NAME VALUE_ST LAST_CAPT
———- —– ————– ————- —————- —- ——– ———
########## OKUR ######### 4s6c5w6zr0dub 0 :1 20070301 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :2 20070301 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :3 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :4 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :5 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :6 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :7 NULL 02-MAR-07

resources used:

Watching SQL Execute on Oracle – Part II James Koopmann

Oracle Documentation for 10g R1

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers