Aggregation Test Setup Script

For anyone following along from yesterday, here's the script to create the test environment. You'll obviously need to amend it for your own environment (file location, passwords and connect strings):

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
DROP TABLESPACE SH_MV INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE SH_MV_TEMP INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE SH_ODM INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE SH_ODM_TEMP INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE SH_AW INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE SH_AW_TEMP INCLUDING CONTENTS AND DATAFILES;
prompt Creating SH_MV tablespace...

CREATE TABLESPACE SH_MV

DATAFILE 'C:\oracle\product\10.2.0\oradata\ora10g\SH_MV.DBF' SIZE 100M AUTOEXTEND ON NEXT 5M;
prompt Creating SH_MV_TEMP temporary tablespace...

CREATE TEMPORARY TABLESPACE SH_MV_TEMP

TEMPFILE 'C:\oracle\product\10.2.0\oradata\ora10g\SH_MV_TEMP.DBF' SIZE 100M AUTOEXTEND ON NEXT 5M

UNIFORM SIZE 256K;
prompt Creating SH_ODM tablespace...

CREATE TABLESPACE SH_ODM

DATAFILE 'C:\oracle\product\10.2.0\oradata\ora10g\SH_ODM.DBF' SIZE 100M AUTOEXTEND ON NEXT 5M;
prompt Creating SH_ODM_TEMP temporary tablespace...

CREATE TEMPORARY TABLESPACE SH_ODM_TEMP

TEMPFILE 'C:\oracle\product\10.2.0\oradata\ora10g\SH_ODM_TEMP.DBF' SIZE 100M AUTOEXTEND ON NEXT 5M

UNIFORM SIZE 256K;
prompt Creating SH_AW tablespace...

CREATE TABLESPACE SH_AW

DATAFILE 'C:\oracle\product\10.2.0\oradata\ora10g\SH_AW.DBF' SIZE 100M AUTOEXTEND ON NEXT 5M;
prompt Creating SH_AW_TEMP temporary tablespace...

CREATE TEMPORARY TABLESPACE SH_AW_TEMP

TEMPFILE 'C:\oracle\product\10.2.0\oradata\ora10g\SH_AW_TEMP.DBF' SIZE 100M AUTOEXTEND ON NEXT 5M

UNIFORM SIZE 256K;
prompt Creating SH_MV user...

CREATE USER SH_MV

IDENTIFIED BY PASSWORD DEFAULT TABLESPACE SH_MV

TEMPORARY TABLESPACE SH_MV_TEMP

QUOTA UNLIMITED ON SH_MV;
prompt Creating SH_MV user...

CREATE USER SH_ODM

IDENTIFIED BY PASSWORD DEFAULT TABLESPACE SH_ODM

TEMPORARY TABLESPACE SH_ODM_TEMP

QUOTA UNLIMITED ON SH_ODM;
prompt Creating SH_AW user...

CREATE USER SH_AW

IDENTIFIED BY PASSWORD DEFAULT TABLESPACE SH_AW

TEMPORARY TABLESPACE SH_AW_TEMP

QUOTA UNLIMITED ON SH_AW;
prompt >> Assigning privileges to test users

GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW TO SH_MV, SH_ODM;

GRANT CONNECT, RESOURCE, 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' 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