Oracle OUTLN_PKG
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose Contains functional interface for procedures and functions associated with management of stored outlines.

Deprecation Notice: Outlines are deprecated as of 12.1: Instead use baselines
AUTHID CURRENT_USER
Dependencies
DBMS_OUTLN DBMS_OUTLN_LIB OUTLINE
DBMS_OUTLN_INTERNAL    
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 the DBA, EXECUTE_CATALOG_ROLE and OUTLN 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
Built-in Functions
Built-in Packages
DBMS_SPM
OUTLN_PKG_INTERNAL
Outlines
Tuning
What's New In 19c
What's New In 20c-21c

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