Oracle | OUTLN

Oracle OUTLN_PKG Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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: Packages and Types Reference

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	DROP_EXTRAS	EXACT_TEXT_SIGNATURES
CREATE_OUTLINE	DROP_EXTRAS_EXPACT	REFRESH_OUTLINE_CACHE
DROP_BY_CAT	DROP_UNREFD_HINTS	REFRESH_OUTLINE_CACHE_EXPACT
DROP_COLLISION	DROP_UNREFD_HINTS_EXPACT	UPDATE_BY_CAT
DROP_COLLISION_EXPACT	DROP_UNUSED	UPDATE_SIGNATURES

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;