• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1056
  • Last Modified:

Change cursor_sharing dynamically using sid and serial# of v$session

Hi Experts,
I want to change cursor_sharing=FORCE in my session level, I dont want to use 'alter session set cursor_sharing=FORCE'. Is there any alternate for that something like dbms_system.set_int_param_in_session ?
Devinder Singh Virdi
Devinder Singh Virdi
  • 3
1 Solution
alternate is

alternative alter session set create_stored_outlines=TRUE

goto this site for examples:

I suggest delete with refund.

stored outlines aren't really equivalent to cursor sharing.
while not at all equivalent, stored outlines can be complimented by cursor sharing

they apply to specific queries so two queries with different literals would not be recognized for the purposes of outlines.

cursor sharing would allow more queries to use a given outline because the literals would be replaced with binds, allowing them to match an outlined query with binds.

However, to answer the virdi_ds's  question...

the boolean equivalent for dbms_system is...

  dbms_system.set_bool_param_in_session(:sid, :serial, 'cursor_sharing', TRUE);

Note, dbms_system has no permissions by default and is undocumented/unsupported by Oracle.
Now that I've posted an answer, I rescind my suggestion of delete with refund.  :)

Hopefully my suggestion will be found acceptable.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now