Rem Rem $Header: cwmlite/admin/amdrelod.sql /main/31 2009/03/10 12:58:11 awesley Exp $ Rem Rem amdrelod.sql Rem Rem Copyright (c) 2001, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem amdrelod.sql - Rem Rem DESCRIPTION Rem Rem Rem MODIFIED (MM/DD/YY) Rem awesley 03/09/09 - add mvtabgs.sql Rem dbardwel 01/20/09 - Need to install the sys.all$olap2_aws view here to avoid regressions Rem awesley 12/18/08 - add grant select any dictionary to olapsys Rem glyon 06/07/07 - add 'onerole' to create CWM_USER role on upgrade Rem cdalessi 05/17/05 - testing output Rem awesley 02/10/05 - change olapodm.sql to olap.odm.plb Rem cdalessi 12/02/04 - Unconditional call to apsroles.sql Rem cdalessi 10/06/04 - Move validation to SYS Rem dbardwel 09/13/04 - Remove cwm2conv.sql obsolete now. Rem cdalessi 01/23/04 - Add sysaux move procedure loads Rem cdalessi 12/26/03 - add a few missing PLB calls. Rem cdalessi 01/21/04 - Rem cdalessi 12/09/03 - Rem cdalessi 11/03/03 - fix banner Rem dbardwel 10/08/03 - Remove obsolete cwm2enut from this script Rem awesley 09/29/03 - 02041 to main Rem cdalessi 09/04/03 - apsroles checks Rem cdalessi 08/06/03 - Remove duplicate grants Rem cdalessi 07/29/03 - Fixes to redundant calls. Rem dbardwel 06/25/03 - Rem cdalessi 06/20/03 - Rem cdalessi 04/14/03 - New tables/views Rem cdalessi 03/20/03 - 9.2.0.3 updates Rem cdalessi 03/18/03 - Update to handle 9.2.0.2 and/or 9.2.0.3 Rem cdalessi 12/27/02 - cdalessi_txn105585 Rem cdalessi 12/18/02 - Change ordering for syn/grant scripts Rem cdalessi 12/16/02 - Migration support Rem cdalessi 12/02/02 - Creation execute sys.dbms_registry.loading('AMD' ,'OLAP Catalog' ,'cwm2_olap_installer.Validate_CWM2_Install' ,'OLAPSYS'); Rem needed to get views on data dictionary tables to work for unprivileged Rem users grant select any dictionary to olapsys; Rem Define the new version of the sys.all$olap2_aws view here to get all regressions to run correctly Rem This view is used to get the AWs and their metadata version - this is run against SYS Schema not OLAPSYS. create or replace view sys.all$olap2_aws (owner, aw, aw_number, aw_version, sf_version) as select aws11g.owner, aws11g.aw_name, aws11g.aw_number, aws11g.aw_version, '11.1' sf_version from all_aws aws11g, (select /*+ ordered */ a.awseq#, r.rid, p.rowid, p.propname from sys.aw$ a, sys.aw_prop$ p, sys.obj$ o, dba_users u, (select max(rowid) keep (dense_rank last order by gen#) rid from sys.aw_prop$ group by awseq#, oid, propname) r where a.owner# = u.user_id and o.owner# = a.owner# and o.name = 'AW$' || a.awname and o.type# = 2 and a.awseq# = p.awseq# and p.objname = '___AW_VERSION' and p.propname = 'AW$VERSION11.1' and p.rowid = r.rid and p.propval is not null) props11g where props11g.awseq# = aws11g.aw_number union all select max(aws.owner) owner, max(aws.aw_name) aw, props1.awseq#, max(aws.aw_version) aw_version, (case when count(props1.awseq#) = 2 then '10.2' when count(props1.awseq#) = 1 then '10.1.0.3' else null end) sf_version from all_aws aws, (select /*+ ordered */ a.awseq#, r.rid, p.rowid, p.propname from sys.aw$ a, sys.aw_prop$ p, sys.obj$ o, dba_users u, (select max(rowid) keep (dense_rank last order by gen#) rid from sys.aw_prop$ group by awseq#, oid, propname) r where a.owner# = u.user_id and o.owner# = a.owner# and o.name = 'AW$' || a.awname and o.type# = 2 and a.awseq# = p.awseq# and p.propname in ('AW$VERSION10.2', 'AW$VERSION10.1.0.3') and p.objname = '___XML_USER_AW_VERSION' and p.rowid = r.rid and p.propval is not null) props1 where props1.awseq# = aws.aw_number and (props1.awseq# not in (select /*+ ordered */ a.awseq# from sys.aw$ a, sys.aw_prop$ p, sys.obj$ o, dba_users u, (select max(rowid) keep (dense_rank last order by gen#) rid from sys.aw_prop$ group by awseq#, oid, propname) r where a.owner# = u.user_id and o.owner# = a.owner# and o.name = 'AW$' || a.awname and o.type# = 2 and a.awseq# = p.awseq# and p.propname = 'AW$VERSION11.1' and p.objname = '___AW_VERSION' and p.rowid = r.rid and p.propval is not null)) group by awseq# / grant select on sys.all$olap2_aws to public / create or replace public synonym ALL_OLAP2_AWS for sys.all$olap2_aws / alter session set current_schema = olapsys; @@onerole.sql @@oneapi.sql @@oneapigr.sql @@oneapisy.sql @@onevd.sql @@onevdgrt.sql @@onevdsyn.sql @@oneva.sql @@onevagrt.sql @@onevasyn.sql @@onemrv.sql @@oneapib.sql @@cwm2trg.sql @@cwm2api.sql -- cwm2 package pls @@cwm2apig.sql -- cwm2 package grants @@cwm2apis.sql -- cwm2 package synonyms @@cwm2alvw.sql -- cwm2 all views @@cwm2grnt.sql -- cwm2 all view grants @@cwm2asyn.sql -- cwm2 all view synonyms @@cwm2dbvw.sql -- cwm2 dba views @@cwm2expv.sql -- cwm2 export views @@cwm2dgrt.sql -- cwm2 dba view grants @@cwm2dsyn.sql -- cwm2 dba view synonyms @@cwm2apib.sql -- cwm2 package plb @@cwm2upg.sql -- upgrade from 9i @@cwm2sed2.sql -- new seed information for AW_CREATE tracking metadata @@cwm2sed3.sql -- new ODBO descriptors @@cwm2awlv.sql -- cwm2 aw load views @@cwm2awlg.sql -- cwm2 aw load view grants @@cwm2awls.sql -- cwm2 aw load view synonyms @@cwm2awmd.sql @@cwm2mrv2.sql -- cwm2 metadata reader views @@cwm2mrgt.sql -- cwm2 metadata reader view grants @@cwm2mrsy.sql -- cwm2 metadata reader view synonyms @@cwm2mrav.sql -- active catalog metadata refresh views @@cwm2mrag.sql -- active catalog metadata refresh grants @@cwm2mras.sql -- active catalog metadata refresh synonyms @@cwm2mrsc.pls -- cwm2 metadata reader security defs @@cwm2mrrf.pls -- cwm2 metadata reader refresh def @@cwm2mrsc.plb -- cwm2 metadata reader security impl describe cwm2$mrall_facttblkeymaps; @@cwm2mrrf.plb -- cwm2 metadata reader refresh impl @@cwm2mrgp.sql -- cwm2 metadata reader packages grants @@cwm2mrss.sql -- cwm2 metadata reader security synonyms @@factvspc.sql @@factview.sql @@dimvwspc.sql @@dimview.sql @@mvtabgs.sql @@olapodm.pls @@olapodm.plb @@cwm2vrq.sql @@cwm2awao.sql -- AW to PLSQL program interface enablement @@cwm2awen.pls -- cwm2_olap_olapapi_enable package specification @@cwm2awen.plb -- cwm2_olap_olapapi_enable package implementation @@cwm2autl.pls -- cwm2_olap_aw_utility @@cwm2autl.plb -- cwm2_olap_aw_utility implementation @@cwm2awct.pls -- cwm2_olap_aw_create for 9.2.0.2.0 @@cwm2awct.plb -- cwm2_olap_aw_create plsql binary @@cwm2awcg.sql -- grant script for aw create and enable packages @@cwm2awcs.sql -- synonym script for aw create and enable packages @@cwm2cube.plb @@cwm2vac.plb @@apsroles.sql execute cwm2_olap_metadata_refresh.mr_refresh; execute sys.dbms_registry.loaded('AMD'); alter session set current_schema = sys; @@cwm2mov.pls @@cwm2mov.plb @@cwm2iner.pls @@cwm2iner.plb execute sys.cwm2_olap_installer.Validate_CWM2_Install;