Page tree
Skip to end of metadata
Go to start of metadata

This topic shows the contents of the create_users_and_tablespaces_template_filesystem.sql database creation script.

SET DEFINE ON
-- Please modify following variables for role, user or tablespace name customizations

DEFINE ROLE_OWNER = 'BCO_OWN_ROLE'
DEFINE ROLE_DASHBOARD = 'BCO_DASH_ROLE'
DEFINE ROLE_REPORT = 'BCO_REP_ROLE'
DEFINE USER_OWNER = 'BCO_OWN'
DEFINE USER_OWNER_PWD = 'BmcCapac1ty_OWN'
DEFINE USER_DASHBOARD = 'BCO_DASH'
DEFINE USER_DASHBOARD_PWD = 'BmcCapac1ty_DASH'
DEFINE USER_REPORT = 'BCO_REP'
DEFINE USER_REPORT_PWD = 'BmcCapac1ty_REP'
DEFINE TBS_CATALOG = 'BCO'
DEFINE TBS_CATALOG_IDX = 'BCO_IDX'
DEFINE TBS_DATA = 'BCO_DATA'
DEFINE TBS_DATA_IDX = 'BCO_DATA_IDX'
DEFINE TBS_DASH= 'BCO_DASH'
DEFINE TBS_TEMP = 'TEMP'

-- Tablespaces
CREATE TABLESPACE &TBS_CATALOG DATAFILE
'/oradata/bco/cpit_01.dbf' SIZE 16M 
AUTOEXTEND ON NEXT 16M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

CREATE TABLESPACE &TBS_CATALOG_IDX DATAFILE
'/oradata/bco/cpit_idx_01.dbf' SIZE 16M 
AUTOEXTEND ON NEXT 16M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

CREATE TABLESPACE &TBS_DATA DATAFILE
'/oradata/bco/cpit_data_01.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M,
'/oradata/bco/cpit_data_02.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M,  
'/oradata/bco/cpit_data_03.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

CREATE TABLESPACE &TBS_DATA_IDX DATAFILE
'/oradata/bco/cpit_data_idx_01.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M, 
'/oradata/bco/cpit_data_idx_02.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M,  
'/oradata/bco/cpit_data_idx_03.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

CREATE TABLESPACE &TBS_DASH DATAFILE
'/oradata/bco/cpit_dash_01.dbf' SIZE 16M 
AUTOEXTEND ON NEXT 16M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

-- Schema owner role
CREATE ROLE &ROLE_OWNER NOT IDENTIFIED
/
GRANT CREATE SESSION TO &ROLE_OWNER
/
GRANT CREATE SYNONYM TO &ROLE_OWNER
/
GRANT CREATE TABLE TO &ROLE_OWNER
/
GRANT CREATE VIEW  TO &ROLE_OWNER
/
GRANT CREATE SEQUENCE TO &ROLE_OWNER
/
GRANT CREATE TYPE TO &ROLE_OWNER
/
GRANT CREATE PROCEDURE TO &ROLE_OWNER
/
GRANT CREATE TRIGGER TO &ROLE_OWNER
/
GRANT CREATE OPERATOR TO &ROLE_OWNER
/
GRANT SELECT ON DBA_DATA_FILES TO &ROLE_OWNER
/

-- Reporting role
CREATE ROLE &ROLE_REPORT NOT IDENTIFIED
/
GRANT CREATE SESSION TO &ROLE_REPORT
/
GRANT CREATE SYNONYM TO &ROLE_REPORT
/

-- Schema owner user
CREATE USER &USER_OWNER IDENTIFIED BY &USER_OWNER_PWD
 DEFAULT TABLESPACE &TBS_CATALOG
 TEMPORARY TABLESPACE &TBS_TEMP
 QUOTA UNLIMITED ON &TBS_CATALOG
/
GRANT &ROLE_OWNER TO &USER_OWNER
/
GRANT &ROLE_REPORT TO &USER_OWNER
/
ALTER USER &USER_OWNER DEFAULT ROLE ALL
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_CATALOG
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_CATALOG_IDX
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_DATA
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_DATA_IDX
/

-- Reporting user (limited set of grant)
CREATE USER &USER_REPORT IDENTIFIED BY &USER_REPORT_PWD
 TEMPORARY TABLESPACE &TBS_TEMP
/
GRANT &ROLE_REPORT TO &USER_REPORT
/
ALTER USER &USER_REPORT DEFAULT ROLE ALL
/

-- Dashboard schema role
CREATE ROLE &ROLE_DASHBOARD NOT IDENTIFIED
/
GRANT CREATE SESSION TO &ROLE_DASHBOARD
/
GRANT CREATE SYNONYM TO &ROLE_DASHBOARD
/
GRANT CREATE TABLE TO &ROLE_DASHBOARD
/
GRANT CREATE VIEW TO &ROLE_DASHBOARD
/
GRANT CREATE SEQUENCE TO &ROLE_DASHBOARD
/
GRANT CREATE TYPE TO &ROLE_DASHBOARD
/
GRANT CREATE PROCEDURE TO &ROLE_DASHBOARD
/
GRANT CREATE TRIGGER TO &ROLE_DASHBOARD
/
GRANT CREATE OPERATOR TO &ROLE_DASHBOARD
/

-- Dashboard user
CREATE USER &USER_DASHBOARD
  IDENTIFIED BY &USER_DASHBOARD_PWD
  DEFAULT TABLESPACE &TBS_DASH
  TEMPORARY TABLESPACE &TBS_TEMP
/
GRANT &ROLE_DASHBOARD TO &USER_DASHBOARD
/
ALTER USER &USER_DASHBOARD DEFAULT ROLE ALL
/
ALTER USER &USER_DASHBOARD QUOTA UNLIMITED ON &TBS_DASH
/