Set up an Oracle database

To use an Oracle database for your TeamForge data, set up the Oracle database and tell the installer how to handle it.

TeamForge database setup

1. Make sure your database uses UTF8 or AL32UTF8 encoding. This is needed to support users in Asian languages.

   For information about discovering and changing the database encoding, see this Oracle knowledge base article.

2. Connect to your Oracle database.
   • SQL> connect <adminusername>@<db_name>/<adminpassword> as sysdba
3. Create the database user and password you will use to connect from TeamForge to Oracle.

   SQL> create user <sf user> identified by <sf passwd> default tablespace <your tablespace> temporary tablespace <temporary tablespace>;

4. Grant permissions to the user that you just created.
   • SQL> grant unlimited tablespace to <sf user>;
   • SQL> grant create snapshot to <sf user>;
   • SQL> grant create cluster to <sf user>;
   • SQL> grant create database link to <sf user>;
   • SQL> grant create procedure to <sf user>;
   • SQL> grant create sequence to <sf user>;
   • SQL> grant create trigger to <sf user>;
   • SQL> grant create type to <sf user>;
   • SQL> grant create view to <sf user>;
   • SQL> grant query rewrite to <sf user>;
   • SQL> grant alter session to <sf user>;
   • SQL> grant create table to <sf user>;
   • SQL> grant create session to <sf user>;
   • SQL> grant create any synonym to <sf user>;
   • SQL> exit
5. Create the database read-only user that you will use to connect from TeamForge.
   • SQL> create user <database_readonly_user> identified by <database_readonly_password> default tablespace <your tablespace> temporary tablespace <temporary tablespace>;
6. Grant the required permissions to the read-only user that you just created.
   • SQL> grant create session to <database_readonly_user>;
   • SQL> exit
7. Connect to your Oracle database as <sf user>.
   • SQL> connect <sf user>@<db_name>/<sf passwd>
8. Grant the 'create synonym' permission on TeamForge database to the read-only user that you just created.

   SQL> begin
   for i in (select table_name from user_tables) loop 
   execute immediate 'grant select on '|| i.table_name||' to <database_readonly_user>'; 
   execute immediate 'create synonym <database_readonly_user>.'||i.table_name||' for '||i.table_name||''; 
   end loop; 
   end;
   
   SQL> exit

Datamart setup

9. Make sure your database uses UTF8 or AL32UTF8 encoding. This is needed to support users in Asian languages.

   For information about discovering and changing the database encoding, see this Oracle knowledge base article.

10. Connect to your Oracle database.
    • SQL> connect <adminusername>@<db_name>/<adminpassword> as sysdba
11. Create the datamart user you will use to connect from TeamForge.

    SQL> create user <reports_database_user> identified by <reports_database_password> default tablespace <your tablespace> temporary tablespace <temporary tablespace>;

12. Grant permissions to the user that you just created.
    • SQL> grant unlimited tablespace to <reports_database_user>;
    • SQL> grant create snapshot to <reports_database_user>;
    • SQL> grant create cluster to <reports_database_user>;
    • SQL> grant create database link to <sreports_database_user>;
    • SQL> grant create procedure to <reports_database_user>;
    • SQL> grant create sequence to <reports_database_user>;
    • SQL> grant create trigger to <reports_database_user>;
    • SQL> grant create type to <reports_database_user>;
    • SQL> grant create view to <reports_database_user>;
    • SQL> grant query rewrite to <reports_database_user>;
    • SQL> grant alter session to <reports_database_user>;
    • SQL> grant create table to <reports_database_user>;
    • SQL> grant create session to <reports_database_user>;
    • SQL> grant create any synonym to <reports_database_user>;
    • SQL> exit
    Note: Replace <reports_database_user> with the datamart username specified in the site-options.conf and <reports_database_password> with the database password specified in site-options.conf.
13. Create the datamart read-only user that you will use to connect from TeamForge.
    • SQL> create user <reports_readonly_user> identified by <reports_readonly_password> default tablespace <your tablespace> temporary tablespace <temporary tablespace>;
14. Grant the required permissions to the read-only user that you just created.
    • SQL> grant create session to <reports_readonly_user>;
    • SQL> exit
    Note: The TeamForge installer creates the tables and default values for you.
15. Connect to your Oracle database as <reports_database_user>.
    • SQL> connect <reports_database_user>@<db_name>/<reports_database_password>
16. Grant the 'create synonym' permission on TeamForge datamart to the read-only user that you just created.

    SQL> begin
    for i in (select table_name from user_tables) loop 
    execute immediate 'grant select on '|| i.table_name||' to <reports_readonly_user>'; 
    execute immediate 'create synonym <reports_readonly_user>.'||i.table_name||' for '||i.table_name||''; 
    end loop; 
    end;
    
    SQL> exit