Oracle OUTLN_PKG
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Contains functional interface for procedures and functions associated with management of stored outlines.

Deprecation Notice: Outlines are deprecated as of 12cR1: Instead use baselines
AUTHID DEFINER
Dependencies
DBMS_ASSERT DBMS_SQL OUTLINE
DBMS_OUTLN OL$ V$SQL
DBMS_OUTLN_LIB OL$HINTS  
Documented Yes
Exceptions
Error Code Reason
ORA-209649 Outline not found
First Available 8.1.5
Security Model Owned by SYS with EXECUTE granted to OUTLN and the DBA and EXECUTE_CATALOG_ROLE roles
Source {ORACLE_HOME}/rdbms/admin/dbmsol.sql
Synonym DBMS_OUTLN
Subprograms
 
CLEAR_USED
Clears the outline 'used' flag outln_pkg.clear_used(name IN VARCHAR2);
exec outln_pkg.clear_used('UW_OUTLINES');
 
CREATE_OUTLINE
Generate an outline from the shared cursor identified by hash value and child number outln_pkg.create_outline(
hash_value   IN NUMBER,
child_number IN NUMBER,
category     IN VARCHAR2 DEFAULT 'DEFAULT');
SELECT /* XPLAN_CURSOR */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT hash_value, sql_id, child_number
FROM gv$sql
WHERE sql_text LIKE '%XPLAN_CURSOR%';

SELECT name, owner, category, enabled, format
FROM dba_outlines;

exec outln_pkg.create_outline(291933262, 0);

SELECT name, owner, category, enabled, format
FROM dba_outlines;

SELECT name, hint
FROM dba_outline_hints;

DROP OUTLINE SYS_OUTLINE_08033010584585901;
 
DROP_BY_CAT
Drop outlines belonging to the named category outln_pkg.drop_by_cat(cat IN VARCHAR2);
exec outln_pkg.drop_by_cat('DEFAULT');
 
DROP_COLLISION
Drop outlines with an ol$.hintcount that does not match the number of hints in ol$hints outln_pkg.drop_collision;
exec outln_pkg.drop_collision;
 
DROP_COLLISION_EXPACT
Generates a string which serves as an invocation of the drop_collision stored procedure outln_pkg.drop_collision_expact RETURN VARCHAR2;
SELECT outln_pkg.drop_collision_expact
FROM dual;
 
DROP_EXTRAS
Drop hint tuples not accounted for by hintcount outln_pkg.drop_extras;
exec outln_pkg.drop_extras;
 
DROP_EXTRAS_EXPACT
Generates a string which serves as an invocation of the drop_extras stored procedure outln_pkg.drop_extras_expact RETURN VARCHAR2;
SELECT outln_pkg.drop_extras_expact
FROM dual;
 
DROP_UNREFD_HINTS
Drop hint tuples having no corresponding outline in the OL$ table outln_pkg.drop_unrefd_hints;
exec outln_pkg.drop_unrefd_hints;
 
DROP_UNREFD_HINTS_EXPACT
Generates a string which serves as an invocation of the drop_unrefd_hints stored procedure outln_pkg.drop_unrefd_hints_expact RETURN VARCHAR2;
SELECT outln_pkg.drop_unrefd_hints_expact
FROM dual;
 
DROP_UNUSED
Drop outlines that have never been applied outln_pkg.drop_unused;
exec outln_pkg.drop_unused;
 
EXACT_TEXT_SIGNATURES
Updates outline signatures to those that compute based on exact text matching outln_pkg.exact_text_signatures;
exec outln_pkg.exact_text_signatures;
 
REFRESH_OUTLINE_CACHE
Re-populates the cache with the  current set of outlines outln_pkg.refresh_outline_cache;
exec outln_pkg.refresh_outline_cache;
 
REFRESH_OUTLINE_CACHE_EXPACT
Generates a string which serves as an invocation of the refresh_outline_cache procedure outln_pkg.refresh_outline_cache_expact RETURN VARCHAR2;
SELECT outln_pkg.refresh_outline_cache_expact
FROM dual;
 
UPDATE_BY_CAT
Change the category of all outlines in one category to another category outln_pkg.update_by_cat(
old_cat IN VARCHAR2 DEFAULT 'DEFAULT',
new_cat IN VARCHAR2 DEFAULT 'DEFAULT');
exec outln_pkg.update_by_cat('DEFAULT', 'UW_OUTLINES');
 
UPDATE_SIGNATURES
Updates outline signatures to the current version's signature outln_pkg.update_signatures;
exec outln_pkg.update_signatures;

Related Topics
DBMS_SPM
Outlines
OUTLN_EDIT_PKG
Packages
Tuning
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved