Oracle OUTLN_PKG
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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

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-2014 Daniel A. Morgan All Rights Reserved