spool c:\agg_test_files\install_agg_test.log

prompt Creates the Aggregation Test environmemnt
prompt 
prompt Three tablespaces are first created:
prompt SH_MV, SH_ODM and SH_AW
prompt
prompt Then three users are created with the same names
prompt
prompt The SH schema is then exported with ROWS=N
prompt This is then imported into the SH_MV and SH_ODM
prompt schemas to create the required structures
prompt
prompt Connect as SYSTEM user
connect system/password@ora10g

prompt dropping existing test users. Ignore errors if users did
prompt not already exist

DROP USER SH_MV CASCADE;
DROP USER SH_ODM CASCADE;
DROP USER SH_AW CASCADE;

prompt dropping existing SH_ tablespaces. Ignore errors if tablespaces did
prompt not existing before

prompt Creating SH_MV user...
CREATE USER SH_MV
    IDENTIFIED BY PASSWORD DEFAULT TABLESPACE EXAMPLE
       TEMPORARY TABLESPACE TEMP
    QUOTA UNLIMITED ON EXAMPLE;

prompt Creating SH_MV user...
CREATE USER SH_ODM
    IDENTIFIED BY PASSWORD DEFAULT TABLESPACE EXAMPLE
       TEMPORARY TABLESPACE TEMP
    QUOTA UNLIMITED ON EXAMPLE;

prompt Creating SH_AW user...
CREATE USER SH_AW
    IDENTIFIED BY PASSWORD DEFAULT TABLESPACE EXAMPLE
       TEMPORARY TABLESPACE TEMP
    QUOTA UNLIMITED ON EXAMPLE;

prompt >> Assigning privileges to test users
GRANT CONNECT, CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE VIEW, CREATE MATERIALIZED VIEW TO SH_MV, SH_ODM;
GRANT CONNECT, CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE VIEW TO SH_AW;

GRANT OLAP_USER TO SH_AW, SH_ODM;

prompt exporting the table structures and OLAP metadata
prompt from the SH schema

ho exp sh/password@ora10g file='sh.dmp' tables=products,promotions,times,channels,countries,customers,sales rows=N

prompt importing SH objects into SH_MV schema

ho imp sh_mv/password@ora10g file='sh.dmp' fromuser=SH touser=SH_MV

prompt importing SH objects into SH_MV schema

ho imp sh_odm/password@ora10g file='sh.dmp' fromuser=SH touser=SH_ODM

prompt creating table grants

GRANT SELECT ON SH.SALES to SH_MV;
GRANT SELECT ON SH.PRODUCTS to SH_MV;
GRANT SELECT ON SH.CUSTOMERS to SH_MV;
GRANT SELECT ON SH.COUNTRIES to SH_MV;
GRANT SELECT ON SH.PROMOTIONS to SH_MV;
GRANT SELECT ON SH.TIMES to SH_MV;
GRANT SELECT ON SH.CHANNELS to SH_MV;
GRANT SELECT ON SH.SALES to SH_ODM;
GRANT SELECT ON SH.PRODUCTS to SH_ODM;
GRANT SELECT ON SH.CUSTOMERS to SH_ODM;
GRANT SELECT ON SH.COUNTRIES to SH_ODM;
GRANT SELECT ON SH.PROMOTIONS to SH_ODM;
GRANT SELECT ON SH.TIMES to SH_ODM;
GRANT SELECT ON SH.CHANNELS to SH_ODM;
GRANT SELECT ON SH.SALES to SH_AW;
GRANT SELECT ON SH.PRODUCTS to SH_AW;
GRANT SELECT ON SH.CUSTOMERS to SH_AW;
GRANT SELECT ON SH.COUNTRIES to SH_AW;
GRANT SELECT ON SH.PROMOTIONS to SH_AW;
GRANT SELECT ON SH.TIMES to SH_AW;
GRANT SELECT ON SH.CHANNELS to SH_AW;

spool off