Rem Rem $Header: catol.sql 05-may-2008.16:03:20 yzhu Exp $ Rem Rem catol.sql Rem Rem Copyright (c) 1998, 2008, Oracle. All rights reserved. Rem Rem NAME Rem catol.sql - outline views and synonyms Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem yzhu 02/04/08 - add MIGRATED column in *_outlines views Rem rburns 08/11/06 - add grants Rem ddas 01/07/05 - #(4052436) modify *_OUTLINE_HINTS views Rem svivian 06/13/03 - add node_name to ol$nodes Rem svivian 06/10/03 - handle local hint format Rem svivian 08/20/02 - remove symbolics that don't work Rem svivian 08/19/02 - add private outline tables to system Rem desinha 04/29/02 - #2303866: change user => userenv('SCHEMAID') Rem gviswana 05/24/01 - CREATE AND REPLACE SYNONYM Rem svivian 08/22/00 - add signature to views Rem pejustus 06/22/98 - add phase column to hint views Rem svivian 04/16/98 - Created Rem -- -- NAME: ora$grant_sys_select -- -- DESCRIPTION: -- System only procedure that grants select on outln tables -- to select_catalog_role without requiring a connect to -- the OUTLN schema. -- -- PARAMETERS -- -- USAGE NOTES: -- To be used during database startup only -- create or replace procedure outln.ora$grant_sys_select as begin EXECUTE IMMEDIATE 'GRANT SELECT ON OUTLN.OL$ TO SELECT_CATALOG_ROLE'; EXECUTE IMMEDIATE 'GRANT SELECT ON OUTLN.OL$HINTS TO SELECT_CATALOG_ROLE'; EXECUTE IMMEDIATE 'GRANT SELECT ON OUTLN.OL$NODES TO SELECT_CATALOG_ROLE'; EXECUTE IMMEDIATE 'GRANT SELECT ON OUTLN.OL$ TO SYS WITH GRANT OPTION'; EXECUTE IMMEDIATE 'GRANT SELECT ON OUTLN.OL$HINTS TO SYS WITH GRANT OPTION'; EXECUTE IMMEDIATE 'GRANT SELECT ON OUTLN.OL$NODES TO SYS WITH GRANT OPTION'; end; / begin outln.ora$grant_sys_select; end; / create or replace view USER_OUTLINES (NAME, CATEGORY, USED, TIMESTAMP, VERSION, SQL_TEXT, SIGNATURE, COMPATIBLE, ENABLED, FORMAT, MIGRATED) as select ol_name, category, decode(bitand(flags, 1), 0, 'UNUSED', 1, 'USED'), timestamp, version, sql_text, signature, decode(bitand(flags, 2), 0, 'COMPATIBLE', 2, 'INCOMPATIBLE'), decode(bitand(flags, 4), 0, 'ENABLED', 4, 'DISABLED'), decode(bitand(flags, 8), 0, 'NORMAL', 8, 'LOCAL'), decode(bitand(flags, 16), 0, 'NOT-MIGRATED', 16, 'MIGRATED') from outln.ol$, sys.user$ u where creator = u.name and u.user# = USERENV('SCHEMAID') / comment on table USER_OUTLINES is 'Stored outlines owned by the user' / comment on column USER_OUTLINES.NAME is 'Name of the outline' / comment on column USER_OUTLINES.CATEGORY is 'Category to which the outline belongs' / comment on column USER_OUTLINES.USED is 'Flag indicating whether the outline has ever been used' / comment on column USER_OUTLINES.TIMESTAMP is 'Timestamp at which the outline was created' / comment on column USER_OUTLINES.VERSION is 'Oracle Version that created the outline' / comment on column USER_OUTLINES.SQL_TEXT is 'SQL text of the query' / comment on column USER_OUTLINES.SIGNATURE is 'Signature uniquely identifying the outline SQL text' / comment on column USER_OUTLINES.COMPATIBLE is 'Flag indicating whether the outline hints were compatible across migration' / comment on column USER_OUTLINES.ENABLED is 'Flag indicating whether the outline is enabled' / comment on column USER_OUTLINES.FORMAT is 'Flag indicating what hint format is used' / comment on column USER_OUTLINES.MIGRATED is 'Flag indicating whether the outline has been migrated to SQL plan baseline' / create or replace public synonym USER_OUTLINES for USER_OUTLINES / grant select on USER_OUTLINES to PUBLIC with grant option / create or replace public synonym ALL_OUTLINES for USER_OUTLINES / grant select on ALL_OUTLINES to PUBLIC with grant option / create or replace view DBA_OUTLINES (NAME, OWNER, CATEGORY, USED, TIMESTAMP, VERSION, SQL_TEXT, SIGNATURE, COMPATIBLE, ENABLED, FORMAT, MIGRATED) as select ol_name, creator, category, decode(bitand(flags, 1), 0, 'UNUSED', 1, 'USED'), timestamp, version, sql_text, signature, decode(bitand(flags, 2), 0, 'COMPATIBLE', 2, 'INCOMPATIBLE'), decode(bitand(flags, 4), 0, 'ENABLED', 4, 'DISABLED'), decode(bitand(flags, 8), 0, 'NORMAL', 8, 'LOCAL'), decode(bitand(flags, 16), 0, 'NOT-MIGRATED', 16, 'MIGRATED') from outln.ol$ / comment on table DBA_OUTLINES is 'Stored outlines' / comment on column DBA_OUTLINES.NAME is 'Name of the outline' / comment on column DBA_OUTLINES.OWNER is 'User who created the outline' / comment on column DBA_OUTLINES.CATEGORY is 'Category to which the outline belongs' / comment on column DBA_OUTLINES.USED is 'Flag indicating whether the outline has ever been used' / comment on column DBA_OUTLINES.TIMESTAMP is 'Timestamp at which the outline was created' / comment on column DBA_OUTLINES.VERSION is 'Oracle Version that created the outline' / comment on column DBA_OUTLINES.SQL_TEXT is 'SQL text of the query' / comment on column DBA_OUTLINES.SIGNATURE is 'Signature uniquely identifying the outline SQL text' / comment on column DBA_OUTLINES.COMPATIBLE is 'Flag indicating whether the outline hints were compatible across migration' / comment on column DBA_OUTLINES.ENABLED is 'Flag indicating whether the outline is enabled' / comment on column DBA_OUTLINES.FORMAT is 'Flag indicating what hint format is used' / comment on column DBA_OUTLINES.MIGRATED is 'Flag indicating whether the outline has been migrated to SQL plan baseline' / create or replace public synonym DBA_OUTLINES for DBA_OUTLINES / grant select on DBA_OUTLINES to select_catalog_role / create or replace view USER_OUTLINE_HINTS (NAME, NODE, STAGE, JOIN_POS, HINT) as select o.ol_name, h.node#, h.stage#, table_pos, NVL(h.hint_string, h.hint_text) from outln.ol$ o, outln.ol$hints h, sys.user$ u where o.ol_name = h.ol_name and o.creator = u.name and u.user# = USERENV('SCHEMAID') / comment on table USER_OUTLINE_HINTS is 'Hints stored in outlines owned by the user' / comment on column USER_OUTLINE_HINTS.NAME is 'Stage at which the outline is processed' / comment on column USER_OUTLINE_HINTS.NODE is 'I.D. of the query or subquery to which the hint applies' / comment on column USER_OUTLINE_HINTS.STAGE is 'Stage at which outline is processed' / comment on column USER_OUTLINE_HINTS.JOIN_POS is 'Position of the table in the join order' / comment on column USER_OUTLINE_HINTS.HINT is 'Text of the hint' / create or replace public synonym USER_OUTLINE_HINTS for USER_OUTLINE_HINTS / grant select on USER_OUTLINE_HINTS to PUBLIC with grant option / create or replace public synonym ALL_OUTLINE_HINTS for USER_OUTLINE_HINTS / grant select on ALL_OUTLINE_HINTS to PUBLIC with grant option / create or replace view DBA_OUTLINE_HINTS (NAME, OWNER, NODE, STAGE, JOIN_POS, HINT) as select o.ol_name, o.creator, h.node#, h.stage#, h.table_pos, NVL(h.hint_string, h.hint_text) from outln.ol$ o, outln.ol$hints h where o.ol_name = h.ol_name / comment on table DBA_OUTLINE_HINTS is 'Hints stored in outlines' / comment on column DBA_OUTLINE_HINTS.NAME is 'Name of the outline' / comment on column DBA_OUTLINE_HINTS.OWNER is 'User who created the outline' / comment on column DBA_OUTLINE_HINTS.NODE is 'I.D. of the query or subquery to which the hint applies' / comment on column DBA_OUTLINE_HINTS.STAGE is 'Stage at which the outline is processed' / comment on column DBA_OUTLINE_HINTS.JOIN_POS is 'Position of the table in the join order' / comment on column DBA_OUTLINE_HINTS.HINT is 'Text of the hint' / create or replace public synonym DBA_OUTLINE_HINTS for DBA_OUTLINE_HINTS / grant select on DBA_OUTLINE_HINTS to select_catalog_role / create global temporary table system.ol$ ( ol_name varchar2(30), /* outline name */ sql_text long, /* the SQL stmt being outlined */ textlen number, /* length of SQL stmt */ signature raw(16), /* signature of sql_text */ hash_value number, /* KGL's calculated hash value */ hash_value2 number,/* hash value on sql_text stripped of whitespace */ category varchar2(30), /* category name */ version varchar2(64), /* db version @ outline creation */ creator varchar2(30), /* user from whom outline created */ timestamp date, /* time of creation */ flags number, /* e.g. everUsed, bindVars, dynSql */ hintcount number, /* number of hints on the outline */ spare1 number, /* spare column */ spare2 varchar2(1000) /* spare column */ ) on commit preserve rows / create global temporary table system.ol$hints ( ol_name varchar2(30), /* outline name */ hint# number, /* which hint for a given outline */ category varchar2(30), /* collection/grouping name */ hint_type number, /* type of hint */ hint_text varchar2(512), /* hint specific information */ stage# number, /* stage of hint generation/applic'n */ node# number, /* QBC node id */ table_name varchar2(30), /* for ORDERED hint */ table_tin number, /* table instance number */ table_pos number, /* for ORDERED hint */ ref_id number, /* node id that this hint is referencing */ user_table_name varchar2(64), /* table name to which this hint applies */ cost double precision, /* optimizer estimated cost of the */ /* hinted operation */ cardinality double precision, /* optimizer estimated cardinality */ /* of the hinted operation */ bytes double precision, /* optimizer estimated byte count */ /* of the hinted operation */ hint_textoff number, /* offset into the SQL statement to */ /* which this hint applies */ hint_textlen number, /* length of SQL to which this hint applies */ join_pred varchar2(2000), /* join predicate (applies only for */ /* join method hints) */ spare1 number, /* spare number for future enhancements */ spare2 number, /* spare number for future enhancements */ hint_string clob /* hint text (replaces hint_text column) */ ) on commit preserve rows / create global temporary table system.ol$nodes ( ol_name varchar2(30), /* outline name */ category varchar2(30), /* outline category */ node_id number, /* qbc node identifier */ parent_id number, /* node id of the parent node for current node */ node_type number, /* qbc node type */ node_textlen number, /* length of SQL to which this node applies */ node_textoff number, /* offset into the SQL statement to which this */ /* node applies */ node_name varchar2(64) /* qbc node name */ ) on commit preserve rows / create unique index system.ol$name on system.ol$(ol_name) / create unique index system.ol$signature on system.ol$(signature,category) / create unique index system.ol$hnt_num on system.ol$hints(ol_name, hint#) / create or replace public synonym ol$ for system.ol$ / create or replace public synonym ol$hints for system.ol$hints / create or replace public synonym ol$nodes for system.ol$nodes / grant select,insert,update,delete on system.ol$ to public / grant select,insert,update,delete on system.ol$hints to public / grant select,insert,update,delete on system.ol$nodes to public /