Rem
Rem $Header: cwmlite/admin/onevacol.sql /main/4 2008/07/28 13:25:50 glyon Exp $
Rem
Rem onevacol.sql
Rem
Rem Copyright (c) 1900, 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      onevacol.sql - all$olap_columns
Rem
Rem    DESCRIPTION
Rem      Columns within Tables and Views
Rem
Rem    NOTES
Rem      
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glyon       06/24/08 - bug 7204558: eliminate references to sys.user$
Rem    mstasiew    03/18/03 - 
Rem    dthompso    02/27/01 - change security implementation
Rem    dthompso    04/26/00 - Initial Version
Rem    dthompso    01/00/00 - Created
Rem

CREATE OR REPLACE VIEW all$olap_columns AS SELECT 
  u.username owner
, t.name table_name
, c.name column_name
, decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                 2, decode(c.scale, null,
                           decode(c.precision#, null, 'NUMBER', 'FLOAT'),
                           'NUMBER'),
                 8, 'LONG',
                 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 
                 69, 'ROWID',
                 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                 105, 'MLSLABEL',
                 106, 'MLSLABEL',
                 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                 178, 'TIME(' ||c.spare1|| ')',
                 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE',
                 180, 'TIMESTAMP(' ||c.spare1|| ')',
                 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE',
                 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH',
                 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' ||
                       c.spare1 || ')',
                 208, 'UROWID',
                 'UNDEFINED') data_type
, decode(c.length, null, 0, c.length) data_length
, decode(c.precision#, null, 0, c.precision#) data_precision
FROM
  dba_users u
, sys.obj$ t
, sys.col$ c
WHERE u.user_id = t.owner#
AND t.type# IN (2,4)
AND t.obj# = c.obj#
AND (   t.owner# = UID
     OR t.obj# IN 
       (SELECT obj# FROM sys.objauth$ 
        WHERE (   grantee# = UID
               OR grantee# IN 
                 (SELECT privilege# 
                  FROM sys.sysauth$ 
                  WHERE privilege# > 0
                  START WITH grantee# = UID 
                  CONNECT BY PRIOR privilege# = grantee#)))
     OR EXISTS /* SELECT ANY TABLE */
       (SELECT null FROM v$enabledprivs 
        WHERE priv_number = -47))
WITH READ ONLY
/