Database Reference
In-Depth Information
With the
ALTER OUTLINE
statement or the
update_by_cat
procedure in the
dbms_outln
package, you're also able
to change the category of stored outlines. Whereas the former changes the category of a single stored outline, the
latter moves all stored outlines belonging to one category to another one. However, because of bug 5759631, it's not
possible with
ALTER OUTLINE
to change the category of a stored outline to
DEFAULT
(for all other categories, it's not a
problem). The following example shows not only what happens if you try to change it but also how to do it with the
update_by_cat
procedure:
SQL> ALTER OUTLINE outline_from_text CHANGE CATEGORY TO DEFAULT;
ALTER OUTLINE outline_from_text CHANGE CATEGORY TO DEFAULT
*
ERROR at line 1:
ORA-00931: missing identifier
SQL> execute dbms_outln.update_by_cat(oldcat => 'TEST', newcat => 'DEFAULT')
SQL> SELECT category
2 FROM user_outlines
3 WHERE name = 'OUTLINE_FROM_TEXT';
CATEGORY
--------
DEFAULT
Finally, with the
ALTER OUTLINE
statement, you can also regenerate a stored outline, which is like re-creating
it. Usually, you'll use this possibility if you want the query optimizer to generate a new set of hints. This could be
necessary if you have changed the access structures of the objects related to the stored outline:
ALTER OUTLINE outline_from_text REBUILD
Activating Stored Outlines
The query optimizer considers only the stored outlines that are active. To be active, a stored outline must meet two
conditions. The first is that the stored outlines must be enabled. This is the default when they're created. To enable
and disable a stored outline, you use the
ALTER OUTLINE
statement:
ALTER OUTLINE outline_from_text DISABLE
ALTER OUTLINE outline_from_text ENABLE
The second condition is that the category must be activated through the
use_stored_outlines
initialization
parameter at the session or system level. The initialization parameter takes as a value either
TRUE
,
FALSE
, or the name
of a category. If
TRUE
is specified, the category defaults to the value
DEFAULT
. The following SQL statement activates
the stored outlines belonging to the
test
category at the session level:
ALTER SESSION SET use_stored_outlines = test
Because the
use_stored_outlines
initialization parameter supports a single category, at a given time a session
can activate only a single category.
