Bu Blogda Ara

17 Kasım 2011 Perşembe

Output of sql is different about cursor_sharing between exact and force

DB Version: Oracle 11.2.0.2.0
OS: Linux
Problem:
If cursor_sharing parameter is FORCE and there are function based index, you would get wrong output your SQL. For example:

SQL>CREATE INDEX owner.index_name ("sdate" DESC);
SQL>@script.sql


 Process   sdate
--------- -----------
  9630571 


SQL>Drop INDEX owner.index_name;
SQL>@script.sql


 Process   sdate
--------- -----------
  9630571 11/2/2011


Solutions:
--You can set  cursor_sharing to EXACT. But you should test in test db about all problem. This parameter is so effective in database.
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing string FORCE


SQL> alter system set cursor_sharing=EXACT scope=both sid='*';

-- You couldn't set cursor_sharing to EXACT. download and apply patch for Bug 10259620 and test

Hiç yorum yok:

Yorum Gönderme