Using jts/setup to configure Oracle to support the RTC 4.0.2 Data Warehouse

One potential gotcha is that the DW users default role must be "DBA", once you have granted it DBA permissions. We had similar issues when initialising the Data Warehouse tables.

We used the script below to create the DW user:

  CREATE USER dw IDENTIFIED BY password DEFAULT TABLESPACE dw QUOTA UNLIMITED ON dw TEMPORARY TABLESPACE dwtmp;
  GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO dw;
  GRANT "AQ_ADMINISTRATOR_ROLE" TO dw;
  GRANT "DBA" TO dw;
  GRANT "MGMT_USER" TO dw;
  ALTER USER dw DEFAULT ROLE "AQ_ADMINISTRATOR_ROLE", "DBA", "MGMT_USER";

Once your RTC installation (including RRDI) is complete, you can then remove the DBA role from the DW user with the following script.

  REVOKE "AQ_ADMINISTRATOR_ROLE" FROM dw;
  REVOKE "DBA" FROM dw;
  REVOKE "MGMT_USER" FROM dw;
  ALTER USER dw DEFAULT ROLE NONE;
  ALTER USER dw TEMPORARY TABLESPACE RIS_TEMP;
  ALTER USER dw QUOTA UNLIMITED ON VNF_32K;
  ALTER USER dw QUOTA UNLIMITED ON VNF_IDX;
  ALTER USER dw QUOTA UNLIMITED ON USERS;
  ALTER USER dw QUOTA UNLIMITED ON VSTR_32K;
  ALTER USER dw QUOTA UNLIMITED ON VSTR_IDX;
  GRANT CREATE SESSION TO dw;
  GRANT CONNECT TO dw;
  GRANT DROP ANY TABLE TO dw;
  GRANT EXECUTE ANY PROCEDURE TO dw;
  GRANT ANALYZE ANY TO dw;

  BEGIN
  FOR TABNAME IN (SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER IN ('RIODS','RIDW','CONFIG','RIASSET','RISCHK','RICALM'))
  LOOP
  EXECUTE IMMEDIATE 'GRANT SELECT,UPDATE,DELETE,INSERT ON ' || TABNAME.OWNER || '."' || TABNAME.TABLE_NAME || '" TO dw';
  END LOOP;
  END;
  /

  BEGIN
  FOR VNAME IN (SELECT OWNER,VIEW_NAME FROM ALL_VIEWS WHERE OWNER IN ('RIDW','RICALM'))
  LOOP   
  EXECUTE IMMEDIATE 'GRANT SELECT ON ' || VNAME.OWNER || '."' || VNAME.VIEW_NAME || '" TO dw';
  END LOOP;
  END;
  /