Creating tablespaces and user on Oracle database for TrueSight Smart Reporting

If you are planning to install TrueSight Smart Reporting, you must crate tablespaces and a database user with appropriate rights for TrueSight Smart Reporting in the Oracle database. The tablespaces and database user are in addition to the tablespaces and user that you created for the Report Engine on the Oracle database. You must provide the TrueSight Smart Reporting database user name when you install TrueSight Operations Management Report Engine.

The TrueSight Operations Management Report Engine installation files contain the create_TSR_repository_user_tablespaces.sql  script that you use to create the tablespaces and the user for TrueSight Smart Reporting. To run the script, you need a user with SYSDBA privileges. A successful execution of the script creates the required tablespaces and a user with appropriate rights. 

The create_TSR_repository_user_tablespaces.sql script creates the following users:

• Schema Owner: When prompted for the Database user name, the script creates the user with the provided name. This user is used to create schema while installing TrueSight Operations ManagementReport Engine. 
  
  The create_TSR_repository_user_tablespaces.sql script provides the following permissions to this user.

  Privilege Type	Role	Object	Privilege
  Explicit Object Privilege		DBMS_LOCK	EXECUTE
  Explicit System Privilege			CREATE MATERIALIZED VIEW
  Explicit System Privilege			CREATE TABLE
  Explicit System Privilege			CREATE VIEW
  Explicit System Privilege			QUERY REWRITE
  Explicit System Privilege			UNLIMITED TABLESPACE
  System Privilege From Role	CONNECT		CREATE SESSION
  System Privilege From Role	RESOURCE		CREATE CLUSTER
  System Privilege From Role	RESOURCE		CREATE INDEXTYPE
  System Privilege From Role	RESOURCE		CREATE OPERATOR
  System Privilege From Role	RESOURCE		CREATE PROCEDURE
  System Privilege From Role	RESOURCE		CREATE SEQUENCE
  System Privilege From Role	RESOURCE		CREATE TABLE
  System Privilege From Role	RESOURCE		CREATE TRIGGER
  System Privilege From Role	RESOURCE		CREATE TYPE

• Read-only User: The script, by default, creates the read-only user with reuniv name, and the RE#Adm1n password.

For the Read-only User, you can change the password after the successful installation. This user is used by BusinessObjects while creating universe connections.

To create tablespaces and user

1. Navigate to the Util folder.

2. If you are using Oracle version 18c or 19c, do the following:
   1. Copy the AdminDatabaseScripts folder that is attached to this knowledge article .
   2. Navigate to <TrueSight Report Engine install directory>\Reports\Util\ and replace the AdminDatabaseScripts folder with the folder that you just copied.
3. If you are using Oracle version 12c, copy the <TrueSight Report Engine install directory>\Reports\Util\AdminDatabaseScripts folder and paste it on the computer where you have installed the Reporting database. 

4. Log on to the Oracle database with the SYSDBA user.

5. Run the create_TSR_repository_user_tablespaces.sql script.
   For example, if your script is located in the d:\AdminDatabaseScripts folder, run the following command:

   @d:\AdminDatabaseScripts\create_TSR_repository_user_tablespaces.sql

6. When prompted, type the Reporting database user name and password. The default user name is tsrepo and the default password is re.
7. Type Y to continue, and then type the path for the datafile.

To create tablespaces and user on the pluggable database

If you have installed Oracle 12.1.0.1.0 with the Pluggable option, do the following:

1. Navigate to the Util folder.
2. Copy the AdminDatabaseScripts folder and paste it on the computer where you have installed the Reporting database.
3. Log on to Oracle 12c Container database as SYSDBA.

4. Run the show pdbs command to display the available pluggable databases and mode of the pluggable databases. 
   Ensure that the pluggable connection has READ WRITE as the open mode. If it has the Mounted option, run the following command to open the connection: 

   alter pluggable database <PluggableDatabase> open;

   where  <PluggableDatabase> is the name of the pluggable database that you provided during while installing Oracle or while creating the Pluggable Database.
   For example, run the following command:

   alter pluggable database PDBORCL open;

5. Run the following command to alter the session to use your pluggable database:

   alter session set container=<PluggableDatabase>;

   For example, run the following command:

   alter session set container=PDBORCL;

6. Run the create_TSR_repository_user_tablespaces.sql script.

7. When prompted, type the Reporting database user name and password. The default user name is tsrepo and the default password is re.
8. Type Y to continue, and then type the path for the datafile.