stored outlines - cursor sharing

From: Oradba Linux <techiey2k3_at_comcast.net>
Date: Sun, 9 Jan 2005 03:06:22 -0500
Message-ID: <i-Sdnb9oNtJ_eH3cRVn-ig@comcast.com>

Oracle 9204 - RHES3.0

Cursor_sharing = force disables creation of stored outlines. Refer to the metalink documentation bug Bug.2976681. This is a change from Oracle 8i behavior. But the following example works by setting cursor_sharing =force and the stored_outline is getting used.

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> alter session set create_stored_outlines=JOBS   2 ;

Session altered.

SQL> r
  1 select department_name , street_address , city , state_province , country_id
  2 from locations , departments
  3 where departments.location_id = locations.location_id   4* and departments.department_id=40

DEPARTMENT_NAME                STREET_ADDRESS


------------------------------ ----------------------------------------

CITY                           STATE_PROVINCE            CO


------------------------------ ------------------------- --

Human Resources                8204 Arthur St
London                                                   UK

SQL> alter session set create_stored_outlines=false;

Session altered.

SQL> set long 4000
SQL> select name , sql_text , category , used from user_outlines where category='JOBS'
  2 /

NAME

---

SQL_TEXT

---

---

CATEGORY                       USED


------------------------------ ---------

SYS_OUTLINE_050109024153029
select department_name , street_address , city , state_province , country_id from locations , departments
where departments.location_id = locations.location_id and departments.department_id=:"SYS_B_0"

JOBS                           UNUSED

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> alter session set use_stored_outlines=JOBS   2 /

Session altered.

SQL> select department_name , street_address , city , state_province , country_id
  2 from locations , departments
  3 where departments.location_id=locations.location_id   4 and departments.department_id=50
  5 /

DEPARTMENT_NAME                STREET_ADDRESS


------------------------------ ----------------------------------------

CITY                           STATE_PROVINCE            CO


------------------------------ ------------------------- --

Shipping                       2011 Interiors Blvd
South San Francisco            California                US

SQL> select name , sql_text , category , used from user_outlines where category='JOBS';

NAME                           SQL_TEXT
CATEGORY                       USED


------------------------------ ---------------------------------------------

----------------------------------- ------------------------------ ---------

SYS_OUTLINE_050109024153029    select department_name , street_address ,
city , state_province , country_id     JOBS                           USED
                               from locations , departments
                               where departments.location_id =
locations.location_id
                               and departments.department_id=:"SYS_B_0"

Received on Sun Jan 09 2005 - 02:06:22 CST