Rem
Rem $Header: catsum.sql 06-aug-99.11:58:50 bpanchap Exp $
Rem
Rem catsum.sql
Rem
Rem  Copyright (c) Oracle Corporation 1997, 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      catsum.sql
Rem
Rem    DESCRIPTION
Rem      Creates the data dictionary views for summary management
Rem
Rem    NOTES
Rem      Must be run while connectd as SYS or INTERNAL
Rem    MODIFIED   (MM/DD/YY)
Rem    bpanchap    08/06/99 - Fixing Bug 955953
Rem    rshaikh     05/24/99 - remove comments so *summary* views dont show in d
Rem    bpanchap    05/19/99 - Fixing the comparison mask for inc_refreshable
Rem    jraitto     03/30/99 - fix INC_REFRESHABLE, RESTRICTED_SYNTAX & SUMMARY
Rem    masubram    11/17/98 - modify all_refresh_dependencies view             
Rem    jraitto     11/17/98 - add REWRITE_ENABLED column to *_MVIEW_ANALYSIS
Rem    qiwang      10/15/98 - use distinct intcol# instead of col#
Rem    jfeenan     10/26/98 - Add DBA_ synonymns
Rem    jfinnert    08/20/98 - Change NONE to NEVER in xxx_mview_analysis
Rem    qiwang      06/12/98 - Change privilege numbers
Rem    awitkows    06/03/98 - add mview views
Rem    jfinnert    06/02/98 - Change summaries views
Rem    rguzman     05/28/98 - Fix ALL_SUMMARIES view, with respect to refresh s
Rem    qiwang      04/06/98 - Change sa.expression to sa.aggtext in SUMMARY_AGG
Rem    qiwang      04/03/98 - Convert 0 to NULL in DIM_CHILD_OF views
Rem    qiwang      04/03/98 - Add even more missing views.
Rem    jfinnert    03/24/98 - Updating object number after refreshview
Rem    qiwang      03/24/98 - Create public synonyms for ALL_ and USER_ views.
Rem    qiwang      03/23/98 - Fix a bug in DBA_DIM_CHILD_OF
Rem    jfinnert    03/21/98 - Change GRANTs to only allow selection from the ca
Rem    qiwang      03/20/98 - Add DBA_ view definitions related to summary obj.
Rem    qiwang      02/17/98 - Drop the synonyms created for the set of DBA_ vie
Rem    qiwang      02/12/98 - Fix bugs in DBA_DIM_JOIN_KEYS view
Rem    qiwang      02/10/98 - Fix some comments
Rem    qiwang      02/10/98 - Add DBA_ view definitions related to dimension ob
Rem    rguzman     02/05/98 - Update ALL_SUMMARIES view with the new summary pr
Rem    wnorcott    01/14/98 - object-type # for summaries changes from 29 to 38
Rem    jfeenan     12/16/97 - Add comment for future priv change
Rem    jfeenan     12/15/97 - Change ALL_SUMMARY_DETAIL_TABLES table type to 1
Rem    jfeenan     11/12/97 - Temp hack for initial Beta1 testing make view loo
Rem    jfeenan     11/10/97 - Break out of pflags and mflags from flags
Rem    jfeenan     11/07/97 - Fix up ALL_SUMMARY_DETAIL_TABLES
Rem    jfeenan     11/06/97 - Add ALL_SUMMARIES, ALL_SUMMARY_DETAIL_TABLES and
Rem    jfeenan     11/06/97 - Catalog views for summary management
Rem    jfeenan     11/06/97 - Created
Rem


rem
rem The following are the view definitions for the 8.1 summary 
rem management project.
rem
rem For Beta 1 this will include only the information for summaries
rem dimensions will be added at a later time.
rem

rem
rem DIMENSIONS
rem

create or replace view DBA_DIMENSIONS
	(OWNER,
	 DIMENSION_NAME,
	 INVALID,
	 REVISION)
as
select u.name, o.name,
       decode(o.status, 5, 'Y', 'N'),
       1                  /* Metadata revision number */
from sys.dim$ d, sys.obj$ o, sys.user$ u
where o.owner# = u.user#
  and o.obj# = d.obj#
/  	
comment on table DBA_DIMENSIONS is
'Description of the dimension objects accessible to the DBA'
/
comment on column DBA_DIMENSIONS.OWNER is
'Owner of the dimension'
/
comment on column DBA_DIMENSIONS.DIMENSION_NAME is
'Name of the dimension'
/
comment on column DBA_DIMENSIONS.INVALID is
'Invalidity of the dimension, Y = INVALID, N = VALID'
/
comment on column DBA_DIMENSIONS.REVISION is
'Revision levle of the dimension'
/
drop public synonym DBA_DIMENSIONS
/
create public synonym DBA_DIMENSIONS for DBA_DIMENSIONS
/
grant select on DBA_DIMENSIONS to select_catalog_role
/

create or replace view ALL_DIMENSIONS
	(OWNER,
	 DIMENSION_NAME,
	 INVALID,
	 REVISION)
as
select u.name, o.name,
       decode(o.status, 5, 'Y', 'N'),
       1                  /* Metadata revision number */
from sys.dim$ d, sys.obj$ o, sys.user$ u
where o.owner# = u.user#
  and o.obj# = d.obj#
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-215 /* CREATE ANY DIMENSION */,
                                       -216 /* ALTER ANY DIMENSION */,
                                       -217 /* DROP ANY DIMENSION */)
                 )
      ) 
/  	
comment on table ALL_DIMENSIONS is
'Description of the dimension objects accessible to the DBA'
/
comment on column ALL_DIMENSIONS.OWNER is
'Owner of the dimension'
/
comment on column ALL_DIMENSIONS.DIMENSION_NAME is
'Name of the dimension'
/
comment on column ALL_DIMENSIONS.INVALID is
'Invalidity of the dimension, Y = INVALID, N = VALID'
/
comment on column ALL_DIMENSIONS.REVISION is
'Revision levle of the dimension'
/
drop public synonym ALL_DIMENSIONS
/
create public synonym ALL_DIMENSIONS for ALL_DIMENSIONS
/
grant select on ALL_DIMENSIONS to PUBLIC with grant option
/

create or replace view USER_DIMENSIONS
	(OWNER,
	 DIMENSION_NAME,
	 INVALID,
	 REVISION)
as
select u.name, o.name,
       decode(o.status, 5, 'Y', 'N'),
       1                  /* Metadata revision number */
from sys.dim$ d, sys.obj$ o, sys.user$ u
where o.owner# = u.user#
  and o.obj# = d.obj# 
  and o.owner# = userenv('SCHEMAID')  
/  	
comment on table USER_DIMENSIONS is
'Description of the dimension objects accessible to the DBA'
/
comment on column USER_DIMENSIONS.OWNER is
'Owner of the dimension'
/
comment on column USER_DIMENSIONS.DIMENSION_NAME is
'Name of the dimension'
/
comment on column USER_DIMENSIONS.INVALID is
'Invalidity of the dimension, Y = INVALID, N = VALID'
/
comment on column USER_DIMENSIONS.REVISION is
'Revision levle of the dimension'
/
drop public synonym USER_DIMENSIONS
/
create public synonym USER_DIMENSIONS for USER_DIMENSIONS
/
grant select on USER_DIMENSIONS to PUBLIC with grant option
/

rem
rem DIM_LEVELS
rem

create or replace view DBA_DIM_LEVELS
   (OWNER, DIMENSION_NAME, LEVEL_NAME, NUM_COLUMNS,
    DETAILOBJ_OWNER, DETAILOBJ_NAME)
as
select u.name, o.name, dl.levelname, 
       temp.num_col,
       u1.name, o1.name
from (select dlk.dimobj#, dlk.levelid#, dlk.detailobj#, 
             COUNT(*) as num_col
      from sys.dimlevelkey$ dlk
      group by dlk.dimobj#, dlk.levelid#, dlk.detailobj#) temp,
      sys.dimlevel$ dl, sys.obj$ o, sys.user$ u,
      sys.obj$ o1, sys.user$ u1
where dl.dimobj# = o.obj#   and
      o.owner# = u.user#    and
      dl.dimobj# = temp.dimobj# and 
      dl.levelid# = temp.levelid# and
      temp.detailobj# = o1.obj# and 
      o1.owner# = u1.user#
/
comment on table DBA_DIM_LEVELS is
'Description of dimension levels visible to DBA'
/
comment on column  DBA_DIM_LEVELS.OWNER is
'Owner of the dimension'
/
comment on column  DBA_DIM_LEVELS.DIMENSION_NAME is
'Name of the dimension'
/
comment on column  DBA_DIM_LEVELS.LEVEL_NAME is
'Name of the dimension level (unique within a dimension)'
/
comment on column  DBA_DIM_LEVELS.NUM_COLUMNS is
'Number of columns in the level definition'
/
comment on column  DBA_DIM_LEVELS.DETAILOBJ_OWNER is
'Owner of the detail object that the keys of this level come from'
/
comment on column  DBA_DIM_LEVELS.DETAILOBJ_NAME is
'Name of the table that the keys of this level come from'
/
drop public synonym DBA_DIM_LEVELS
/
create public synonym DBA_DIM_LEVELS for DBA_DIM_LEVELS
/
grant select on DBA_DIM_LEVELS to select_catalog_role
/

create or replace view ALL_DIM_LEVELS
   (OWNER, DIMENSION_NAME, LEVEL_NAME, NUM_COLUMNS,
    DETAILOBJ_OWNER, DETAILOBJ_NAME)
as
select u.name, o.name, dl.levelname, 
       temp.num_col,
       u1.name, o1.name
from (select dlk.dimobj#, dlk.levelid#, dlk.detailobj#, 
             COUNT(*) as num_col
      from sys.dimlevelkey$ dlk
      group by dlk.dimobj#, dlk.levelid#, dlk.detailobj#) temp,
      sys.dimlevel$ dl, sys.obj$ o, sys.user$ u,
      sys.obj$ o1, sys.user$ u1
where dl.dimobj# = o.obj#   and
      o.owner# = u.user#    and
      dl.dimobj# = temp.dimobj# and 
      dl.levelid# = temp.levelid# and
      temp.detailobj# = o1.obj# and 
      o1.owner# = u1.user# and
      (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-215 /* CREATE ANY DIMENSION */,
                                       -216 /* ALTER ANY DIMENSION */,
                                       -217 /* DROP ANY DIMENSION */)
                 )
      ) 
/
comment on table ALL_DIM_LEVELS is
'Description of dimension levels visible to DBA'
/
comment on column  ALL_DIM_LEVELS.OWNER is
'Owner of the dimension'
/
comment on column  ALL_DIM_LEVELS.DIMENSION_NAME is
'Name of the dimension'
/
comment on column  ALL_DIM_LEVELS.LEVEL_NAME is
'Name of the dimension level (unique within a dimension)'
/
comment on column  ALL_DIM_LEVELS.NUM_COLUMNS is
'Number of columns in the level definition'
/
comment on column  ALL_DIM_LEVELS.DETAILOBJ_OWNER is
'Owner of the detail object that the keys of this level come from'
/
comment on column  ALL_DIM_LEVELS.DETAILOBJ_NAME is
'Name of the table that the keys of this level come from'
/

drop public synonym ALL_DIM_LEVELS
/
create public synonym ALL_DIM_LEVELS for ALL_DIM_LEVELS
/
grant select on ALL_DIM_LEVELS to PUBLIC with grant option
/

create or replace view USER_DIM_LEVELS
   (OWNER, DIMENSION_NAME, LEVEL_NAME, NUM_COLUMNS,
    DETAILOBJ_OWNER, DETAILOBJ_NAME)
as
select u.name, o.name, dl.levelname, 
       temp.num_col,
       u1.name, o1.name
from (select dlk.dimobj#, dlk.levelid#, dlk.detailobj#, 
             COUNT(*) as num_col
      from sys.dimlevelkey$ dlk
      group by dlk.dimobj#, dlk.levelid#, dlk.detailobj#) temp,
      sys.dimlevel$ dl, sys.obj$ o, sys.user$ u,
      sys.obj$ o1, sys.user$ u1
where dl.dimobj# = o.obj#   and
      o.owner# = u.user#    and
      dl.dimobj# = temp.dimobj# and 
      dl.levelid# = temp.levelid# and
      temp.detailobj# = o1.obj# and 
      o1.owner# = u1.user# and
      o.owner# = userenv('SCHEMAID')
/
comment on table USER_DIM_LEVELS is
'Description of dimension levels visible to DBA'
/
comment on column  USER_DIM_LEVELS.OWNER is
'Owner of the dimension'
/
comment on column  USER_DIM_LEVELS.DIMENSION_NAME is
'Name of the dimension'
/
comment on column  USER_DIM_LEVELS.LEVEL_NAME is
'Name of the dimension level (unique within a dimension)'
/
comment on column  USER_DIM_LEVELS.NUM_COLUMNS is
'Number of columns in the level definition'
/
comment on column  USER_DIM_LEVELS.DETAILOBJ_OWNER is
'Owner of the detail object that the keys of this level come from'
/
comment on column  USER_DIM_LEVELS.DETAILOBJ_NAME is
'Name of the table that the keys of this level come from'
/
drop public synonym USER_DIM_LEVELS
/
create public synonym USER_DIM_LEVELS for USER_DIM_LEVELS
/
grant select on USER_DIM_LEVELS to PUBLIC with grant option
/
 
REM
REM  DBA_DIM_LEVEL_KEY
REM

create or replace view DBA_DIM_LEVEL_KEY
   (OWNER, DIMENSION_NAME, LEVEL_NAME, KEY_POSITION, COLUMN_NAME)
as
select u.name, o.name, dl.levelname, dlk.keypos#, c.name
from sys.dimlevelkey$ dlk, sys.obj$ o, sys.user$ u, sys.dimlevel$ dl, 
     sys.col$ c
where dlk.dimobj# = o.obj#
  and o.owner# = u.user#
  and dlk.dimobj# = dl.dimobj#
  and dlk.levelid# = dl.levelid#
  and dlk.detailobj# = c.obj#
  and dlk.col# = c.intcol#

/
comment on table DBA_DIM_LEVEL_KEY is
'Representations of columns of a dimension level'
/
comment on column DBA_DIM_LEVEL_KEY.OWNER is
'Owner of the dimension'
/
comment on column DBA_DIM_LEVEL_KEY.DIMENSION_NAME is
'Name of the dimension'
/
comment on column DBA_DIM_LEVEL_KEY.LEVEL_NAME is
'Name of the hierarchy level'
/
comment on column DBA_DIM_LEVEL_KEY.KEY_POSITION is
'Ordinal position of the key column within the level'
/
comment on column DBA_DIM_LEVEL_KEY.COLUMN_NAME is
'Name of the key column'
/
drop public synonym DBA_DIM_LEVEL_KEY
/
create public synonym DBA_DIM_LEVEL_KEY for DBA_DIM_LEVEL_KEY
/
grant select on DBA_DIM_LEVEL_KEY to select_catalog_role
/

create or replace view ALL_DIM_LEVEL_KEY
   (OWNER, DIMENSION_NAME, LEVEL_NAME, KEY_POSITION, COLUMN_NAME)
as
select u.name, o.name, dl.levelname, dlk.keypos#, c.name
from sys.dimlevelkey$ dlk, sys.obj$ o, sys.user$ u, sys.dimlevel$ dl, 
     sys.col$ c
where dlk.dimobj# = o.obj#
  and o.owner# = u.user#
  and dlk.dimobj# = dl.dimobj#
  and dlk.levelid# = dl.levelid#
  and dlk.detailobj# = c.obj#
  and dlk.col# = c.intcol#
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-215 /* CREATE ANY DIMENSION */,
                                       -216 /* ALTER ANY DIMENSION */,
                                       -217 /* DROP ANY DIMENSION */)
                 )
      ) 
/
comment on table ALL_DIM_LEVEL_KEY is
'Representations of columns of a dimension level'
/
comment on column ALL_DIM_LEVEL_KEY.OWNER is
'Owner of the dimension'
/
comment on column ALL_DIM_LEVEL_KEY.DIMENSION_NAME is
'Name of the dimension'
/
comment on column ALL_DIM_LEVEL_KEY.LEVEL_NAME is
'Name of the hierarchy level'
/
comment on column ALL_DIM_LEVEL_KEY.KEY_POSITION is
'Ordinal position of the key column within the level'
/
comment on column ALL_DIM_LEVEL_KEY.COLUMN_NAME is
'Name of the key column'
/
drop public synonym ALL_DIM_LEVEL_KEY
/
create public synonym ALL_DIM_LEVEL_KEY for ALL_DIM_LEVEL_KEY
/
grant select on ALL_DIM_LEVEL_KEY to PUBLIC with grant option
/

create or replace view USER_DIM_LEVEL_KEY
   (OWNER, DIMENSION_NAME, LEVEL_NAME, KEY_POSITION, COLUMN_NAME)
as
select u.name, o.name, dl.levelname, dlk.keypos#, c.name
from sys.dimlevelkey$ dlk, sys.obj$ o, sys.user$ u, sys.dimlevel$ dl, 
     sys.col$ c
where dlk.dimobj# = o.obj#
  and o.owner# = u.user#
  and dlk.dimobj# = dl.dimobj#
  and dlk.levelid# = dl.levelid#
  and dlk.detailobj# = c.obj#
  and dlk.col# = c.intcol#
  and o.owner# = userenv('SCHEMAID')
/
comment on table USER_DIM_LEVEL_KEY is
'Representations of columns of a dimension level'
/
comment on column USER_DIM_LEVEL_KEY.OWNER is
'Owner of the dimension'
/
comment on column USER_DIM_LEVEL_KEY.DIMENSION_NAME is
'Name of the dimension'
/
comment on column USER_DIM_LEVEL_KEY.LEVEL_NAME is
'Name of the hierarchy level'
/
comment on column USER_DIM_LEVEL_KEY.KEY_POSITION is
'Ordinal position of the key column within the level'
/
comment on column USER_DIM_LEVEL_KEY.COLUMN_NAME is
'Name of the key column'
/
drop public synonym USER_DIM_LEVEL_KEY
/
create public synonym USER_DIM_LEVEL_KEY for USER_DIM_LEVEL_KEY
/
grant select on USER_DIM_LEVEL_KEY to PUBLIC with grant option
/

REM
REM DBA_DIM_ATTRIBUTES
REM

create or replace view DBA_DIM_ATTRIBUTES
    (OWNER, DIMENSION_NAME, LEVEL_NAME, COLUMN_NAME, INFERRED)
as
select u.name, o.name, dl.levelname, c.name, 'N'
from sys.dimattr$ da, sys.obj$ o, sys.user$ u, sys.dimlevel$ dl, sys.col$ c 
where da.dimobj# = o.obj#
  and o.owner# = u.user#
  and da.dimobj# = dl.dimobj#
  and da.levelid# = dl.levelid#
  and da.detailobj# = c.obj#
  and da.col# = c.intcol#

/
comment on table DBA_DIM_ATTRIBUTES is
'Representation of the relationship between a dimension level and
 a functionally dependent column'
/
comment on column DBA_DIM_ATTRIBUTES.OWNER is
'Owner of the dimentsion'
/
comment on column DBA_DIM_ATTRIBUTES.DIMENSION_NAME is
'Name of the dimension'
/
comment on column DBA_DIM_ATTRIBUTES.LEVEL_NAME is
'Name of the hierarchy level'
/
comment on column DBA_DIM_ATTRIBUTES.COLUMN_NAME is
'Name of the dependent column'
/
comment on column DBA_DIM_ATTRIBUTES.INFERRED is
'Whether this attribute is inferred from a JOIN KEY specification'
/
drop public synonym DBA_DIM_ATTRIBUTES
/
create public synonym DBA_DIM_ATTRIBUTES for DBA_DIM_ATTRIBUTES
/
grant select on DBA_DIM_ATTRIBUTES  to select_catalog_role
/

create or replace view ALL_DIM_ATTRIBUTES
    (OWNER, DIMENSION_NAME, LEVEL_NAME, COLUMN_NAME, INFERRED)
as
select u.name, o.name, dl.levelname, c.name, 'N'
from sys.dimattr$ da, sys.obj$ o, sys.user$ u, sys.dimlevel$ dl, sys.col$ c 
where da.dimobj# = o.obj#
  and o.owner# = u.user#
  and da.dimobj# = dl.dimobj#
  and da.levelid# = dl.levelid#
  and da.detailobj# = c.obj#
  and da.col# = c.intcol#
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-215 /* CREATE ANY DIMENSION */,
                                       -216 /* ALTER ANY DIMENSION */,
                                       -217 /* DROP ANY DIMENSION */)
                 )
      ) 
/
comment on table ALL_DIM_ATTRIBUTES is
'Representation of the relationship between a dimension level and
 a functionally dependent column'
/
comment on column ALL_DIM_ATTRIBUTES.OWNER is
'Owner of the dimentsion'
/
comment on column ALL_DIM_ATTRIBUTES.DIMENSION_NAME is
'Name of the dimension'
/
comment on column ALL_DIM_ATTRIBUTES.LEVEL_NAME is
'Name of the hierarchy level'
/
comment on column ALL_DIM_ATTRIBUTES.COLUMN_NAME is
'Name of the dependent column'
/
comment on column ALL_DIM_ATTRIBUTES.INFERRED is
'Whether this attribute is inferred from a JOIN KEY specification'
/

drop public synonym ALL_DIM_ATTRIBUTES
/
create public synonym ALL_DIM_ATTRIBUTES for ALL_DIM_ATTRIBUTES
/
grant select on ALL_DIM_ATTRIBUTES to PUBLIC with grant option
/

create or replace view USER_DIM_ATTRIBUTES
    (OWNER, DIMENSION_NAME, LEVEL_NAME, COLUMN_NAME, INFERRED)
as
select u.name, o.name, dl.levelname, c.name, 'N'
from sys.dimattr$ da, sys.obj$ o, sys.user$ u, sys.dimlevel$ dl, sys.col$ c 
where da.dimobj# = o.obj#
  and o.owner# = u.user#
  and da.dimobj# = dl.dimobj#
  and da.levelid# = dl.levelid#
  and da.detailobj# = c.obj#
  and da.col# = c.intcol#
  and o.owner# = userenv('SCHEMAID')
/
comment on table USER_DIM_ATTRIBUTES is
'Representation of the relationship between a dimension level and
 a functionally dependent column'
/
comment on column USER_DIM_ATTRIBUTES.OWNER is
'Owner of the dimentsion'
/
comment on column USER_DIM_ATTRIBUTES.DIMENSION_NAME is
'Name of the dimension'
/
comment on column USER_DIM_ATTRIBUTES.LEVEL_NAME is
'Name of the hierarchy level'
/
comment on column USER_DIM_ATTRIBUTES.COLUMN_NAME is
'Name of the dependent column'
/
comment on column USER_DIM_ATTRIBUTES.INFERRED is
'Whether this attribute is inferred from a JOIN KEY specification'
/
drop public synonym USER_DIM_ATTRIBUTES
/
create public synonym USER_DIM_ATTRIBUTES for USER_DIM_ATTRIBUTES
/
grant select on USER_DIM_ATTRIBUTES to PUBLIC with grant option
/

REM
REM DBA_DIM_HIERARCHIES
REM

create or replace view DBA_DIM_HIERARCHIES
    (OWNER, DIMENSION_NAME, HIERARCHY_NAME)
as
select u.name, o.name, h.hiername
from sys.hier$ h, sys.obj$ o, sys.user$ u
where h.dimobj# = o.obj#
  and o.owner# = u.user#

/
comment on table DBA_DIM_HIERARCHIES is
'Representation of a dimension hierarchy'
/
comment on column DBA_DIM_HIERARCHIES.OWNER is
'Owner of the dimension'
/
comment on column DBA_DIM_HIERARCHIES.DIMENSION_NAME is
'Name of the dimension'
/
comment on column DBA_DIM_HIERARCHIES.HIERARCHY_NAME is
'Name of the hierarchy'
/
drop public synonym DBA_DIM_HIERARCHIES
/
create public synonym DBA_DIM_HIERARCHIES for DBA_DIM_HIERARCHIES
/
grant select on DBA_DIM_HIERARCHIES  to select_catalog_role
/

create or replace view ALL_DIM_HIERARCHIES
    (OWNER, DIMENSION_NAME, HIERARCHY_NAME)
as
select u.name, o.name, h.hiername
from sys.hier$ h, sys.obj$ o, sys.user$ u
where h.dimobj# = o.obj#
  and o.owner# = u.user#
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-215 /* CREATE ANY DIMENSION */,
                                       -216 /* ALTER ANY DIMENSION */,
                                       -217 /* DROP ANY DIMENSION */)
                 )
      ) 
/
comment on table ALL_DIM_HIERARCHIES is
'Representation of a dimension hierarchy'
/
comment on column ALL_DIM_HIERARCHIES.OWNER is
'Owner of the dimension'
/
comment on column ALL_DIM_HIERARCHIES.DIMENSION_NAME is
'Name of the dimension'
/
comment on column ALL_DIM_HIERARCHIES.HIERARCHY_NAME is
'Name of the hierarchy'
/
drop public synonym ALL_DIM_HIERARCHIES
/
create public synonym ALL_DIM_HIERARCHIES for ALL_DIM_HIERARCHIES
/
grant select on ALL_DIM_HIERARCHIES to PUBLIC with grant option
/

create or replace view USER_DIM_HIERARCHIES
    (OWNER, DIMENSION_NAME, HIERARCHY_NAME)
as
select u.name, o.name, h.hiername
from sys.hier$ h, sys.obj$ o, sys.user$ u
where h.dimobj# = o.obj#
  and o.owner# = u.user#
  and o.owner# = userenv('SCHEMAID')
/
comment on table USER_DIM_HIERARCHIES is
'Representation of a dimension hierarchy'
/
comment on column USER_DIM_HIERARCHIES.OWNER is
'Owner of the dimension'
/
comment on column USER_DIM_HIERARCHIES.DIMENSION_NAME is
'Name of the dimension'
/
comment on column USER_DIM_HIERARCHIES.HIERARCHY_NAME is
'Name of the hierarchy'
/
drop public synonym USER_DIM_HIERARCHIES
/
create public synonym USER_DIM_HIERARCHIES for USER_DIM_HIERARCHIES
/
grant select on USER_DIM_HIERARCHIES to PUBLIC with grant option
/

REM
REM DBA_DIM_CHILD_OF
REM

create or replace view DBA_DIM_CHILD_OF 
   (OWNER, DIMENSION_NAME, HIERARCHY_NAME, POSITION,
    CHILD_LEVEL_NAME, JOIN_KEY_ID, PARENT_LEVEL_NAME)
as
select u.name, o.name, h.hiername, chl.pos#,
       cdl.levelname, 
       decode(phl.joinkeyid#, 0, NULL, phl.joinkeyid#),
       pdl.levelname
from sys.obj$ o, sys.user$ u, sys.hier$ h,
     sys.hierlevel$ phl, sys.hierlevel$ chl,
     sys.dimlevel$ pdl,  sys.dimlevel$ cdl
where phl.dimobj# = o.obj#
  and o.owner# = u.user#
  and phl.dimobj# = h.dimobj#
  and phl.hierid# = h.hierid#
  and phl.dimobj# = pdl.dimobj#
  and phl.levelid# = pdl.levelid#
  and phl.dimobj# = chl.dimobj#
  and phl.hierid# = chl.hierid#
  and phl.pos# = chl.pos# + 1
  and chl.dimobj# = cdl.dimobj#
  and chl.levelid# = cdl.levelid#

/
comment on table DBA_DIM_CHILD_OF is
'Representaion of a 1:n hierarchical relationship between a pair of levels in 
 a dimension'
/
comment on column DBA_DIM_CHILD_OF.OWNER is
'Owner of the dimension'
/
comment on column DBA_DIM_CHILD_OF.DIMENSION_NAME is
'Name of the dimension'
/
comment on column DBA_DIM_CHILD_OF.HIERARCHY_NAME is
'Name of the hierarchy'
/
comment on column DBA_DIM_CHILD_OF.POSITION is
'Hierarchical position within this hierarchy, position 1 being
 the most detailed'
/
comment on column DBA_DIM_CHILD_OF.CHILD_LEVEL_NAME is
'Name of the child-side level of this 1:n relationship'
/
comment on column DBA_DIM_CHILD_OF.JOIN_KEY_ID is
'Keys that join child uniquely to the parent'
/
comment on column DBA_DIM_CHILD_OF.PARENT_LEVEL_NAME is
'Name of the parent-side level of this 1:n relationship'
/
drop public synonym DBA_DIM_CHILD_OF
/
create public synonym DBA_DIM_CHILD_OF for DBA_DIM_CHILD_OF
/
grant select on DBA_DIM_CHILD_OF  to select_catalog_role
/

create or replace view ALL_DIM_CHILD_OF 
   (OWNER, DIMENSION_NAME, HIERARCHY_NAME, POSITION,
    CHILD_LEVEL_NAME, JOIN_KEY_ID, PARENT_LEVEL_NAME)
as
select u.name, o.name, h.hiername, chl.pos#,
       cdl.levelname, 
       decode(phl.joinkeyid#, 0, NULL, phl.joinkeyid#),
       pdl.levelname
from sys.obj$ o, sys.user$ u, sys.hier$ h,
     sys.hierlevel$ phl, sys.hierlevel$ chl,
     sys.dimlevel$ pdl,  sys.dimlevel$ cdl
where phl.dimobj# = o.obj#
  and o.owner# = u.user#
  and phl.dimobj# = h.dimobj#
  and phl.hierid# = h.hierid#
  and phl.dimobj# = pdl.dimobj#
  and phl.levelid# = pdl.levelid#
  and phl.dimobj# = chl.dimobj#
  and phl.hierid# = chl.hierid#
  and phl.pos# = chl.pos# + 1
  and chl.dimobj# = cdl.dimobj#
  and chl.levelid# = cdl.levelid#
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-215 /* CREATE ANY DIMENSION */,
                                       -216 /* ALTER ANY DIMENSION */,
                                       -217 /* DROP ANY DIMENSION */)
                 )
      ) 
/
comment on table ALL_DIM_CHILD_OF is
'Representaion of a 1:n hierarchical relationship between a pair of levels in 
 a dimension'
/
comment on column ALL_DIM_CHILD_OF.OWNER is
'Owner of the dimension'
/
comment on column ALL_DIM_CHILD_OF.DIMENSION_NAME is
'Name of the dimension'
/
comment on column ALL_DIM_CHILD_OF.HIERARCHY_NAME is
'Name of the hierarchy'
/
comment on column ALL_DIM_CHILD_OF.POSITION is
'Hierarchical position within this hierarchy, position 1 being
 the most detailed'
/
comment on column ALL_DIM_CHILD_OF.CHILD_LEVEL_NAME is
'Name of the child-side level of this 1:n relationship'
/
comment on column ALL_DIM_CHILD_OF.JOIN_KEY_ID is
'Keys that join child uniquely to the parent'
/
comment on column ALL_DIM_CHILD_OF.PARENT_LEVEL_NAME is
'Name of the parent-side level of this 1:n relationship'
/
drop public synonym ALL_DIM_CHILD_OF
/
create public synonym ALL_DIM_CHILD_OF for ALL_DIM_CHILD_OF
/
grant select on ALL_DIM_CHILD_OF to PUBLIC with grant option
/

create or replace view USER_DIM_CHILD_OF 
   (OWNER, DIMENSION_NAME, HIERARCHY_NAME, POSITION,
    CHILD_LEVEL_NAME, JOIN_KEY_ID, PARENT_LEVEL_NAME)
as
select u.name, o.name, h.hiername, chl.pos#,
       cdl.levelname, 
       decode(phl.joinkeyid#, 0, NULL, phl.joinkeyid#),
       pdl.levelname
from sys.obj$ o, sys.user$ u, sys.hier$ h,
     sys.hierlevel$ phl, sys.hierlevel$ chl,
     sys.dimlevel$ pdl,  sys.dimlevel$ cdl
where phl.dimobj# = o.obj#
  and o.owner# = u.user#
  and phl.dimobj# = h.dimobj#
  and phl.hierid# = h.hierid#
  and phl.dimobj# = pdl.dimobj#
  and phl.levelid# = pdl.levelid#
  and phl.dimobj# = chl.dimobj#
  and phl.hierid# = chl.hierid#
  and phl.pos# = chl.pos# + 1
  and chl.dimobj# = cdl.dimobj#
  and chl.levelid# = cdl.levelid#
  and o.owner# = userenv('SCHEMAID') 
/
comment on table USER_DIM_CHILD_OF is
'Representaion of a 1:n hierarchical relationship between a pair of levels in 
 a dimension'
/
comment on column USER_DIM_CHILD_OF.OWNER is
'Owner of the dimension'
/
comment on column USER_DIM_CHILD_OF.DIMENSION_NAME is
'Name of the dimension'
/
comment on column USER_DIM_CHILD_OF.HIERARCHY_NAME is
'Name of the hierarchy'
/
comment on column USER_DIM_CHILD_OF.POSITION is
'Hierarchical position within this hierarchy, position 1 being
 the most detailed'
/
comment on column USER_DIM_CHILD_OF.CHILD_LEVEL_NAME is
'Name of the child-side level of this 1:n relationship'
/
comment on column USER_DIM_CHILD_OF.JOIN_KEY_ID is
'Keys that join child uniquely to the parent'
/
comment on column USER_DIM_CHILD_OF.PARENT_LEVEL_NAME is
'Name of the parent-side level of this 1:n relationship'
/
drop public synonym USER_DIM_CHILD_OF
/
create public synonym USER_DIM_CHILD_OF for USER_DIM_CHILD_OF
/
grant select on USER_DIM_CHILD_OF to PUBLIC with grant option
/

REM
REM DBA_DIM_JOIN_KEY
REM

create or replace view DBA_DIM_JOIN_KEY
   (OWNER, DIMENSION_NAME, DIM_KEY_ID, LEVEL_NAME,
    KEY_POSITION, HIERARCHY_NAME, CHILD_JOIN_COLUMN)
as
select u.name, o.name, djk.joinkeyid#, dl.levelname,
       djk.keypos#, h.hiername, c.name
from sys.dimjoinkey$ djk, sys.obj$ o, sys.user$ u,
     sys.dimlevel$ dl, sys.hier$ h, sys.col$ c
where djk.dimobj# = o.obj#
  and o.owner# = u.user#
  and djk.dimobj# = dl.dimobj#
  and djk.levelid# = dl.levelid#
  and djk.dimobj# = h.dimobj#
  and djk.hierid# = h.hierid#
  and djk.detailobj# = c.obj#
  and djk.col# = c.intcol#

/
comment on table DBA_DIM_JOIN_KEY is
'Representation of a join between two dimension tables. '
/
comment on column DBA_DIM_JOIN_KEY.OWNER is
'Owner of the dimension'
/
comment on column DBA_DIM_JOIN_KEY.DIMENSION_NAME is
'Name of the dimension'
/
comment on column DBA_DIM_JOIN_KEY.DIM_KEY_ID is
'Join key ID (unique within a dimension)'
/
comment on column DBA_DIM_JOIN_KEY.LEVEL_NAME is
'Name of the hierarchy level'
/
comment on column DBA_DIM_JOIN_KEY.KEY_POSITION is
'Position of the key column within the level'
/
comment on column DBA_DIM_JOIN_KEY.HIERARCHY_NAME is
'Name of the hierarchy'
/
comment on column DBA_DIM_JOIN_KEY.CHILD_JOIN_COLUMN is
'Name of the join column'
/
drop public synonym DBA_DIM_JOIN_KEY
/
create public synonym DBA_DIM_JOIN_KEY for DBA_DIM_JOIN_KEY
/
grant select on DBA_DIM_JOIN_KEY  to select_catalog_role
/

create or replace view ALL_DIM_JOIN_KEY
   (OWNER, DIMENSION_NAME, DIM_KEY_ID, LEVEL_NAME,
    KEY_POSITION, HIERARCHY_NAME, CHILD_JOIN_COLUMN)
as
select u.name, o.name, djk.joinkeyid#, dl.levelname,
       djk.keypos#, h.hiername, c.name
from sys.dimjoinkey$ djk, sys.obj$ o, sys.user$ u,
     sys.dimlevel$ dl, sys.hier$ h, sys.col$ c
where djk.dimobj# = o.obj#
  and o.owner# = u.user#
  and djk.dimobj# = dl.dimobj#
  and djk.levelid# = dl.levelid#
  and djk.dimobj# = h.dimobj#
  and djk.hierid# = h.hierid#
  and djk.detailobj# = c.obj#
  and djk.col# = c.intcol#
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-215 /* CREATE ANY DIMENSION */,
                                       -216 /* ALTER ANY DIMENSION */,
                                       -217 /* DROP ANY DIMENSION */)
                 )
      ) 
/
comment on table ALL_DIM_JOIN_KEY is
'Representation of a join between two dimension tables. '
/
comment on column ALL_DIM_JOIN_KEY.OWNER is
'Owner of the dimension'
/
comment on column ALL_DIM_JOIN_KEY.DIMENSION_NAME is
'Name of the dimension'
/
comment on column ALL_DIM_JOIN_KEY.DIM_KEY_ID is
'Join key ID (unique within a dimension)'
/
comment on column ALL_DIM_JOIN_KEY.LEVEL_NAME is
'Name of the hierarchy level'
/
comment on column ALL_DIM_JOIN_KEY.KEY_POSITION is
'Position of the key column within the level'
/
comment on column ALL_DIM_JOIN_KEY.HIERARCHY_NAME is
'Name of the hierarchy'
/
comment on column ALL_DIM_JOIN_KEY.CHILD_JOIN_COLUMN is
'Name of the join column'
/
drop public synonym ALL_DIM_JOIN_KEY
/
create public synonym ALL_DIM_JOIN_KEY for ALL_DIM_JOIN_KEY
/
grant select on ALL_DIM_JOIN_KEY  to PUBLIC with grant option
/

create or replace view USER_DIM_JOIN_KEY
   (OWNER, DIMENSION_NAME, DIM_KEY_ID, LEVEL_NAME,
    KEY_POSITION, HIERARCHY_NAME, CHILD_JOIN_COLUMN)
as
select u.name, o.name, djk.joinkeyid#, dl.levelname,
       djk.keypos#, h.hiername, c.name
from sys.dimjoinkey$ djk, sys.obj$ o, sys.user$ u,
     sys.dimlevel$ dl, sys.hier$ h, sys.col$ c
where djk.dimobj# = o.obj#
  and o.owner# = u.user#
  and djk.dimobj# = dl.dimobj#
  and djk.levelid# = dl.levelid#
  and djk.dimobj# = h.dimobj#
  and djk.hierid# = h.hierid#
  and djk.detailobj# = c.obj#
  and djk.col# = c.intcol#
  and o.owner# = userenv('SCHEMAID') 
/
comment on table USER_DIM_JOIN_KEY is
'Representation of a join between two dimension tables. '
/
comment on column USER_DIM_JOIN_KEY.OWNER is
'Owner of the dimension'
/
comment on column USER_DIM_JOIN_KEY.DIMENSION_NAME is
'Name of the dimension'
/
comment on column USER_DIM_JOIN_KEY.DIM_KEY_ID is
'Join key ID (unique within a dimension)'
/
comment on column USER_DIM_JOIN_KEY.LEVEL_NAME is
'Name of the hierarchy level'
/
comment on column USER_DIM_JOIN_KEY.KEY_POSITION is
'Position of the key column within the level'
/
comment on column USER_DIM_JOIN_KEY.HIERARCHY_NAME is
'Name of the hierarchy'
/
comment on column USER_DIM_JOIN_KEY.CHILD_JOIN_COLUMN is
'Name of the join column'
/
drop public synonym USER_DIM_JOIN_KEY
/
create public synonym USER_DIM_JOIN_KEY for USER_DIM_JOIN_KEY
/
grant select on USER_DIM_JOIN_KEY  to PUBLIC with grant option
/

rem The pflags field referenced from sum$ is highly dependent on the 
rem bit combinations of QSMKSUM.
rem **jjf** the privs have to be summary based not tables when implemented

rem
rem Familiy of SUMMARIES views
rem

rem ALL_SUMMARIES

create or replace view ALL_SUMMARIES
    (OWNER, SUMMARY_NAME, CONTAINER_OWNER, CONTAINER_NAME, 
     LAST_REFRESH_SCN, LAST_REFRESH_DATE, REFRESH_METHOD, SUMMARY,
     FULLREFRESHTIM, INCREFRESHTIM,
     CONTAINS_VIEWS, UNUSABLE, RESTRICTED_SYNTAX, INC_REFRESHABLE,
     KNOWN_STALE, QUERY_LEN, QUERY)
as
select u.name, o.name, u.name, s.containernam,
       s.lastrefreshscn, s.lastrefreshdate,
       decode (s.refreshmode, 0, 'NONE', 1, 'ANY', 2, 'INCREMENTAL', 3,'FULL'),
       decode(bitand(s.pflags, 25165824), 25165824, 'N', 'Y'),
       s.fullrefreshtim, s.increfreshtim,
       decode(bitand(s.pflags, 48), 0, 'N', 'Y'),
       decode(bitand(s.mflags, 64), 0, 'N', 'Y'), /* QSMQSUM_UNUSABLE */ 
       decode(bitand(s.pflags, 1294319), 0, 'Y', 'N'), 
       decode(bitand(s.pflags, 236879743), 0, 'Y', 'N'),
       decode(bitand(s.mflags, 1), 0, 'N', 'Y'), /* QSMQSUM_KNOWNSTL */
       s.sumtextlen,s.sumtext
from sys.user$ u, sys.sum$ s, sys.obj$ o
where o.owner# = u.user#
  and o.obj# = s.obj#
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/


comment on table ALL_SUMMARIES is
'Description of the summaries accessible to the user'
/
comment on column ALL_SUMMARIES.OWNER is
'Owner of the summary'
/
comment on column ALL_SUMMARIES.SUMMARY_NAME is
'Name of the summary'
/
comment on column ALL_SUMMARIES.CONTAINER_OWNER is
'Owner of the container table'
/
comment on column ALL_SUMMARIES.CONTAINER_NAME is
'Name of the container table for this summary'
/
comment on column ALL_SUMMARIES.LAST_REFRESH_SCN is
'The SCN of the last transaction to refresh the summary'
/
comment on column ALL_SUMMARIES.LAST_REFRESH_DATE is
'The date of the last refresh of the summary'
/
comment on column ALL_SUMMARIES.REFRESH_METHOD is
'User declared method of refresh for the summary'
/
comment on column ALL_SUMMARIES.FULLREFRESHTIM is
'The time that it took to fully refresh the summary'
/
comment on column ALL_SUMMARIES.INCREFRESHTIM is
'The time that it took to incrementally refresh the summary'
/
comment on column ALL_SUMMARIES.CONTAINS_VIEWS is
'This summary contains views in the FROM clause'
/
comment on column ALL_SUMMARIES.UNUSABLE is
'This summary is unusable, the build was deferred'
/
comment on column ALL_SUMMARIES.RESTRICTED_SYNTAX is
'This summary contains restrictive syntax'
/
comment on column ALL_SUMMARIES.INC_REFRESHABLE is
'This summary is not restricted from being incrementally refreshed'
/
comment on column ALL_SUMMARIES.KNOWN_STALE is
'This summary is directly stale'
/
comment on column ALL_SUMMARIES.SUMMARY is
'Indicates the presence of either aggregation or a GROUP BY'
/
drop public synonym ALL_SUMMARIES
/
create public synonym ALL_SUMMARIES for ALL_SUMMARIES
/
grant select on ALL_SUMMARIES to PUBLIC with grant option
/

rem USER_SUMMARIES

create or replace view USER_SUMMARIES
    (OWNER, SUMMARY_NAME, CONTAINER_OWNER, CONTAINER_NAME, 
     LAST_REFRESH_SCN, LAST_REFRESH_DATE, REFRESH_METHOD, SUMMARY,
     FULLREFRESHTIM, INCREFRESHTIM,
     CONTAINS_VIEWS, UNUSABLE, RESTRICTED_SYNTAX, INC_REFRESHABLE,
     KNOWN_STALE, QUERY_LEN, QUERY)
as
select u.name, o.name, u.name, s.containernam,
       s.lastrefreshscn, s.lastrefreshdate,
       decode (s.refreshmode, 0, 'NONE', 1, 'ANY', 2, 'INCREMENTAL', 3,'FULL'),
       decode(bitand(s.pflags, 25165824), 25165824, 'N', 'Y'),
       s.fullrefreshtim, s.increfreshtim,
       decode(bitand(s.pflags, 48), 0, 'N', 'Y'),
       decode(bitand(s.mflags, 64), 0, 'N', 'Y'), /* QSMQSUM_UNUSABLE */ 
       decode(bitand(s.pflags, 1294319), 0, 'Y', 'N'), 
       decode(bitand(s.pflags, 236879743), 0, 'Y', 'N'),
       decode(bitand(s.mflags, 1), 0, 'N', 'Y'), /* QSMQSUM_KNOWNSTL */
       s.sumtextlen,s.sumtext
from sys.user$ u, sys.sum$ s, sys.obj$ o
where o.owner# = u.user#
  and o.obj# = s.obj#
  and o.owner# = userenv('SCHEMAID')

/
comment on table USER_SUMMARIES is
'Description of the summaries created by the user'
/
comment on column USER_SUMMARIES.OWNER is
'Owner of the summary'
/
comment on column USER_SUMMARIES.SUMMARY_NAME is
'Name of the summary'
/
comment on column USER_SUMMARIES.CONTAINER_OWNER is
'Owner of the container table'
/
comment on column USER_SUMMARIES.CONTAINER_NAME is
'Name of the container table for this summary'
/
comment on column USER_SUMMARIES.LAST_REFRESH_SCN is
'The SCN of the last transaction to refresh the summary'
/
comment on column USER_SUMMARIES.LAST_REFRESH_DATE is
'The date of the last refresh of the summary'
/
comment on column USER_SUMMARIES.REFRESH_METHOD is
'User declared method of refresh for the summary'
/
comment on column USER_SUMMARIES.FULLREFRESHTIM is
'The time that it took to fully refresh the summary'
/
comment on column USER_SUMMARIES.INCREFRESHTIM is
'The time that it took to incrementally refresh the summary'
/
comment on column USER_SUMMARIES.CONTAINS_VIEWS is
'This summary contains views in the FROM clause'
/
comment on column USER_SUMMARIES.UNUSABLE is
'This summary is unusable, the build was deferred'
/
comment on column USER_SUMMARIES.RESTRICTED_SYNTAX is
'This summary contains restrictive syntax'
/
comment on column USER_SUMMARIES.INC_REFRESHABLE is
'This summary is not restricted from being incrementally refreshed'
/
comment on column USER_SUMMARIES.KNOWN_STALE is
'This summary is directly stale'
/
comment on column USER_SUMMARIES.SUMMARY is
'Indicates the presence of either aggregation or a GROUP BY'
/
drop public synonym USER_SUMMARIES
/
create public synonym USER_SUMMARIES for USER_SUMMARIES
/
grant select on USER_SUMMARIES to PUBLIC with grant option
/


rem DBA_SUMMARIES

create or replace view DBA_SUMMARIES
    (OWNER, SUMMARY_NAME, CONTAINER_OWNER, CONTAINER_NAME, 
     LAST_REFRESH_SCN, LAST_REFRESH_DATE, REFRESH_METHOD, SUMMARY,
     FULLREFRESHTIM, INCREFRESHTIM,
     CONTAINS_VIEWS, UNUSABLE, RESTRICTED_SYNTAX, INC_REFRESHABLE,
     KNOWN_STALE, QUERY_LEN, QUERY)
as
select u.name, o.name, u.name, s.containernam,
       s.lastrefreshscn, s.lastrefreshdate,
       decode (s.refreshmode, 0, 'NONE', 1, 'ANY', 2, 'INCREMENTAL', 3,'FULL'),
       decode(bitand(s.pflags, 25165824), 25165824, 'N', 'Y'),
       s.fullrefreshtim, s.increfreshtim,
       decode(bitand(s.pflags, 48), 0, 'N', 'Y'),
       decode(bitand(s.mflags, 64), 0, 'N', 'Y'), /* QSMQSUM_UNUSABLE */ 
       decode(bitand(s.pflags, 1294319), 0, 'Y', 'N'), 
       decode(bitand(s.pflags, 236879743), 0, 'Y', 'N'),
       decode(bitand(s.mflags, 1), 0, 'N', 'Y'), /* QSMQSUM_KNOWNSTL */
       s.sumtextlen,s.sumtext
from sys.user$ u, sys.sum$ s, sys.obj$ o
where o.owner# = u.user#
  and o.obj# = s.obj#
/

comment on table DBA_SUMMARIES is
'Description of the summaries accessible to dba'
/
comment on column DBA_SUMMARIES.OWNER is
'Owner of the summary'
/
comment on column DBA_SUMMARIES.SUMMARY_NAME is
'Name of the summary'
/
comment on column DBA_SUMMARIES.CONTAINER_OWNER is
'Owner of the container table'
/
comment on column DBA_SUMMARIES.CONTAINER_NAME is
'Name of the container table for this summary'
/
comment on column DBA_SUMMARIES.LAST_REFRESH_SCN is
'The SCN of the last transaction to refresh the summary'
/
comment on column DBA_SUMMARIES.LAST_REFRESH_DATE is
'The date of the last refresh of the summary'
/
comment on column DBA_SUMMARIES.REFRESH_METHOD is
'User declared method of refresh for the summary'
/
comment on column DBA_SUMMARIES.FULLREFRESHTIM is
'The time that it took to fully refresh the summary'
/
comment on column DBA_SUMMARIES.INCREFRESHTIM is
'The time that it took to incrementally refresh the summary'
/
comment on column DBA_SUMMARIES.CONTAINS_VIEWS is
'This summary contains views in the FROM clause'
/
comment on column DBA_SUMMARIES.UNUSABLE is
'This summary is unusable, the build was deferred'
/
comment on column DBA_SUMMARIES.RESTRICTED_SYNTAX is
'This summary contains restrictive syntax'
/
comment on column DBA_SUMMARIES.INC_REFRESHABLE is
'This summary is not restricted from being incrementally refreshed'
/
comment on column DBA_SUMMARIES.KNOWN_STALE is
'This summary is directly stale'
/
comment on column DBA_SUMMARIES.SUMMARY is
'Indicates the presence of either aggregation or a GROUP BY'
/
drop public synonym DBA_SUMMARIES
/
create public synonym DBA_SUMMARIES for DBA_SUMMARIES
/
grant select on DBA_SUMMARIES  to select_catalog_role
/

rem
rem Family of SUMMARY_AGGREGATES
rem

rem DBA_SUMMARY_AGGREGATES

create or replace view DBA_SUMMARY_AGGREGATES
  (OWNER, SUMMARY_NAME, POSITION_IN_SELECT, CONTAINER_COLUMN,
   AGG_FUNCTION, DISTINCTFLAG, MEASURE)
as
select u.name, o.name, sa.sumcolpos#, c.name,
       decode(sa.aggfunction, 15, 'AVG', 16, 'SUM', 17, 'COUNT',
                              18, 'MIN', 19, 'MAX',
                              97, 'VARIANCE', 98, 'STDDEV'),
       decode(sa.flags, 0, 'N', 'Y'),
       sa.aggtext
from sys.sumagg$ sa, sys.obj$ o, sys.user$ u, sys.sum$ s, sys.col$ c
where sa.sumobj# = o.obj#
  AND o.owner# = u.user#
  AND sa.sumobj# = s.obj# 
  AND c.obj# = s.containerobj#
  AND c.col# = sa.containercol#
/
REM comment on table DBA_SUMMARY_AGGREGATES is
REM 'Description of the summary aggregates accessible to dba'
REM /
comment on column DBA_SUMMARY_AGGREGATES.OWNER is
'Owner of the summary'
/
comment on column DBA_SUMMARY_AGGREGATES.SUMMARY_NAME is
'Name of the summary'
/
comment on column DBA_SUMMARY_AGGREGATES.POSITION_IN_SELECT is
'Position of this aggregated measure with the SELECT list'
/
comment on column DBA_SUMMARY_AGGREGATES.CONTAINER_COLUMN is
'Name of this column in the container table'
/
comment on column DBA_SUMMARY_AGGREGATES.AGG_FUNCTION is
'Name of the aggregation function, one of the following:
COUNT, SUM, MIN, MAX, AVG, VARIANCE, STDDEV'
/
comment on column DBA_SUMMARY_AGGREGATES.DISTINCTFLAG is
'Set to Y is this is a DISTINCT aggregation'
/
comment on column DBA_SUMMARY_AGGREGATES.MEASURE is
'The SQL text of the measure, excluding the aggregation function'
/
drop public synonym DBA_SUMMARY_AGGREGATES
/
create public synonym DBA_SUMMARY_AGGREGATES for DBA_SUMMARY_AGGREGATES
/
grant select on DBA_SUMMARY_AGGREGATES  to select_catalog_role
/

rem USER_SUMMARY_AGGREGATES

create or replace view USER_SUMMARY_AGGREGATES
  (OWNER, SUMMARY_NAME, POSITION_IN_SELECT, CONTAINER_COLUMN,
   AGG_FUNCTION, DISTINCTFLAG, MEASURE)
as
select u.name, o.name, sa.sumcolpos#, c.name,
       decode(sa.aggfunction, 15, 'AVG', 16, 'SUM', 17, 'COUNT',
                              18, 'MIN', 19, 'MAX',
                              97, 'VARIANCE', 98, 'STDDEV'),
       decode(sa.flags, 0, 'N', 'Y'),
       sa.aggtext
from sys.sumagg$ sa, sys.obj$ o, sys.user$ u, sys.sum$ s, sys.col$ c
where sa.sumobj# = o.obj#
  AND o.owner# = u.user#
  AND sa.sumobj# = s.obj# 
  AND c.obj# = s.containerobj#
  AND c.col# = sa.containercol#
  AND o.owner# = userenv('SCHEMAID')
/
REM comment on table USER_SUMMARY_AGGREGATES is
REM 'Description of the summary aggregates created by the user'
REM /
comment on column USER_SUMMARY_AGGREGATES.OWNER is
'Owner of the summary'
/
comment on column USER_SUMMARY_AGGREGATES.SUMMARY_NAME is
'Name of the summary'
/
comment on column USER_SUMMARY_AGGREGATES.POSITION_IN_SELECT is
'Position of this aggregated measure with the SELECT list'
/
comment on column USER_SUMMARY_AGGREGATES.CONTAINER_COLUMN is
'Name of this column in the container table'
/
comment on column USER_SUMMARY_AGGREGATES.AGG_FUNCTION is
'Name of the aggregation function, one of the following:
COUNT, SUM, MIN, MAX, AVG, VARIANCE, STDDEV'
/
comment on column USER_SUMMARY_AGGREGATES.DISTINCTFLAG is
'Set to Y is this is a DISTINCT aggregation'
/
comment on column USER_SUMMARY_AGGREGATES.MEASURE is
'The SQL text of the measure, excluding the aggregation function'
/
drop public synonym USER_SUMMARY_AGGREGATES
/
create public synonym USER_SUMMARY_AGGREGATES for USER_SUMMARY_AGGREGATES
/
grant select on USER_SUMMARY_AGGREGATES  to PUBLIC with grant option 
/


rem ALL_SUMMARY_AGGREGATES

create or replace view ALL_SUMMARY_AGGREGATES
  (OWNER, SUMMARY_NAME, POSITION_IN_SELECT, CONTAINER_COLUMN,
   AGG_FUNCTION, DISTINCTFLAG, MEASURE)
as
select u.name, o.name, sa.sumcolpos#, c.name,
       decode(sa.aggfunction, 15, 'AVG', 16, 'SUM', 17, 'COUNT',
                              18, 'MIN', 19, 'MAX',
                              97, 'VARIANCE', 98, 'STDDEV'),
       decode(sa.flags, 0, 'N', 'Y'),
       sa.aggtext
from sys.sumagg$ sa, sys.obj$ o, sys.user$ u, sys.sum$ s, sys.col$ c
where sa.sumobj# = o.obj#
  AND o.owner# = u.user#
  AND sa.sumobj# = s.obj# 
  AND c.obj# = s.containerobj#
  AND c.col# = sa.containercol#
  AND (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/

REM comment on table ALL_SUMMARY_AGGREGATES is
REM 'Description of the summary aggregates accessible to the user'
REM /
comment on column ALL_SUMMARY_AGGREGATES.OWNER is
'Owner of the summary'
/
comment on column ALL_SUMMARY_AGGREGATES.SUMMARY_NAME is
'Name of the summary'
/
comment on column ALL_SUMMARY_AGGREGATES.POSITION_IN_SELECT is
'Position of this aggregated measure with the SELECT list'
/
comment on column ALL_SUMMARY_AGGREGATES.CONTAINER_COLUMN is
'Name of this column in the container table'
/
comment on column ALL_SUMMARY_AGGREGATES.AGG_FUNCTION is
'Name of the aggregation function, one of the following:
COUNT, SUM, MIN, MAX, AVG, VARIANCE, STDDEV'
/
comment on column ALL_SUMMARY_AGGREGATES.DISTINCTFLAG is
'Set to Y is this is a DISTINCT aggregation'
/
comment on column ALL_SUMMARY_AGGREGATES.MEASURE is
'The SQL text of the measure, excluding the aggregation function'
/
drop public synonym ALL_SUMMARY_AGGREGATES
/
create public synonym ALL_SUMMARY_AGGREGATES for ALL_SUMMARY_AGGREGATES
/
grant select on ALL_SUMMARY_AGGREGATES  to PUBLIC with grant option 
/


rem
rem Family of SUMMARY_DETAIL_TABLES
rem

rem ALL_SUMMARY_DETAIL_TABLES

create or replace view ALL_SUMMARY_DETAIL_TABLES
    (OWNER, SUMMARY_NAME, DETAIL_OWNER, DETAIL_RELATION, DETAIL_TYPE, 
     DETAIL_ALIAS)
as
select u.name, o.name, du.name,  do.name,
       decode (sd.detailobjtype, 1, 'TABLE', 2, 'VIEW',
                                3, 'SNAPSHOT', 4, 'CONTAINER', 'UNDEFINED'),
       sd.detailalias
from sys.user$ u, sys.sumdetail$ sd, sys.obj$ o, sys.obj$ do, 
sys.user$ du
where o.owner# = u.user#
  and o.obj# = sd.sumobj#
  and do.obj# = sd.detailobj#
  and do.owner# = du.user#
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
REM comment on table ALL_SUMMARY_DETAIL_TABLES is
REM 'Description of the summary detail tables accessible to the user'
REM /
comment on column ALL_SUMMARY_DETAIL_TABLES.OWNER is
'Owner of the summary'
/
comment on column ALL_SUMMARY_DETAIL_TABLES.SUMMARY_NAME is
'Name of the summary'
/
comment on column ALL_SUMMARY_DETAIL_TABLES.DETAIL_OWNER is
'Owner of the detail relation'
/
comment on column ALL_SUMMARY_DETAIL_TABLES.DETAIL_RELATION is
'Name of the summary detail table'
/
comment on column ALL_SUMMARY_DETAIL_TABLES.DETAIL_TYPE is
'Type of summary detail table type'
/
comment on column ALL_SUMMARY_DETAIL_TABLES.DETAIL_ALIAS is
'Alias of the detail relation if used'

/
drop public synonym ALL_SUMMARY_DETAIL_TABLES
/
create public synonym ALL_SUMMARY_DETAIL_TABLES for
    ALL_SUMMARY_DETAIL_TABLES
/
grant select on ALL_SUMMARY_DETAIL_TABLES to PUBLIC with grant option
/

rem USER_SUMMARY_DETAIL_TABLES

create or replace view USER_SUMMARY_DETAIL_TABLES
    (OWNER, SUMMARY_NAME, DETAIL_OWNER, DETAIL_RELATION, DETAIL_TYPE, 
     DETAIL_ALIAS)
as
select u.name, o.name, du.name,  do.name,
       decode (sd.detailobjtype, 1, 'TABLE', 2, 'VIEW',
                                3, 'SNAPSHOT', 4, 'CONTAINER', 'UNDEFINED'),
       sd.detailalias
from sys.user$ u, sys.sumdetail$ sd, sys.obj$ o, sys.obj$ do, 
sys.user$ du
where o.owner# = u.user#
  and o.obj# = sd.sumobj#
  and do.obj# = sd.detailobj#
  and do.owner# = du.user#
  and o.owner# = userenv('SCHEMAID')
/
REM comment on table USER_SUMMARY_DETAIL_TABLES is
REM 'Description of the summary detail tables of the summaries created by the user'
REM /
comment on column USER_SUMMARY_DETAIL_TABLES.OWNER is
'Owner of the summary'
/
comment on column USER_SUMMARY_DETAIL_TABLES.SUMMARY_NAME is
'Name of the summary'
/
comment on column USER_SUMMARY_DETAIL_TABLES.DETAIL_OWNER is
'Owner of the detail relation'
/
comment on column USER_SUMMARY_DETAIL_TABLES.DETAIL_RELATION is
'Name of the summary detail table'
/
comment on column USER_SUMMARY_DETAIL_TABLES.DETAIL_TYPE is
'Type of summary detail table type'
/
comment on column USER_SUMMARY_DETAIL_TABLES.DETAIL_ALIAS is
'Alias of the detail relation if used'

/
drop public synonym USER_SUMMARY_DETAIL_TABLES
/
create public synonym USER_SUMMARY_DETAIL_TABLES for
    USER_SUMMARY_DETAIL_TABLES
/
grant select on USER_SUMMARY_DETAIL_TABLES to PUBLIC with grant option
/

rem DBA_SUMMARY_DETAIL_TABLES

create or replace view DBA_SUMMARY_DETAIL_TABLES
    (OWNER, SUMMARY_NAME, DETAIL_OWNER, DETAIL_RELATION, DETAIL_TYPE, 
     DETAIL_ALIAS)
as
select u.name, o.name, du.name,  do.name,
       decode (sd.detailobjtype, 1, 'TABLE', 2, 'VIEW',
                                3, 'SNAPSHOT', 4, 'CONTAINER', 'UNDEFINED'),
       sd.detailalias
from sys.user$ u, sys.sumdetail$ sd, sys.obj$ o, sys.obj$ do, 
     sys.user$ du
where o.owner# = u.user#
  and o.obj# = sd.sumobj#
  and do.obj# = sd.detailobj#
  and do.owner# = du.user#

/
REM comment on table DBA_SUMMARY_DETAIL_TABLES is
REM 'Description of the summary detail tables accessible to dba'
REM /
comment on column DBA_SUMMARY_DETAIL_TABLES.OWNER is
'Owner of the summary'
/
comment on column DBA_SUMMARY_DETAIL_TABLES.SUMMARY_NAME is
'Name of the summary'
/
comment on column DBA_SUMMARY_DETAIL_TABLES.DETAIL_OWNER is
'Owner of the detail relation'
/
comment on column DBA_SUMMARY_DETAIL_TABLES.DETAIL_RELATION is
'Name of the summary detail table'
/
comment on column DBA_SUMMARY_DETAIL_TABLES.DETAIL_TYPE is
'Type of summary detail table type'
/
comment on column DBA_SUMMARY_DETAIL_TABLES.DETAIL_ALIAS is
'Alias of the detail relation if used'
/
drop public synonym DBA_SUMMARY_DETAIL_TABLES
/
create public synonym DBA_SUMMARY_DETAIL_TABLES for DBA_SUMMARY_DETAIL_TABLES
/
grant select on DBA_SUMMARY_DETAIL_TABLES  to select_catalog_role
/

rem
rem FAMILY of SUMMARY_KEYS
rem

rem DBA_SUMMARY_KEYS

create or replace view DBA_SUMMARY_KEYS
  (OWNER, SUMMARY_NAME, POSITION_IN_SELECT, CONTAINER_COLUMN,
   DETAILOBJ_OWNER, DETAILOBJ_NAME, DETAILOBJ_ALIAS,
   DETAILOBJ_TYPE, DETAILOBJ_COLUMN)
as 
select u1.name, o1.name, sk.sumcolpos#, c1.name,
       u2.name, o2.name, sd.detailalias,
       decode(sk.detailobjtype, 1, 'TABLE', 2, 'VIEW'), c2.name
from sys.sumkey$ sk, sys.obj$ o1, sys.user$ u1, sys.col$ c1, sys.sum$ s, 
     sys.sumdetail$ sd, sys.obj$ o2, sys.user$ u2, sys.col$ c2
where sk.sumobj# = o1.obj#
  AND o1.owner# = u1.user#
  AND sk.sumobj# = s.obj#
  AND s.containerobj# = c1.obj#
  AND c1.col# = sk.containercol#
  AND sk.detailobj# = o2.obj#
  AND o2.owner# = u2.user#
  AND sk.sumobj# = sd.sumobj#
  AND sk.detailobj# = sd.detailobj#
  AND sk.detailobj# = c2.obj#
  AND sk.detailcol# = c2.intcol#
/
REM comment on table DBA_SUMMARY_KEYS is
REM 'Description of the columns that appear in the GROUP BY
REM list of a summary accessible to dba'
REM /
comment on column DBA_SUMMARY_KEYS.OWNER is
'Owner of the summary'
/
comment on column DBA_SUMMARY_KEYS.SUMMARY_NAME is
'Name of the summary'
/
comment on column DBA_SUMMARY_KEYS.POSITION_IN_SELECT is
'Position of this key within the SELECT list'
/
comment on column DBA_SUMMARY_KEYS.CONTAINER_COLUMN is
'Name of the column in the container table'
/
comment on column DBA_SUMMARY_KEYS.DETAILOBJ_OWNER is
'Owner of the detail object'
/
comment on column DBA_SUMMARY_KEYS.DETAILOBJ_NAME is
'Name of the detail object'
/
comment on column DBA_SUMMARY_KEYS.DETAILOBJ_ALIAS is
'Alias of the detail object'
/
comment on column DBA_SUMMARY_KEYS.DETAILOBJ_TYPE is
'Type of the detail object: VIEW or TABLE'
/
comment on column DBA_SUMMARY_KEYS.DETAILOBJ_COLUMN is
'Name of the detail object column'

/
drop public synonym DBA_SUMMARY_KEYS
/
create public synonym DBA_SUMMARY_KEYS for DBA_SUMMARY_KEYS
/
grant select on DBA_SUMMARY_KEYS to select_catalog_role
/

rem ALL_SUMMARY_KEYS

create or replace view ALL_SUMMARY_KEYS
  (OWNER, SUMMARY_NAME, POSITION_IN_SELECT, CONTAINER_COLUMN,
   DETAILOBJ_OWNER, DETAILOBJ_NAME, DETAILOBJ_ALIAS,
   DETAILOBJ_TYPE, DETAILOBJ_COLUMN)
as 
select u1.name, o1.name, sk.sumcolpos#, c1.name,
       u2.name, o2.name, sd.detailalias,
       decode(sk.detailobjtype, 1, 'TABLE', 2, 'VIEW'), c2.name
from sys.sumkey$ sk, sys.obj$ o1, sys.user$ u1, sys.col$ c1, sys.sum$ s, 
     sys.sumdetail$ sd, sys.obj$ o2, sys.user$ u2, sys.col$ c2
where sk.sumobj# = o1.obj#
  AND o1.owner# = u1.user#
  AND sk.sumobj# = s.obj#
  AND s.containerobj# = c1.obj#
  AND c1.col# = sk.containercol#
  AND sk.detailobj# = o2.obj#
  AND o2.owner# = u2.user#
  AND sk.sumobj# = sd.sumobj#
  AND sk.detailobj# = sd.detailobj#
  AND sk.detailobj# = c2.obj#
  AND sk.detailcol# = c2.intcol#
  AND (o1.owner# = userenv('SCHEMAID')
       or o1.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
REM comment on table ALL_SUMMARY_KEYS is
REM 'Description of the columns that appear in the GROUP BY
REM list of a summary accessible to the user'
REM /
comment on column ALL_SUMMARY_KEYS.OWNER is
'Owner of the summary'
/
comment on column ALL_SUMMARY_KEYS.SUMMARY_NAME is
'Name of the summary'
/
comment on column ALL_SUMMARY_KEYS.POSITION_IN_SELECT is
'Position of this key within the SELECT list'
/
comment on column ALL_SUMMARY_KEYS.CONTAINER_COLUMN is
'Name of the column in the container table'
/
comment on column ALL_SUMMARY_KEYS.DETAILOBJ_OWNER is
'Owner of the detail object'
/
comment on column ALL_SUMMARY_KEYS.DETAILOBJ_NAME is
'Name of the detail object'
/
comment on column ALL_SUMMARY_KEYS.DETAILOBJ_ALIAS is
'Alias of the detail object'
/
comment on column ALL_SUMMARY_KEYS.DETAILOBJ_TYPE is
'Type of the detail object: VIEW or TABLE'
/
comment on column ALL_SUMMARY_KEYS.DETAILOBJ_COLUMN is
'Name of the detail object column'

/
drop public synonym ALL_SUMMARY_KEYS
/
create public synonym ALL_SUMMARY_KEYS for ALL_SUMMARY_KEYS
/
grant select on ALL_SUMMARY_KEYS to PUBLIC with grant option
/

rem USER_SUMMARY_KEYS

create or replace view USER_SUMMARY_KEYS
  (OWNER, SUMMARY_NAME, POSITION_IN_SELECT, CONTAINER_COLUMN,
   DETAILOBJ_OWNER, DETAILOBJ_NAME, DETAILOBJ_ALIAS,
   DETAILOBJ_TYPE, DETAILOBJ_COLUMN)
as 
select u1.name, o1.name, sk.sumcolpos#, c1.name,
       u2.name, o2.name, sd.detailalias,
       decode(sk.detailobjtype, 1, 'TABLE', 2, 'VIEW'), c2.name
from sys.sumkey$ sk, sys.obj$ o1, sys.user$ u1, sys.col$ c1, sys.sum$ s, 
     sys.sumdetail$ sd, sys.obj$ o2, sys.user$ u2, sys.col$ c2
where sk.sumobj# = o1.obj#
  AND o1.owner# = u1.user#
  AND sk.sumobj# = s.obj#
  AND s.containerobj# = c1.obj#
  AND c1.col# = sk.containercol#
  AND sk.detailobj# = o2.obj#
  AND o2.owner# = u2.user#
  AND sk.sumobj# = sd.sumobj#
  AND sk.detailobj# = sd.detailobj#
  AND sk.detailobj# = c2.obj#
  AND sk.detailcol# = c2.intcol#
  AND o1.owner# = userenv('SCHEMAID')
/
REM comment on table USER_SUMMARY_KEYS is
REM 'Description of the columns that appear in the GROUP BY
REM list of a summary  created by the user'
REM /
comment on column USER_SUMMARY_KEYS.OWNER is
'Owner of the summary'
/
comment on column USER_SUMMARY_KEYS.SUMMARY_NAME is
'Name of the summary'
/
comment on column USER_SUMMARY_KEYS.POSITION_IN_SELECT is
'Position of this key within the SELECT list'
/
comment on column USER_SUMMARY_KEYS.CONTAINER_COLUMN is
'Name of the column in the container table'
/
comment on column USER_SUMMARY_KEYS.DETAILOBJ_OWNER is
'Owner of the detail object'
/
comment on column USER_SUMMARY_KEYS.DETAILOBJ_NAME is
'Name of the detail object'
/
comment on column USER_SUMMARY_KEYS.DETAILOBJ_ALIAS is
'Alias of the detail object'
/
comment on column USER_SUMMARY_KEYS.DETAILOBJ_TYPE is
'Type of the detail object: VIEW or TABLE'
/
comment on column USER_SUMMARY_KEYS.DETAILOBJ_COLUMN is
'Name of the detail object column'

/
drop public synonym USER_SUMMARY_KEYS
/
create public synonym USER_SUMMARY_KEYS for USER_SUMMARY_KEYS
/
grant select on USER_SUMMARY_KEYS to PUBLIC with grant option
/


rem
rem FAMILY of SUMMARY_JOINS
rem

rem DBA_SUMMARY_JOINS

create or replace view DBA_SUMMARY_JOINS
  (OWNER, SUMMARY_NAME, 
  DETAILOBJ1_OWNER, DETAILOBJ1_RELATION, DETAILOBJ1_COLUMN, OPERATOR,
  DETAILOBJ2_OWNER, DETAILOBJ2_RELATION, DETAILOBJ2_COLUMN)
as
select u.name, o.name, 
       u1.name, o1.name, c1.name, '=',
       u2.name, o2.name, c2.name
from sys.sumjoin$ sj, sys.obj$ o, sys.user$ u,
     sys.obj$ o1, sys.user$ u1, sys.col$ c1,
     sys.obj$ o2, sys.user$ u2, sys.col$ c2
where sj.sumobj# = o.obj#
  AND o.owner# = u.user#
  AND sj.tab1obj# = o1.obj#
  AND o1.owner# = u1.user#
  AND sj.tab1obj# = c1.obj#
  AND sj.tab1col# = c1.intcol#
  AND sj.tab2obj# = o2.obj#
  AND o2.owner# = u2.user#
  AND sj.tab2obj# = c2.obj#
  AND sj.tab2col# = c2.intcol#
/
REM comment on table DBA_SUMMARY_JOINS is
REM 'Description of a join between two columns in the
REM WHERE clause of a summary accessible to dba'
REM /
comment on column DBA_SUMMARY_JOINS.OWNER is
'Owner of the summary'
/
comment on column DBA_SUMMARY_JOINS.SUMMARY_NAME is
'Name of the summary'
/
comment on column DBA_SUMMARY_JOINS.DETAILOBJ1_OWNER is
'Owner of the 1st detail object'
/
comment on column DBA_SUMMARY_JOINS.DETAILOBJ1_RELATION is
'Name of the 1st detail object'
/
comment on column DBA_SUMMARY_JOINS. DETAILOBJ1_COLUMN is
'Name of the 1st detail object column'
/
comment on column DBA_SUMMARY_JOINS.OPERATOR is
'Name of the join operator. Currently only = is defined'
/
comment on column DBA_SUMMARY_JOINS.DETAILOBJ2_OWNER is
'Owner of the 2nd detail object'
/
comment on column DBA_SUMMARY_JOINS.DETAILOBJ2_RELATION is
'Name of the 2nd detail object'
/
comment on column DBA_SUMMARY_JOINS.DETAILOBJ2_COLUMN is
'Name of the 2nd detail object column'
/
drop public synonym DBA_SUMMARY_JOINS
/
create public synonym DBA_SUMMARY_JOINS for DBA_SUMMARY_JOINS
/
grant select on DBA_SUMMARY_JOINS to select_catalog_role
/

rem ALL_SUMMARY_JOINS

create or replace view ALL_SUMMARY_JOINS
  (OWNER, SUMMARY_NAME, 
  DETAILOBJ1_OWNER, DETAILOBJ1_RELATION, DETAILOBJ1_COLUMN, OPERATOR,
  DETAILOBJ2_OWNER, DETAILOBJ2_RELATION, DETAILOBJ2_COLUMN)
as
select u.name, o.name, 
       u1.name, o1.name, c1.name, '=',
       u2.name, o2.name, c2.name
from sys.sumjoin$ sj, sys.obj$ o, sys.user$ u,
     sys.obj$ o1, sys.user$ u1, sys.col$ c1,
     sys.obj$ o2, sys.user$ u2, sys.col$ c2
where sj.sumobj# = o.obj#
  AND o.owner# = u.user#
  AND sj.tab1obj# = o1.obj#
  AND o1.owner# = u1.user#
  AND sj.tab1obj# = c1.obj#
  AND sj.tab1col# = c1.intcol#
  AND sj.tab2obj# = o2.obj#
  AND o2.owner# = u2.user#
  AND sj.tab2obj# = c2.obj#
  AND sj.tab2col# = c2.intcol#
  AND (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
REM comment on table ALL_SUMMARY_JOINS is
REM 'Description of a join between two columns in the
REM WHERE clause of a summary accessible to the user'
REM /
comment on column ALL_SUMMARY_JOINS.OWNER is
'Owner of the summary'
/
comment on column ALL_SUMMARY_JOINS.SUMMARY_NAME is
'Name of the summary'
/
comment on column ALL_SUMMARY_JOINS.DETAILOBJ1_OWNER is
'Owner of the 1st detail object'
/
comment on column ALL_SUMMARY_JOINS.DETAILOBJ1_RELATION is
'Name of the 1st detail object'
/
comment on column ALL_SUMMARY_JOINS. DETAILOBJ1_COLUMN is
'Name of the 1st detail object column'
/
comment on column ALL_SUMMARY_JOINS.OPERATOR is
'Name of the join operator. Currently only = is defined'
/
comment on column ALL_SUMMARY_JOINS.DETAILOBJ2_OWNER is
'Owner of the 2nd detail object'
/
comment on column ALL_SUMMARY_JOINS.DETAILOBJ2_RELATION is
'Name of the 2nd detail object'
/
comment on column ALL_SUMMARY_JOINS.DETAILOBJ2_COLUMN is
'Name of the 2nd detail object column'
/
drop public synonym ALL_SUMMARY_JOINS
/
create public synonym ALL_SUMMARY_JOINS for ALL_SUMMARY_JOINS
/
grant select on ALL_SUMMARY_JOINS to PUBLIC with grant option
/


rem USER_SUMMARY_JOINS

create or replace view USER_SUMMARY_JOINS
  (OWNER, SUMMARY_NAME, 
  DETAILOBJ1_OWNER, DETAILOBJ1_RELATION, DETAILOBJ1_COLUMN, OPERATOR,
  DETAILOBJ2_OWNER, DETAILOBJ2_RELATION, DETAILOBJ2_COLUMN)
as
select u.name, o.name, 
       u1.name, o1.name, c1.name, '=',
       u2.name, o2.name, c2.name
from sys.sumjoin$ sj, sys.obj$ o, sys.user$ u,
     sys.obj$ o1, sys.user$ u1, sys.col$ c1,
     sys.obj$ o2, sys.user$ u2, sys.col$ c2
where sj.sumobj# = o.obj#
  AND o.owner# = u.user#
  AND sj.tab1obj# = o1.obj#
  AND o1.owner# = u1.user#
  AND sj.tab1obj# = c1.obj#
  AND sj.tab1col# = c1.intcol#
  AND sj.tab2obj# = o2.obj#
  AND o2.owner# = u2.user#
  AND sj.tab2obj# = c2.obj#
  AND sj.tab2col# = c2.intcol#
  AND o.owner# = userenv('SCHEMAID')
/
REM comment on table USER_SUMMARY_JOINS is
REM 'Description of a join between two columns in the
REM WHERE clause of a summary created by the user'
REM /
comment on column USER_SUMMARY_JOINS.OWNER is
'Owner of the summary'
/
comment on column USER_SUMMARY_JOINS.SUMMARY_NAME is
'Name of the summary'
/
comment on column USER_SUMMARY_JOINS.DETAILOBJ1_OWNER is
'Owner of the 1st detail object'
/
comment on column USER_SUMMARY_JOINS.DETAILOBJ1_RELATION is
'Name of the 1st detail object'
/
comment on column USER_SUMMARY_JOINS. DETAILOBJ1_COLUMN is
'Name of the 1st detail object column'
/
comment on column USER_SUMMARY_JOINS.OPERATOR is
'Name of the join operator. Currently only = is defined'
/
comment on column USER_SUMMARY_JOINS.DETAILOBJ2_OWNER is
'Owner of the 2nd detail object'
/
comment on column USER_SUMMARY_JOINS.DETAILOBJ2_RELATION is
'Name of the 2nd detail object'
/
comment on column USER_SUMMARY_JOINS.DETAILOBJ2_COLUMN is
'Name of the 2nd detail object column'
/
drop public synonym USER_SUMMARY_JOINS
/
create public synonym USER_SUMMARY_JOINS for USER_SUMMARY_JOINS
/
grant select on USER_SUMMARY_JOINS to PUBLIC with grant option
/

rem aw1>

rem
rem Familiy of MVIEW_ANALYSIS views
rem

rem ALL_MVIEW_ANALYSIS

create or replace view ALL_MVIEW_ANALYSIS
    (OWNER, MVIEW_NAME, MVIEW_TABLE_OWNER, CONTAINER_NAME, 
     LAST_REFRESH_SCN, LAST_REFRESH_DATE, REFRESH_METHOD, SUMMARY,
     FULLREFRESHTIM, INCREFRESHTIM,
     CONTAINS_VIEWS, UNUSABLE, RESTRICTED_SYNTAX, INC_REFRESHABLE,
     KNOWN_STALE, INVALID, REWRITE_ENABLED, QUERY_LEN, QUERY, REVISION)
as
select u.name, o.name, u.name, s.containernam,
       s.lastrefreshscn, s.lastrefreshdate,
       decode (s.refreshmode, 0, 'NEVER', 1, 'FORCE', 2, 'FAST', 3,'COMPLETE'),
       decode(bitand(s.pflags, 25165824), 25165824, 'N', 'Y'),
       s.fullrefreshtim, s.increfreshtim,
       decode(bitand(s.pflags, 48), 0, 'N', 'Y'),
       decode(bitand(s.mflags, 64), 0, 'N', 'Y'), /* QSMQSUM_UNUSABLE */ 
       decode(bitand(s.pflags, 1294319), 0, 'Y', 'N'), 
       decode(bitand(s.pflags, 236879743), 0, 'Y', 'N'),
       decode(bitand(s.mflags, 1), 0, 'N', 'Y'), /* QSMQSUM_KNOWNSTL */
       decode(o.status, 5, 'Y', 'N'),
       decode(bitand(s.mflags, 4), 0, 'Y', 'N'), /* QSMQSUM_DISABLED */
       s.sumtextlen,s.sumtext,
       1/* Metadata revision number */
from sys.user$ u, sys.sum$ s, sys.obj$ o
where o.owner# = u.user#
  and o.obj# = s.obj#
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/


comment on table ALL_MVIEW_ANALYSIS is
'Description of the materialized views accessible to the user'
/
comment on column ALL_MVIEW_ANALYSIS.OWNER is
'Owner of the materialized view'
/
comment on column ALL_MVIEW_ANALYSIS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column ALL_MVIEW_ANALYSIS.MVIEW_TABLE_OWNER is
'Owner of the container table'
/
comment on column ALL_MVIEW_ANALYSIS.CONTAINER_NAME is
'Name of the container table for this materialized view'
/
comment on column ALL_MVIEW_ANALYSIS.LAST_REFRESH_SCN is
'The SCN of the last transaction to refresh the materialized view'
/
comment on column ALL_MVIEW_ANALYSIS.LAST_REFRESH_DATE is
'The date of the last refresh of the materialized view'
/
comment on column ALL_MVIEW_ANALYSIS.REFRESH_METHOD is
'User declared method of refresh for the materialized view'
/
comment on column ALL_MVIEW_ANALYSIS.SUMMARY is
'Indicates if the materialized view includes the GROUP BY clause'
/
comment on column ALL_MVIEW_ANALYSIS.FULLREFRESHTIM is
'The time that it took to fully refresh the materialized view'
/
comment on column ALL_MVIEW_ANALYSIS.INCREFRESHTIM is
'The time that it took to incrementally refresh the materialized view'
/
comment on column ALL_MVIEW_ANALYSIS.CONTAINS_VIEWS is
'This materialized view contains views in the FROM clause'
/
comment on column ALL_MVIEW_ANALYSIS.UNUSABLE is
'This materialized view is unusable, the build was deferred'
/
comment on column ALL_MVIEW_ANALYSIS.RESTRICTED_SYNTAX is
'This materialized view contains restrictive syntax'
/
comment on column ALL_MVIEW_ANALYSIS.INC_REFRESHABLE is
'This materialized view is not restricted from being incrementally refreshed'
/
comment on column ALL_MVIEW_ANALYSIS.KNOWN_STALE is
'This materialized view is directly stale'
/
comment on column ALL_MVIEW_ANALYSIS.INVALID is
'Invalidity of the materialized view, Y = INVALID, N = VALID'
/
comment on column ALL_MVIEW_ANALYSIS.REWRITE_ENABLED is
'This materialized view is enabled for query rewrite'
/
drop public synonym ALL_MVIEW_ANALYSIS
/
create public synonym ALL_MVIEW_ANALYSIS for ALL_MVIEW_ANALYSIS
/
grant select on ALL_MVIEW_ANALYSIS to PUBLIC with grant option
/

rem USER_MVIEW_ANALYSIS

create or replace view USER_MVIEW_ANALYSIS
    (OWNER, MVIEW_NAME, MVIEW_TABLE_OWNER, CONTAINER_NAME, 
     LAST_REFRESH_SCN, LAST_REFRESH_DATE, REFRESH_METHOD, SUMMARY,
     FULLREFRESHTIM, INCREFRESHTIM,
     CONTAINS_VIEWS, UNUSABLE, RESTRICTED_SYNTAX, INC_REFRESHABLE,
     KNOWN_STALE,  INVALID, REWRITE_ENABLED, QUERY_LEN, QUERY, REVISION)
as
select u.name, o.name, u.name, s.containernam,
       s.lastrefreshscn, s.lastrefreshdate,
       decode (s.refreshmode, 0, 'NEVER', 1, 'FORCE', 2, 'FAST', 3,'COMPLETE'),
       decode(bitand(s.pflags, 25165824), 25165824, 'N', 'Y'),
       s.fullrefreshtim, s.increfreshtim,
       decode(bitand(s.pflags, 48), 0, 'N', 'Y'),
       decode(bitand(s.mflags, 64), 0, 'N', 'Y'), /* QSMQSUM_UNUSABLE */ 
       decode(bitand(s.pflags, 1294319), 0, 'Y', 'N'), 
       decode(bitand(s.pflags, 236879743), 0, 'Y', 'N'),
       decode(bitand(s.mflags, 1), 0, 'N', 'Y'), /* QSMQSUM_KNOWNSTL */
       decode(o.status, 5, 'Y', 'N'),
       decode(bitand(s.mflags, 4), 0, 'Y', 'N'), /* QSMQSUM_DISABLED */
       s.sumtextlen,s.sumtext,
       1/* Metadata revision number */
from sys.user$ u, sys.sum$ s, sys.obj$ o
where o.owner# = u.user#
  and o.obj# = s.obj#
  and o.owner# = userenv('SCHEMAID')

/
comment on table USER_MVIEW_ANALYSIS is
'Description of the materialized views created by the user'
/
comment on column USER_MVIEW_ANALYSIS.OWNER is
'Owner of the materialized view'
/
comment on column USER_MVIEW_ANALYSIS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column USER_MVIEW_ANALYSIS.MVIEW_TABLE_OWNER is
'Owner of the container table'
/
comment on column USER_MVIEW_ANALYSIS.CONTAINER_NAME is
'Name of the container table for this materialized view'
/
comment on column USER_MVIEW_ANALYSIS.LAST_REFRESH_SCN is
'The SCN of the last transaction to refresh the materialized view'
/
comment on column USER_MVIEW_ANALYSIS.LAST_REFRESH_DATE is
'The date of the last refresh of the materialized view'
/
comment on column USER_MVIEW_ANALYSIS.REFRESH_METHOD is
'User declared method of refresh for the materialized view'
/
comment on column ALL_MVIEW_ANALYSIS.SUMMARY is
'Indicates if the materialized view includes the GROUP BY clause'
/
comment on column USER_MVIEW_ANALYSIS.FULLREFRESHTIM is
'The time that it took to fully refresh the materialized view'
/
comment on column USER_MVIEW_ANALYSIS.INCREFRESHTIM is
'The time that it took to incrementally refresh the materialized view'
/
comment on column USER_MVIEW_ANALYSIS.CONTAINS_VIEWS is
'This materialized view contains views in the FROM clause'
/
comment on column USER_MVIEW_ANALYSIS.UNUSABLE is
'This materialized view is unusable, the build was deferred'
/
comment on column USER_MVIEW_ANALYSIS.RESTRICTED_SYNTAX is
'This materialized view contains restrictive syntax'
/
comment on column USER_MVIEW_ANALYSIS.INC_REFRESHABLE is
'This materialized view is not restricted from being incrementally refreshed'
/
comment on column USER_MVIEW_ANALYSIS.KNOWN_STALE is
'This materialized view is directly stale'
/
comment on column USER_MVIEW_ANALYSIS.INVALID is
'Invalidity of the materialized view, Y = INVALID, N = VALID'
/
comment on column USER_MVIEW_ANALYSIS.REWRITE_ENABLED is
'This materialized view is enabled for query rewrite'
/
drop public synonym USER_MVIEW_ANALYSIS
/
create public synonym USER_MVIEW_ANALYSIS for USER_MVIEW_ANALYSIS
/
grant select on USER_MVIEW_ANALYSIS to PUBLIC with grant option
/


rem DBA_MVIEW_ANALYSIS

create or replace view DBA_MVIEW_ANALYSIS
    (OWNER, MVIEW_NAME, MVIEW_TABLE_OWNER, CONTAINER_NAME, 
     LAST_REFRESH_SCN, LAST_REFRESH_DATE, REFRESH_METHOD, SUMMARY,
     FULLREFRESHTIM, INCREFRESHTIM,
     CONTAINS_VIEWS, UNUSABLE, RESTRICTED_SYNTAX, INC_REFRESHABLE,
     KNOWN_STALE, INVALID, REWRITE_ENABLED, QUERY_LEN, QUERY, REVISION)
as
select u.name, o.name, u.name, s.containernam,
       s.lastrefreshscn, s.lastrefreshdate,
       decode (s.refreshmode, 0, 'NEVER', 1, 'FORCE', 2, 'FAST', 3,'COMPLETE'),
       decode(bitand(s.pflags, 25165824), 25165824, 'N', 'Y'),
       s.fullrefreshtim, s.increfreshtim,
       decode(bitand(s.pflags, 48), 0, 'N', 'Y'),
       decode(bitand(s.mflags, 64), 0, 'N', 'Y'), /* QSMQSUM_UNUSABLE */ 
       decode(bitand(s.pflags, 1294319), 0, 'Y', 'N'), 
       decode(bitand(s.pflags, 236879743), 0, 'Y', 'N'),
       decode(bitand(s.mflags, 1), 0, 'N', 'Y'), /* QSMQSUM_KNOWNSTL */
       decode(o.status, 5, 'Y', 'N'),
       decode(bitand(s.mflags, 4), 0, 'Y', 'N'), /* QSMQSUM_DISABLED */
       s.sumtextlen,s.sumtext,
       1/* Metadata revision number */
from sys.user$ u, sys.sum$ s, sys.obj$ o
where o.owner# = u.user#
  and o.obj# = s.obj#
/

comment on table DBA_MVIEW_ANALYSIS is
'Description of the materialized views accessible to dba'
/
comment on column DBA_MVIEW_ANALYSIS.OWNER is
'Owner of the materialized view'
/
comment on column DBA_MVIEW_ANALYSIS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column DBA_MVIEW_ANALYSIS.MVIEW_TABLE_OWNER is
'Owner of the container table'
/
comment on column DBA_MVIEW_ANALYSIS.CONTAINER_NAME is
'Name of the container table for this materialized view'
/
comment on column DBA_MVIEW_ANALYSIS.LAST_REFRESH_SCN is
'The SCN of the last transaction to refresh the materialized view'
/
comment on column DBA_MVIEW_ANALYSIS.LAST_REFRESH_DATE is
'The date of the last refresh of the materialized view'
/
comment on column DBA_MVIEW_ANALYSIS.REFRESH_METHOD is
'User declared method of refresh for the materialized view'
/
comment on column ALL_MVIEW_ANALYSIS.SUMMARY is
'Indicates if the materialized view includes the GROUP BY clause'
/
comment on column DBA_MVIEW_ANALYSIS.FULLREFRESHTIM is
'The time that it took to fully refresh the materialized view'
/
comment on column DBA_MVIEW_ANALYSIS.INCREFRESHTIM is
'The time that it took to incrementally refresh the materialized view'
/
comment on column DBA_MVIEW_ANALYSIS.CONTAINS_VIEWS is
'This materialized view contains views in the FROM clause'
/
comment on column DBA_MVIEW_ANALYSIS.UNUSABLE is
'This materialized view is unusable, the build was deferred'
/
comment on column DBA_MVIEW_ANALYSIS.RESTRICTED_SYNTAX is
'This materialized view contains restrictive syntax'
/
comment on column DBA_MVIEW_ANALYSIS.INC_REFRESHABLE is
'This materialized view is not restricted from being incrementally refreshed'
/
comment on column DBA_MVIEW_ANALYSIS.KNOWN_STALE is
'This materialized view is directly stale'
/
comment on column DBA_MVIEW_ANALYSIS.INVALID is
'Invalidity of the materialized view, Y = INVALID, N = VALID'
/
comment on column DBA_MVIEW_ANALYSIS.REWRITE_ENABLED is
'This materialized view is enabled for query rewrite'
/
drop public synonym DBA_MVIEW_ANALYSIS
/
create public synonym DBA_MVIEW_ANALYSIS for DBA_MVIEW_ANALYSIS
/ 
grant select on DBA_MVIEW_ANALYSIS to select_catalog_role
/

rem
rem Family of MVIEW_AGGREGATES
rem

rem DBA_MVIEW_AGGREGATES

create or replace view DBA_MVIEW_AGGREGATES
  (OWNER, MVIEW_NAME, POSITION_IN_SELECT, CONTAINER_COLUMN,
   AGG_FUNCTION, DISTINCTFLAG, MEASURE)
as
select u.name, o.name, sa.sumcolpos#, c.name,
       decode(sa.aggfunction, 15, 'AVG', 16, 'SUM', 17, 'COUNT',
                              18, 'MIN', 19, 'MAX',
                              97, 'VARIANCE', 98, 'STDDEV'),
       decode(sa.flags, 0, 'N', 'Y'),
       sa.aggtext
from sys.sumagg$ sa, sys.obj$ o, sys.user$ u, sys.sum$ s, sys.col$ c
where sa.sumobj# = o.obj#
  AND o.owner# = u.user#
  AND sa.sumobj# = s.obj# 
  AND c.obj# = s.containerobj#
  AND c.col# = sa.containercol#
/
comment on table DBA_MVIEW_AGGREGATES is
'Description of the materialized view aggregates accessible to dba'
/
comment on column DBA_MVIEW_AGGREGATES.OWNER is
'Owner of the materialized view'
/
comment on column DBA_MVIEW_AGGREGATES.MVIEW_NAME is
'Name of the materialized view'
/
comment on column DBA_MVIEW_AGGREGATES.POSITION_IN_SELECT is
'Position of this aggregated measure with the SELECT list'
/
comment on column DBA_MVIEW_AGGREGATES.CONTAINER_COLUMN is
'Name of this column in the container table'
/
comment on column DBA_MVIEW_AGGREGATES.AGG_FUNCTION is
'Name of the aggregation function, one of the following:
COUNT, SUM, MIN, MAX, AVG, VARIANCE, STDDEV'
/
comment on column DBA_MVIEW_AGGREGATES.DISTINCTFLAG is
'Set to Y is this is a DISTINCT aggregation'
/
comment on column DBA_MVIEW_AGGREGATES.MEASURE is
'The SQL text of the measure, excluding the aggregation function'
/
drop public synonym DBA_MVIEW_AGGREGATES
/
create public synonym DBA_MVIEW_AGGREGATES for DBA_MVIEW_AGGREGATES
/
grant select on DBA_MVIEW_AGGREGATES  to select_catalog_role
/

rem USER_MVIEW_AGGREGATES

create or replace view USER_MVIEW_AGGREGATES
  (OWNER, MVIEW_NAME, POSITION_IN_SELECT, CONTAINER_COLUMN,
   AGG_FUNCTION, DISTINCTFLAG, MEASURE)
as
select u.name, o.name, sa.sumcolpos#, c.name,
       decode(sa.aggfunction, 15, 'AVG', 16, 'SUM', 17, 'COUNT',
                              18, 'MIN', 19, 'MAX',
                              97, 'VARIANCE', 98, 'STDDEV'),
       decode(sa.flags, 0, 'N', 'Y'),
       sa.aggtext
from sys.sumagg$ sa, sys.obj$ o, sys.user$ u, sys.sum$ s, sys.col$ c
where sa.sumobj# = o.obj#
  AND o.owner# = u.user#
  AND sa.sumobj# = s.obj# 
  AND c.obj# = s.containerobj#
  AND c.col# = sa.containercol#
  AND o.owner# = userenv('SCHEMAID')
/
comment on table USER_MVIEW_AGGREGATES is
'Description of the materialized view aggregates created by the user'
/
comment on column USER_MVIEW_AGGREGATES.OWNER is
'Owner of the materialized view'
/
comment on column USER_MVIEW_AGGREGATES.MVIEW_NAME is
'Name of the materialized view'
/
comment on column USER_MVIEW_AGGREGATES.POSITION_IN_SELECT is
'Position of this aggregated measure with the SELECT list'
/
comment on column USER_MVIEW_AGGREGATES.CONTAINER_COLUMN is
'Name of this column in the container table'
/
comment on column USER_MVIEW_AGGREGATES.AGG_FUNCTION is
'Name of the aggregation function, one of the following:
COUNT, SUM, MIN, MAX, AVG, VARIANCE, STDDEV'
/
comment on column USER_MVIEW_AGGREGATES.DISTINCTFLAG is
'Set to Y is this is a DISTINCT aggregation'
/
comment on column USER_MVIEW_AGGREGATES.MEASURE is
'The SQL text of the measure, excluding the aggregation function'
/
drop public synonym USER_MVIEW_AGGREGATES
/
create public synonym USER_MVIEW_AGGREGATES for USER_MVIEW_AGGREGATES
/
grant select on USER_MVIEW_AGGREGATES  to PUBLIC with grant option 
/


rem ALL_MVIEW_AGGREGATES

create or replace view ALL_MVIEW_AGGREGATES
  (OWNER, MVIEW_NAME, POSITION_IN_SELECT, CONTAINER_COLUMN,
   AGG_FUNCTION, DISTINCTFLAG, MEASURE)
as
select u.name, o.name, sa.sumcolpos#, c.name,
       decode(sa.aggfunction, 15, 'AVG', 16, 'SUM', 17, 'COUNT',
                              18, 'MIN', 19, 'MAX',
                              97, 'VARIANCE', 98, 'STDDEV'),
       decode(sa.flags, 0, 'N', 'Y'),
       sa.aggtext
from sys.sumagg$ sa, sys.obj$ o, sys.user$ u, sys.sum$ s, sys.col$ c
where sa.sumobj# = o.obj#
  AND o.owner# = u.user#
  AND sa.sumobj# = s.obj# 
  AND c.obj# = s.containerobj#
  AND c.col# = sa.containercol#
  AND (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/

comment on table ALL_MVIEW_AGGREGATES is
'Description of the materialized view aggregates accessible to the user'
/
comment on column ALL_MVIEW_AGGREGATES.OWNER is
'Owner of the materialized view'
/
comment on column ALL_MVIEW_AGGREGATES.MVIEW_NAME is
'Name of the materialized view'
/
comment on column ALL_MVIEW_AGGREGATES.POSITION_IN_SELECT is
'Position of this aggregated measure with the SELECT list'
/
comment on column ALL_MVIEW_AGGREGATES.CONTAINER_COLUMN is
'Name of this column in the container table'
/
comment on column ALL_MVIEW_AGGREGATES.AGG_FUNCTION is
'Name of the aggregation function, one of the following:
COUNT, SUM, MIN, MAX, AVG, VARIANCE, STDDEV'
/
comment on column ALL_MVIEW_AGGREGATES.DISTINCTFLAG is
'Set to Y is this is a DISTINCT aggregation'
/
comment on column ALL_MVIEW_AGGREGATES.MEASURE is
'The SQL text of the measure, excluding the aggregation function'
/
drop public synonym ALL_MVIEW_AGGREGATES
/
create public synonym ALL_MVIEW_AGGREGATES for ALL_MVIEW_AGGREGATES
/
grant select on ALL_MVIEW_AGGREGATES  to PUBLIC with grant option 
/


rem
rem Family of MVIEW_DETAIL_RELATIONS
rem

rem ALL_MVIEW_DETAIL_RELATIONS

create or replace view ALL_MVIEW_DETAIL_RELATIONS
    (OWNER, MVIEW_NAME, DETAILOBJ_OWNER, DETAILOBJ_NAME, DETAILOBJ_TYPE, 
     DETAILOBJ_ALIAS)
as
select u.name, o.name, du.name,  do.name,
       decode (sd.detailobjtype, 1, 'TABLE', 2, 'VIEW',
                                3, 'SNAPSHOT', 4, 'CONTAINER', 'UNDEFINED'),
       sd.detailalias
from sys.user$ u, sys.sumdetail$ sd, sys.obj$ o, sys.obj$ do, 
sys.user$ du
where o.owner# = u.user#
  and o.obj# = sd.sumobj#
  and do.obj# = sd.detailobj#
  and do.owner# = du.user#
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
comment on table ALL_MVIEW_DETAIL_RELATIONS is
'Description of the materialized view detail tables accessible to the user'
/
comment on column ALL_MVIEW_DETAIL_RELATIONS.OWNER is
'Owner of the materialized view'
/
comment on column ALL_MVIEW_DETAIL_RELATIONS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column ALL_MVIEW_DETAIL_RELATIONS.DETAILOBJ_OWNER is
'Owner of the detail relation'
/
comment on column ALL_MVIEW_DETAIL_RELATIONS.DETAILOBJ_NAME is
'Name of the materialized view detail table'
/
comment on column ALL_MVIEW_DETAIL_RELATIONS.DETAILOBJ_TYPE is
'Type of materialized view detail table type'
/
comment on column ALL_MVIEW_DETAIL_RELATIONS.DETAILOBJ_ALIAS is
'Alias of the detail relation if used'

/
drop public synonym ALL_MVIEW_DETAIL_RELATIONS
/
create public synonym ALL_MVIEW_DETAIL_RELATIONS for
    ALL_MVIEW_DETAIL_RELATIONS
/
grant select on ALL_MVIEW_DETAIL_RELATIONS to PUBLIC with grant option
/

rem USER_MVIEW_DETAIL_RELATIONS

create or replace view USER_MVIEW_DETAIL_RELATIONS
    (OWNER, MVIEW_NAME, DETAILOBJ_OWNER, DETAILOBJ_NAME, DETAILOBJ_TYPE, 
     DETAILOBJ_ALIAS)
as
select u.name, o.name, du.name,  do.name,
       decode (sd.detailobjtype, 1, 'TABLE', 2, 'VIEW',
                                3, 'SNAPSHOT', 4, 'CONTAINER', 'UNDEFINED'),
       sd.detailalias
from sys.user$ u, sys.sumdetail$ sd, sys.obj$ o, sys.obj$ do, 
sys.user$ du
where o.owner# = u.user#
  and o.obj# = sd.sumobj#
  and do.obj# = sd.detailobj#
  and do.owner# = du.user#
  and o.owner# = userenv('SCHEMAID')
/
comment on table USER_MVIEW_DETAIL_RELATIONS is
'Description of the materialized view detail tables of the materialized 
views created by the user'
/
comment on column USER_MVIEW_DETAIL_RELATIONS.OWNER is
'Owner of the materialized view'
/
comment on column USER_MVIEW_DETAIL_RELATIONS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column USER_MVIEW_DETAIL_RELATIONS.DETAILOBJ_OWNER is
'Owner of the detail relation'
/
comment on column USER_MVIEW_DETAIL_RELATIONS.DETAILOBJ_NAME is
'Name of the materialized view detail table'
/
comment on column USER_MVIEW_DETAIL_RELATIONS.DETAILOBJ_TYPE is
'Type of materialized view detail table type'
/
comment on column USER_MVIEW_DETAIL_RELATIONS.DETAILOBJ_ALIAS is
'Alias of the detail relation if used'

/
drop public synonym USER_MVIEW_DETAIL_RELATIONS
/
create public synonym USER_MVIEW_DETAIL_RELATIONS for
    USER_MVIEW_DETAIL_RELATIONS
/
grant select on USER_MVIEW_DETAIL_RELATIONS to PUBLIC with grant option
/

rem DBA_MVIEW_DETAIL_RELATIONS

create or replace view DBA_MVIEW_DETAIL_RELATIONS
    (OWNER, MVIEW_NAME, DETAILOBJ_OWNER, DETAILOBJ_NAME, DETAILOBJ_TYPE, 
     DETAILOBJ_ALIAS)
as
select u.name, o.name, du.name,  do.name,
       decode (sd.detailobjtype, 1, 'TABLE', 2, 'VIEW',
                                3, 'SNAPSHOT', 4, 'CONTAINER', 'UNDEFINED'),
       sd.detailalias
from sys.user$ u, sys.sumdetail$ sd, sys.obj$ o, sys.obj$ do, 
     sys.user$ du
where o.owner# = u.user#
  and o.obj# = sd.sumobj#
  and do.obj# = sd.detailobj#
  and do.owner# = du.user#

/
comment on table DBA_MVIEW_DETAIL_RELATIONS is
'Description of the materialized view detail tables accessible to dba'
/
comment on column DBA_MVIEW_DETAIL_RELATIONS.OWNER is
'Owner of the materialized view'
/
comment on column DBA_MVIEW_DETAIL_RELATIONS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column DBA_MVIEW_DETAIL_RELATIONS.DETAILOBJ_OWNER is
'Owner of the detail relation'
/
comment on column DBA_MVIEW_DETAIL_RELATIONS.DETAILOBJ_NAME is
'Name of the materialized view detail table'
/
comment on column DBA_MVIEW_DETAIL_RELATIONS.DETAILOBJ_TYPE is
'Type of materialized detail table type'
/
comment on column DBA_MVIEW_DETAIL_RELATIONS.DETAILOBJ_ALIAS is
'Alias of the detail relation if used'

/
drop public synonym DBA_MVIEW_DETAIL_RELATIONS
/
create public synonym DBA_MVIEW_DETAIL_RELATIONS for DBA_MVIEW_DETAIL_RELATIONS
/
grant select on DBA_MVIEW_DETAIL_RELATIONS  to select_catalog_role
/

rem
rem FAMILY of MVIEW_KEYS
rem

rem DBA_MVIEW_KEYS

create or replace view DBA_MVIEW_KEYS
  (OWNER, MVIEW_NAME, POSITION_IN_SELECT, CONTAINER_COLUMN,
   DETAILOBJ_OWNER, DETAILOBJ_NAME, DETAILOBJ_ALIAS,
   DETAILOBJ_TYPE, DETAILOBJ_COLUMN)
as 
select u1.name, o1.name, sk.sumcolpos#, c1.name,
       u2.name, o2.name, sd.detailalias,
       decode(sk.detailobjtype, 1, 'TABLE', 2, 'VIEW'), c2.name
from sys.sumkey$ sk, sys.obj$ o1, sys.user$ u1, sys.col$ c1, sys.sum$ s, 
     sys.sumdetail$ sd, sys.obj$ o2, sys.user$ u2, sys.col$ c2
where sk.sumobj# = o1.obj#
  AND o1.owner# = u1.user#
  AND sk.sumobj# = s.obj#
  AND s.containerobj# = c1.obj#
  AND c1.col# = sk.containercol#
  AND sk.detailobj# = o2.obj#
  AND o2.owner# = u2.user#
  AND sk.sumobj# = sd.sumobj#
  AND sk.detailobj# = sd.detailobj#
  AND sk.detailobj# = c2.obj#
  AND sk.detailcol# = c2.intcol#
/
comment on table DBA_MVIEW_KEYS is
'Description of the columns that appear in the GROUP BY
list of a materialized view accessible to dba'
/
comment on column DBA_MVIEW_KEYS.OWNER is
'Owner of the materialized view'
/
comment on column DBA_MVIEW_KEYS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column DBA_MVIEW_KEYS.POSITION_IN_SELECT is
'Position of this key within the SELECT list'
/
comment on column DBA_MVIEW_KEYS.CONTAINER_COLUMN is
'Name of the column in the container table'
/
comment on column DBA_MVIEW_KEYS.DETAILOBJ_OWNER is
'Owner of the detail object'
/
comment on column DBA_MVIEW_KEYS.DETAILOBJ_NAME is
'Name of the detail object'
/
comment on column DBA_MVIEW_KEYS.DETAILOBJ_ALIAS is
'Alias of the detail object'
/
comment on column DBA_MVIEW_KEYS.DETAILOBJ_TYPE is
'Type of the detail object: VIEW or TABLE'
/
comment on column DBA_MVIEW_KEYS.DETAILOBJ_COLUMN is
'Name of the detail object column'

/
drop public synonym DBA_MVIEW_KEYS
/
create public synonym DBA_MVIEW_KEYS for DBA_MVIEW_KEYS
/
grant select on DBA_MVIEW_KEYS to select_catalog_role
/

rem ALL_MVIEW_KEYS

create or replace view ALL_MVIEW_KEYS
  (OWNER, MVIEW_NAME, POSITION_IN_SELECT, CONTAINER_COLUMN,
   DETAILOBJ_OWNER, DETAILOBJ_NAME, DETAILOBJ_ALIAS,
   DETAILOBJ_TYPE, DETAILOBJ_COLUMN)
as 
select u1.name, o1.name, sk.sumcolpos#, c1.name,
       u2.name, o2.name, sd.detailalias,
       decode(sk.detailobjtype, 1, 'TABLE', 2, 'VIEW'), c2.name
from sys.sumkey$ sk, sys.obj$ o1, sys.user$ u1, sys.col$ c1, sys.sum$ s, 
     sys.sumdetail$ sd, sys.obj$ o2, sys.user$ u2, sys.col$ c2
where sk.sumobj# = o1.obj#
  AND o1.owner# = u1.user#
  AND sk.sumobj# = s.obj#
  AND s.containerobj# = c1.obj#
  AND c1.col# = sk.containercol#
  AND sk.detailobj# = o2.obj#
  AND o2.owner# = u2.user#
  AND sk.sumobj# = sd.sumobj#
  AND sk.detailobj# = sd.detailobj#
  AND sk.detailobj# = c2.obj#
  AND sk.detailcol# = c2.intcol#
  AND (o1.owner# = userenv('SCHEMAID')
       or o1.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
comment on table ALL_MVIEW_KEYS is
'Description of the columns that appear in the GROUP BY
list of a materialized view accessible to the user'
/
comment on column ALL_MVIEW_KEYS.OWNER is
'Owner of the materialized view'
/
comment on column ALL_MVIEW_KEYS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column ALL_MVIEW_KEYS.POSITION_IN_SELECT is
'Position of this key within the SELECT list'
/
comment on column ALL_MVIEW_KEYS.CONTAINER_COLUMN is
'Name of the column in the container table'
/
comment on column ALL_MVIEW_KEYS.DETAILOBJ_OWNER is
'Owner of the detail object'
/
comment on column ALL_MVIEW_KEYS.DETAILOBJ_NAME is
'Name of the detail object'
/
comment on column ALL_MVIEW_KEYS.DETAILOBJ_ALIAS is
'Alias of the detail object'
/
comment on column ALL_MVIEW_KEYS.DETAILOBJ_TYPE is
'Type of the detail object: VIEW or TABLE'
/
comment on column ALL_MVIEW_KEYS.DETAILOBJ_COLUMN is
'Name of the detail object column'

/
drop public synonym ALL_MVIEW_KEYS
/
create public synonym ALL_MVIEW_KEYS for ALL_MVIEW_KEYS
/
grant select on ALL_MVIEW_KEYS to PUBLIC with grant option
/

rem USER_MVIEW_KEYS

create or replace view USER_MVIEW_KEYS
  (OWNER, MVIEW_NAME, POSITION_IN_SELECT, CONTAINER_COLUMN,
   DETAILOBJ_OWNER, DETAILOBJ_NAME, DETAILOBJ_ALIAS,
   DETAILOBJ_TYPE, DETAILOBJ_COLUMN)
as 
select u1.name, o1.name, sk.sumcolpos#, c1.name,
       u2.name, o2.name, sd.detailalias,
       decode(sk.detailobjtype, 1, 'TABLE', 2, 'VIEW'), c2.name
from sys.sumkey$ sk, sys.obj$ o1, sys.user$ u1, sys.col$ c1, sys.sum$ s, 
     sys.sumdetail$ sd, sys.obj$ o2, sys.user$ u2, sys.col$ c2
where sk.sumobj# = o1.obj#
  AND o1.owner# = u1.user#
  AND sk.sumobj# = s.obj#
  AND s.containerobj# = c1.obj#
  AND c1.col# = sk.containercol#
  AND sk.detailobj# = o2.obj#
  AND o2.owner# = u2.user#
  AND sk.sumobj# = sd.sumobj#
  AND sk.detailobj# = sd.detailobj#
  AND sk.detailobj# = c2.obj#
  AND sk.detailcol# = c2.intcol#
  AND o1.owner# = userenv('SCHEMAID')
/
comment on table USER_MVIEW_KEYS is
'Description of the columns that appear in the GROUP BY
list of a materialized view  created by the user'
/
comment on column USER_MVIEW_KEYS.OWNER is
'Owner of the materialized view'
/
comment on column USER_MVIEW_KEYS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column USER_MVIEW_KEYS.POSITION_IN_SELECT is
'Position of this key within the SELECT list'
/
comment on column USER_MVIEW_KEYS.CONTAINER_COLUMN is
'Name of the column in the container table'
/
comment on column USER_MVIEW_KEYS.DETAILOBJ_OWNER is
'Owner of the detail object'
/
comment on column USER_MVIEW_KEYS.DETAILOBJ_NAME is
'Name of the detail object'
/
comment on column USER_MVIEW_KEYS.DETAILOBJ_ALIAS is
'Alias of the detail object'
/
comment on column USER_MVIEW_KEYS.DETAILOBJ_TYPE is
'Type of the detail object: VIEW or TABLE'
/
comment on column USER_MVIEW_KEYS.DETAILOBJ_COLUMN is
'Name of the detail object column'

/
drop public synonym USER_MVIEW_KEYS
/
create public synonym USER_MVIEW_KEYS for USER_MVIEW_KEYS
/
grant select on USER_MVIEW_KEYS to PUBLIC with grant option
/


rem
rem FAMILY of MVIEW_JOINS
rem

rem DBA_MVIEW_JOINS

create or replace view DBA_MVIEW_JOINS
  (OWNER, MVIEW_NAME, 
  DETAILOBJ1_OWNER, DETAILOBJ1_RELATION, DETAILOBJ1_COLUMN, OPERATOR,
  OPERATOR_TYPE, DETAILOBJ2_OWNER, DETAILOBJ2_RELATION, DETAILOBJ2_COLUMN)
as
select u.name, o.name, 
       u1.name, o1.name, c1.name, '=',
       decode(sj.flags, 0, 'I', 1, 'L', 2, 'R'),
       u2.name, o2.name, c2.name
from sys.sumjoin$ sj, sys.obj$ o, sys.user$ u,
     sys.obj$ o1, sys.user$ u1, sys.col$ c1,
     sys.obj$ o2, sys.user$ u2, sys.col$ c2
where sj.sumobj# = o.obj#
  AND o.owner# = u.user#
  AND sj.tab1obj# = o1.obj#
  AND o1.owner# = u1.user#
  AND sj.tab1obj# = c1.obj#
  AND sj.tab1col# = c1.intcol#
  AND sj.tab2obj# = o2.obj#
  AND o2.owner# = u2.user#
  AND sj.tab2obj# = c2.obj#
  AND sj.tab2col# = c2.intcol#
/
comment on table DBA_MVIEW_JOINS is
'Description of a join between two columns in the
WHERE clause of a materialized view accessible to dba'
/
comment on column DBA_MVIEW_JOINS.OWNER is
'Owner of the materialized view'
/
comment on column DBA_MVIEW_JOINS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column DBA_MVIEW_JOINS.DETAILOBJ1_OWNER is
'Owner of the 1st detail object'
/
comment on column DBA_MVIEW_JOINS.DETAILOBJ1_RELATION is
'Name of the 1st detail object'
/
comment on column DBA_MVIEW_JOINS.DETAILOBJ1_COLUMN is
'Name of the 1st detail object column'
/
comment on column DBA_MVIEW_JOINS.OPERATOR is
'Name of the join operator. Currently only = is defined'
/
comment on column DBA_MVIEW_JOINS.OPERATOR_TYPE is
'Indicates inner or outer join. I = inner join, L = DETAILOBJ1 table
is the left side of an outer join'
/
comment on column DBA_MVIEW_JOINS.DETAILOBJ2_OWNER is
'Owner of the 2nd detail object'
/
comment on column DBA_MVIEW_JOINS.DETAILOBJ2_RELATION is
'Name of the 2nd detail object'
/
comment on column DBA_MVIEW_JOINS.DETAILOBJ2_COLUMN is
'Name of the 2nd detail object column'
/

drop public synonym DBA_MVIEW_JOINS
/
create public synonym DBA_MVIEW_JOINS for DBA_MVIEW_JOINS
/
grant select on DBA_MVIEW_JOINS to select_catalog_role
/

rem ALL_MVIEW_JOINS

create or replace view ALL_MVIEW_JOINS
  (OWNER, MVIEW_NAME, 
  DETAILOBJ1_OWNER, DETAILOBJ1_RELATION, DETAILOBJ1_COLUMN, OPERATOR,
  OPERATOR_TYPE, DETAILOBJ2_OWNER, DETAILOBJ2_RELATION, DETAILOBJ2_COLUMN)
as
select u.name, o.name, 
       u1.name, o1.name, c1.name, '=',
       decode(sj.flags, 0, 'I', 1, 'L', 2, 'R'),
       u2.name, o2.name, c2.name
from sys.sumjoin$ sj, sys.obj$ o, sys.user$ u,
     sys.obj$ o1, sys.user$ u1, sys.col$ c1,
     sys.obj$ o2, sys.user$ u2, sys.col$ c2
where sj.sumobj# = o.obj#
  AND o.owner# = u.user#
  AND sj.tab1obj# = o1.obj#
  AND o1.owner# = u1.user#
  AND sj.tab1obj# = c1.obj#
  AND sj.tab1col# = c1.intcol#
  AND sj.tab2obj# = o2.obj#
  AND o2.owner# = u2.user#
  AND sj.tab2obj# = c2.obj#
  AND sj.tab2col# = c2.intcol#
  AND (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
comment on table ALL_MVIEW_JOINS is
'Description of a join between two columns in the
WHERE clause of a materialized view accessible to the user'
/
comment on column ALL_MVIEW_JOINS.OWNER is
'Owner of the materialized view'
/
comment on column ALL_MVIEW_JOINS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column ALL_MVIEW_JOINS.DETAILOBJ1_OWNER is
'Owner of the 1st detail object'
/
comment on column ALL_MVIEW_JOINS.DETAILOBJ1_RELATION is
'Name of the 1st detail object'
/
comment on column ALL_MVIEW_JOINS.DETAILOBJ1_COLUMN is
'Name of the 1st detail object column'
/
comment on column ALL_MVIEW_JOINS.OPERATOR is
'Name of the join operator. Currently only = is defined'
/
comment on column DBA_MVIEW_JOINS.OPERATOR_TYPE is
'Indicates inner or outer join. I = inner join, L = DETAILOBJ1 table
is the left side of an outer join'
/
comment on column ALL_MVIEW_JOINS.DETAILOBJ2_OWNER is
'Owner of the 2nd detail object'
/
comment on column ALL_MVIEW_JOINS.DETAILOBJ2_RELATION is
'Name of the 2nd detail object'
/
comment on column ALL_MVIEW_JOINS.DETAILOBJ2_COLUMN is
'Name of the 2nd detail object column'
/
drop public synonym ALL_MVIEW_JOINS
/
create public synonym ALL_MVIEW_JOINS for ALL_MVIEW_JOINS
/
grant select on ALL_MVIEW_JOINS to PUBLIC with grant option
/


rem USER_MVIEW_JOINS

create or replace view USER_MVIEW_JOINS
  (OWNER, MVIEW_NAME, 
  DETAILOBJ1_OWNER, DETAILOBJ1_RELATION, DETAILOBJ1_COLUMN, OPERATOR,
  OPERATOR_TYPE, DETAILOBJ2_OWNER, DETAILOBJ2_RELATION, DETAILOBJ2_COLUMN)
as
select u.name, o.name, 
       u1.name, o1.name, c1.name, '=',
       decode(sj.flags, 0, 'I', 1, 'L', 2, 'R'),
       u2.name, o2.name, c2.name
from sys.sumjoin$ sj, sys.obj$ o, sys.user$ u,
     sys.obj$ o1, sys.user$ u1, sys.col$ c1,
     sys.obj$ o2, sys.user$ u2, sys.col$ c2
where sj.sumobj# = o.obj#
  AND o.owner# = u.user#
  AND sj.tab1obj# = o1.obj#
  AND o1.owner# = u1.user#
  AND sj.tab1obj# = c1.obj#
  AND sj.tab1col# = c1.intcol#
  AND sj.tab2obj# = o2.obj#
  AND o2.owner# = u2.user#
  AND sj.tab2obj# = c2.obj#
  AND sj.tab2col# = c2.intcol#
  AND o.owner# = userenv('SCHEMAID')
/
comment on table USER_MVIEW_JOINS is
'Description of a join between two columns in the
WHERE clause of a materialized view created by the user'
/
comment on column USER_MVIEW_JOINS.OWNER is
'Owner of the materialized view'
/
comment on column USER_MVIEW_JOINS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column USER_MVIEW_JOINS.DETAILOBJ1_OWNER is
'Owner of the 1st detail object'
/
comment on column USER_MVIEW_JOINS.DETAILOBJ1_RELATION is
'Name of the 1st detail object'
/
comment on column USER_MVIEW_JOINS.DETAILOBJ1_COLUMN is
'Name of the 1st detail object column'
/
comment on column USER_MVIEW_JOINS.OPERATOR is
'Name of the join operator. Currently only = is defined'
/
comment on column DBA_MVIEW_JOINS.OPERATOR_TYPE is
'Indicates inner or outer join. I = inner join, L = DETAILOBJ1 table
is the left side of an outer join, R = DETAILOBJ2 table is the right
side of an outer join'
/
comment on column USER_MVIEW_JOINS.DETAILOBJ2_OWNER is
'Owner of the 2nd detail object'
/
comment on column USER_MVIEW_JOINS.DETAILOBJ2_RELATION is
'Name of the 2nd detail object'
/
comment on column USER_MVIEW_JOINS.DETAILOBJ2_COLUMN is
'Name of the 2nd detail object column'
/
drop public synonym USER_MVIEW_JOINS
/
create public synonym USER_MVIEW_JOINS for USER_MVIEW_JOINS
/
grant select on USER_MVIEW_JOINS to PUBLIC with grant option
/

rem aw1<

  

rem
rem FAMILY of REFRESH_DEPENDENCIES
rem
rem Note: Must be in sync with literals AOPIXCS... and KGLTSUMM
rem
create or replace view ALL_REFRESH_DEPENDENCIES
  (OWNER,TABLE_NAME,PARENT_OBJECT_TYPE,OLDEST_REFRESH_SCN,OLDEST_REFRESH_DATE)
as 
select u.name, o.name, 'MATERIALIZED VIEW', dep.lastrefreshscn, 
       dep.lastrefreshdate 
from (select dt.obj#, 
             min(dt.lastrefreshscn) as lastrefreshscn, 
             min(dt.lastrefreshdate) as lastrefreshdate
      from
           (select d.p_obj# as obj#, s.lastrefreshscn, s.lastrefreshdate
            from sumdep$ d, sum$ s, obj$ do
            where d.sumobj# = s.obj#
              and d.sumobj# = do.obj#
              and do.type# IN (4, 42)
            union  
            select sl.tableobj# as obj#, 
                   decode(0, 1, 2, NULL) as lastrefreshscn, 
                   sl.oldest  as lastrefreshdate
            from snap_loadertime$ sl) dt
      group by dt.obj#) dep, obj$ o, user$ u
where o.obj# = dep.obj#
  and o.owner# = u.user#
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in (select oa.obj# from sys.objauth$ oa
                     where grantee# in (select kzsrorol from x$kzsro)
                    )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )                     
       )
/
comment on table ALL_REFRESH_DEPENDENCIES is
'Description of the detail tables that materialized views depend on for
refresh'
/
comment on column ALL_REFRESH_DEPENDENCIES.OWNER is
'Owner of the dependent table'
/
comment on column ALL_REFRESH_DEPENDENCIES.TABLE_NAME is
'Name of the dependent table'
/
comment on column ALL_REFRESH_DEPENDENCIES.PARENT_OBJECT_TYPE is
'Materialized View'
/
comment on column ALL_REFRESH_DEPENDENCIES.OLDEST_REFRESH_SCN is
'The oldest scn of a dependent materialized view'
/
comment on column ALL_REFRESH_DEPENDENCIES.OLDEST_REFRESH_DATE is
'The oldest date scn of a dependent materialized view'
/
drop public synonym ALL_REFRESH_DEPENDENCIES
/
create public synonym ALL_REFRESH_DEPENDENCIES for
    ALL_REFRESH_DEPENDENCIES
/
grant select on ALL_REFRESH_DEPENDENCIES to PUBLIC with grant option
/



Rem End of File
