Rem
Rem $Header: catol.sql 22-jun-98.12:47:36 pejustus Exp $
Rem
Rem catol.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998. All Rights Reserved.
Rem
Rem    NAME
Rem      catol.sql - outline views and synonyms
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    pejustus    06/22/98 - add phase column to hint views
Rem    svivian     04/16/98 - Created
Rem

create or replace view USER_OUTLINES
  (NAME, CATEGORY, USED, TIMESTAMP, VERSION, SQL_TEXT)
as
select ol_name, category, 
  decode(flags, 0, 'UNUSED', 1, 'USED', 'UNDEFINED'),
  timestamp, version, sql_text
from outln.ol$
where creator = USER
/
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'
/
drop public synonym USER_OUTLINES
/
create public synonym USER_OUTLINES for USER_OUTLINES
/
grant select on USER_OUTLINES to PUBLIC with grant option
/
drop public synonym ALL_OUTLINES
/
create 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)
as
select ol_name, creator, category, 
  decode(flags, 0, 'UNUSED', 1, 'USED', 'UNDEFINED'),
  timestamp, version, sql_text
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'
/
drop public synonym DBA_OUTLINES
/
create 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, h.hint_text
from outln.ol$ o, outln.ol$hints h
where o.ol_name = h.ol_name
  and o.creator = USER
/
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'
/
drop public synonym USER_OUTLINE_HINTS
/
create public synonym USER_OUTLINE_HINTS for USER_OUTLINE_HINTS
/
grant select on USER_OUTLINE_HINTS to PUBLIC with grant option
/
drop public synonym ALL_OUTLINE_HINTS
/
create 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, 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'
/
drop public synonym DBA_OUTLINE_HINTS
/
create public synonym DBA_OUTLINE_HINTS for DBA_OUTLINE_HINTS
/
grant select on DBA_OUTLINE_HINTS to select_catalog_role
/
