Hi
I can see many sql queries doing full table scan in the database, as there is a parameter cursor_sharing='SIMILAR' , Queries are
using bind variables :"SYS_B_2" and :"SYS_B_3 hence it is very difficult to analyse a complex queries with 10 to 15 Bind variable.
Let me know if we can trace the current value of bind variable running in the database?
Can we generate the SQL trace for already running sql queries in the database?

do a 10046 trace - you will get the bind vairables in the trace file

further to dave's suggestion;you can do so like

- find sid, serial# of the session running the query
select sid,serial# from v$session where username = 'user running the query';

- exec sys.dbms_system.set_bool_param_in_session(sid,serial#,'timed_statistics',true);
- exec sys.dbms_system.set_int_param_in_session(sid,serial#,'max_dump_file_size',900000000000);
- exec sys.dbms_system.set_ev(sid,serial#,10046,12,'');


to switchoff tracing do like

- exec sys.dbms_system.set_ev(sid,serial#,10046,0,'')

hth...

Here are note on how to read the cursor states from a 10046 trace:

http://www.dba-oracle.com/t_10046_trace_fi...rse_execute.htm

http://www.dba-oracle.com/t_10046_loading_...zing_traces.htm