Creating tablespaces and users on Oracle

This topic describes how to create reports data warehouse tablespaces and users.

You can create tablespaces and users by using either of the following methods: 

Required tablespaces

The following table lists the Oracle tablespace names and purposes that are created by the downloaded scripts. You need to be aware of these tablespace names because the schema creation scripts use them.

Notes

  • You must create the tablespaces by using the same names that are specified in the following table. Creating the tablespaces with different names can impact the product execution.
  • The BSARA_DATA, BSARA_INDEX, BSARA_DATA2, BSARA_INDEX2, BSARA_DATA3, and BSARA_INDEX3 tablespaces should not be on the same physical disk as the BSARA_ETL_STAGE tablespace to prevent impact on the ETL performance of the reports data warehouse.

Oracle tablespaces

Database instance

Tablespace name

Purpose

1

BSARA_DATA

Storage of the reports data warehouse tables except for the Audit and Inventory tables

1

BSARA_INDEX

Storage of the reports data warehouse indexes except for the Audit and Inventory tables

1

BSARA_DATA2

Storage of the Inventory Snapshot tables

1

BSARA_INDEX2

Storage of the Inventory Snapshot indexes

1

BSARA_DATA3

Storage of the Audit tables

1

BSARA_INDEX3

Storage of the Audit indexes

1

BSARA_ETL_REPO

Storage of the ETL tool repository

1

BSARA_ETL_STAGE

Storage of the ETL staging tables that are created, used, and dropped by the ETL processes

Required users

The following table lists the users that are created by the downloaded scripts. You can use different user names than those used in the table examples by modifying the script. Make a note of the user names for reference during the TrueSight Server Automation - Data Warehouse configuration. These users are created with a default password, sa. You can modify the password as required.

Oracle users

User

Description

Required privileges

TSSADW_DW

This user has the following responsibilities:

  • Is owner of the reports database objects.
  • Executes ETL jobs (using JDBC connectivity) by reading data from the TrueSight Server Automation database across the database link.
  • Connects to the reports layer and executes report queries.

This user requires the following privileges:

  • connect
  • create database link
  • create procedure
  • create sequence
  • create synonym
  • create table
  • create trigger
  • create view
  • query rewrite
  • execute on DBMS_LOB

The user also needs an unlimited quota on the BSARA_DATA, BSARA_INDEX, BSARA_DATA2, BSARA_INDEX2, BSARA_DATA3, BSARA_INDEX3, and BSARA_ETL_STAGE tablespaces that are listed in .

TSSADW_ETL_MASTER

This user has the following responsibilities:

  • Is owner of the ETL master repository objects.
  • Uses JDBC connectivity for ETL job execution.

This user requires the following privileges:

  • connect
  • create database link
  • create procedure
  • create sequence
  • create synonym
  • create table
  • create trigger
  • create view

The user also needs an unlimited quota on the BSARA_ETL_REPO tablespace that is listed in .

TSSADW_ETL_WORK

This user has the following responsibilities:

  • Is owner of the ETL work repository objects.
  • Uses JDBC connectivity for ETL job execution.

This user requires the following privileges:

  • connect
  • create database link
  • create procedure
  • create sequence
  • create synonym
  • create table
  • create trigger
  • create view

The user also needs an unlimited quota on the BSARA_ETL_REPO tablespace that is listed in .

 

To create tablespaces and users from the scripts that are copied from the external files

  1. If you have not already done so, extract the contents of the TSSA-DW<version>external-files.zip into a temporary directory.
    The scripts for creating tablespaces and users are copied into the /Oracle directory.  
  2. Navigate to the Oracle directory.

  3. Open the create_oracle_tablespaces_and_users.sql script and edit it to modify the Oracle data file paths and user passwords as necessary.

    Warning

    Do not modify tablespace names.

  4. In the script, modify the value of the MAXSIZE parameter for the following tablespaces according to your sizing requirements.
    To calculate the sizing requirements, use the sizing worksheet as described in Sizing-for-new-product-installations-and-new-TrueSight-Server-Automation-installations.

    Note

    The sizing worksheet returns the tablespace size in GB. Convert it into MB before you modify the MAXSIZE parameter in the script.

     

    • BSARA_DATA
    • BSARA_INDEX
    • BSARA_DATA2 
    • BSARA_INDEX2
    • BSARA_DATA3 
    • BSARA_INDEX3
    • BSARA_ETL_STAGE
    • BSARA_ETL_REPO
  5. Save the script.

  6. Log on to the instance on which you want to create the reports data warehouse and log on to SQL Plus using a user name that has sysdba privileges. 
    For example, you can log on using the following commands:

    sqlplus /nolog
    connect sys/<password>@<TNS_ENTRY> as sysdba

  7. Run the following script:

    @ create_oracle_tablespaces_and_users.sql;

    This script creates the reports data warehouse tablespaces and users.

  8. Log out of SQL Plus.

To create tablespaces and users from the script that is downloaded during configuration

  1. Navigate to the directory in which you have downloaded the create_bdssa_oracle_users.sql script.

  2. Open the script and edit it to modify the Oracle data file paths and user passwords for Oracle tablespace and users, as necessary.

    Warning

    Do not modify the tablespace names.

  3. Modify the value of the MAXSIZE parameter for the following tablespaces according to your sizing requirements. To calculate the sizing requirements, use the sizing worksheet as described in Sizing-for-new-product-installations-and-new-TrueSight-Server-Automation-installations.

    Note

    The sizing worksheet returns the tablespace size in GB. Convert it into MB before you modify the MAXSIZE parameter in the script.

     

    • BSARA_DATA
    • BSARA_INDEX
    • BSARA_DATA2 
    • BSARA_INDEX2
    • BSARA_DATA3 
    • BSARA_INDEX3
    • BSARA_ETL_STAGE
    • BSARA_ETL_REPO

  4. Log on to the instance on which you want to create the reports data warehouse and log on to SQL Plus using a user name that has sysdba privileges. For example, you can log on using the following commands:

    sqlplus /nolog
    connect sys/<password>@<TNS_ENTRY> as sysdba
  5. At the SQL prompt, copy and paste the sections starting with the following names for creating reports data warehouse tablespaces:
    • CREATE TABLESPACE BSARA_DATA 
    • CREATE TABLESPACE BSARA_INDEX
    • CREATE TABLESPACE BSARA_DATA2
    • CREATE TABLESPACE BSARA_INDEX2
    • CREATE TABLESPACE BSARA_DATA3
    • CREATE TABLESPACE BSARA_INDEX3
    • CREATE TABLESPACE BSARA_ETL_STAGE
    • CREATE TABLESPACE BSARA_ETL_REPO
  6. Press Enter.
    The reports data warehouse tablespaces are created. 
  7. At the SQL prompt, copy and paste the sections starting with the following names for creating reports data warehouse, and ETL work and master repository users:
    • CREATE USER TSSADW_DW
    • CREATE USER TSSADW_ETL_WORK
    • CREATE USER TSSADW_ETL_MASTER 
  8. Press Enter.
    The reports data warehouse, and ETL work and master repository users are created.
  9. Log out of SQL Plus.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*