Rem
Rem $Header: cwmlite/admin/onevadim.sql /main/6 2008/07/28 13:25:50 glyon Exp $
Rem
Rem onevadim.sql
Rem
Rem  Copyright (c) Oracle Corporation 2000, 2001. All Rights Reserved.
Rem
Rem    NAME
Rem      onevadim.sql - all$olap_dimensions
Rem
Rem    DESCRIPTION
Rem      Dimensions.
Rem
Rem    NOTES
Rem      
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glyon       06/24/08 - bug 7204558: eliminate references to sys.user$
Rem    dthompso    02/27/01 - change security implementation
Rem    dthompso    10/26/00 - modify how validate_dimension invoked
Rem    dallan      08/18/00 - Modify dimension validate code.
Rem    dallan      08/15/00 - Add OLAP_VALID column to view..
Rem    dthompso    04/26/00 - Initial Version
Rem    dthompso    01/00/00 - Created
Rem

CREATE OR REPLACE VIEW all$olap_dimensions AS SELECT
  u.username owner
, o.name dimension_name
, dim.pluralname plural_name
, dim.displayname display_name
, dim.description description
, display_hierarchy_name default_display_hierarchy
, decode(o.status, 5, 'Y', 'N') invalid
, cwm$util.dim_valid(o.obj#) olap_valid
FROM 
  dba_users u
, sys.obj$ o
, sys.dim$ d
, cwm$dimension dim
WHERE u.user_id = o.owner#
AND o.obj# = d.obj#
AND d.obj# = dim.irid (+)
AND (   cwm$util.dimension_tables_visible(d.obj#) = 'Y'
     OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */
       (SELECT null FROM v$enabledprivs 
        WHERE priv_number IN (-47,-215,-216,-217)))
WITH READ ONLY
/