Creating Oracle Users and Tablespaces

SEI provides SQL scripts to create dedicated Oracle users and tablespaces. You must run the scripts BEFORE installing SEI. This step is required as of version 2022 R1.3.

Note

These scripts must be run with a user with administrative permissions on the server.

Follow these general steps:

Additional scenarios:

  • You can add space to the Oracle database as needed.

  • When uninstalling SEI, you can remove the Oracle users.

  • Click here to view additional information about Oracle.

Step 1: Revoke an existing user's permissions

If an Oracle user is already used in your SEI environment, you must revoke this user's permissions before proceeding.

  • Run the 0_RevokePermissions.sql script.

    Set the SEI_USER parameter to SEI.

/* Script to revoke permissions on existing users.
** 
** # Description
** The purpose of this script is to revoke the current permissions for a given user.
** 
** # Permissions
** 
** # How to use it
** - The script needs to be run from a user with administrative permissions on the server
** - Fill the variables after the comment `Change the values`
**   * SEI_USER: Name of the SEI user
**   * ENABLE_LOGING: Enable the script to print some logs
** - Run the script
*/

set serveroutput on;

DECLARE
    /*************************/
    /* Variables declaration */
    /*************************/
    SEI_USER                    VARCHAR2(1000);
    ENABLE_LOGING               BOOLEAN;
    -- Other variables
    v_SEI_USER_CHECK            NUMBER;

BEGIN
    /*********************/
    /* Change the values */
    /*********************/
    -- User information
    SEI_USER                       := 'SEI_SEED';
    -- Other
    ENABLE_LOGING                  := true;
        
    -- Enabling logs
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;
    
    /***********************************************************/
    /*                        Variables                        */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  SEI Username =>  ' || SEI_USER);
    dbms_output.new_line;
    
    /***********************************************************/
    /*                   TableSpace / Users                    */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/*    Permissions     */');
    dbms_output.put_line('/**********************/');
    -- Check if the user SEI Objects exists
    SELECT COUNT(1) INTO v_SEI_USER_CHECK FROM DBA_USERS WHERE USERNAME = SEI_USER;
    
    -- Creating user SEI Objects
    IF ( v_SEI_USER_CHECK <> 0 ) THEN
        dbms_output.put_line('/*******************************/');
        dbms_output.put_line('/*    Revoking Permissions     */');
        dbms_output.put_line('/*******************************/');
        
        dbms_output.put_line('  Revoking granted roles ...');
        for r in ( select * from dba_role_privs
                   where grantee = SEI_USER )
        loop
            EXECUTE IMMEDIATE 'revoke ' || r.granted_role ||' from ' || r.grantee;
            dbms_output.put_line('    Revoked: revoke ' || r.granted_role ||' from ' || r.grantee ||';');
        end loop;
        
        dbms_output.put_line('  Revoking granted system privileges ...');
        
        for r in ( select * from dba_sys_privs
                   where grantee = SEI_USER )
        loop
            EXECUTE IMMEDIATE 'revoke ' || r.privilege ||' from ' || r.grantee;
            dbms_output.put_line('    Revoked: revoke ' || r.privilege ||' from ' || r.grantee ||';');
        end loop;
        
        dbms_output.put_line('  Revoking granted access privileges ...');
        
        for r in ( select * from dba_tab_privs
                   where grantee = SEI_USER )
        loop
            EXECUTE IMMEDIATE 'revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee;
            dbms_output.put_line('    Revoked: revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee ||';');
        end loop;
    ELSE
        dbms_output.put_line('  The UserName "' || SEI_USER || ' doesn''t exists');
    END IF;
END;

Step 2: Create the SEI user and schema

  • Run the 1_ConfigDB.sql script.

    Configure the parameters as follows:

    1. The SEI_USER parameter must be set to SEI.

    2. The SEI_TABLE_SPACE parameter should be set to SEI but you can change it another name.

    3. The SEI_TMP_TABLE_SPACE parameter should be set to SEI_TMP but you can change it another name.

    4. Set the other parameters as needed.

/* Script to create users for SEI.
** 
** # Description
** The purpose of this script is to create an user specific for the SEI application.
** The script will create an Oracle user with an associated TableSpace (or can use an existing one)
** This script should be run for SEI and BILICENSE users
** 
** # Permissions
** The SEI user will have all rights on his own schema only
** This script will remove all existing permissions on the existing user
** 
** # How to use it
** - The script needs to be run from a user with administrative permissions on the server
** - Fill the variables after the comment `Change the values`
**   * SEI_USER: Name of the SEI user
**   * SEI_USER_PWD: Password for the user
**   * SEI_TABLE_SPACE: Name of the main table space for the user
**   * SEI_TABLE_SPACE_LOG: Enable the logging for the main table space
**   * SEI_TABLE_SPACE_COMP: Enable the compression for the main table space (Compression has to be enabled server wide)
**   * SEI_TMP_TABLE_SPACE: Name of the temporary table space
**   * ENABLE_LOGING: Enable the script to print some logs
**   * REVOKE_EXIST_PERM: If the user already exists, define if we want to remove the permissions already granted
** - Run the script
*/

set serveroutput on;

DECLARE
    /*************************/
    /* Variables declaration */
    /*************************/
    SEI_USER                        VARCHAR2(1000);
    SEI_USER_PWD                    VARCHAR2(1000);
    SEI_TABLE_SPACE                 VARCHAR2(1000);
    SEI_TABLE_SPACE_LOG             BOOLEAN;
    SEI_TABLE_SPACE_COMP            BOOLEAN;
    SEI_TMP_TABLE_SPACE             VARCHAR2(1000);
    ENABLE_LOGING                   BOOLEAN;
    REVOKE_EXIST_PERM               BOOLEAN;
    -- Other variables
    v_CUSTOM_TABLE_SPACE_PATH       VARCHAR2(1000);
    v_SEI_USER_CHECK                NUMBER;
    v_SEI_TABLE_SPACE_LOG           VARCHAR2(1000);
    v_SEI_TABLE_SPACE_COMP          VARCHAR2(1000);
    TablespaceExistsExcep            EXCEPTION;
    FileExistsExcep                    EXCEPTION;
    PRAGMA EXCEPTION_INIT(TablespaceExistsExcep,-1543);
    PRAGMA EXCEPTION_INIT(FileExistsExcep,-1119);
BEGIN
    /*********************/
    /* Change the values */
    /*********************/
    -- User information
    SEI_USER                    := 'SEI';
    SEI_USER_PWD                := 'SEI_PWD';
     -- TableSpace information
    SEI_TABLE_SPACE             := 'SEI';
    SEI_TABLE_SPACE_LOG         := true;
    SEI_TABLE_SPACE_COMP        := false;
    
    SEI_TMP_TABLE_SPACE         := 'SEI_TMP';
    -- Other
    ENABLE_LOGING               := true;
    REVOKE_EXIST_PERM           := false;
    
    -- Getting custom datafile path
    SELECT VALUE INTO v_CUSTOM_TABLE_SPACE_PATH FROM v$parameter WHERE NAME ='db_create_file_dest' ;
    IF v_CUSTOM_TABLE_SPACE_PATH is not null THEN
        v_CUSTOM_TABLE_SPACE_PATH := v_CUSTOM_TABLE_SPACE_PATH || '/';
    END IF;
        
    -- Enabling logs
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;
    
    -- Preparing setting for the table spaces
    -- SEI
    IF ( SEI_TABLE_SPACE_LOG = true ) THEN
        v_SEI_TABLE_SPACE_LOG := '';
    ELSE
        v_SEI_TABLE_SPACE_LOG := ' NOLOGGING ';
    END IF;
    
    IF ( SEI_TABLE_SPACE_COMP = true ) THEN
        v_SEI_TABLE_SPACE_COMP := ' DEFAULT COMPRESS ';
    ELSE
        v_SEI_TABLE_SPACE_COMP := '';
    END IF;
    
    /***********************************************************/
    /*                        Variables                        */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  SEI Username =>  ' || SEI_USER);
    dbms_output.put_line('  SEI Password => ' || SEI_USER_PWD);
    dbms_output.put_line('  /* Table space informations */');
    dbms_output.put_line('    SEI TableSpace');
    dbms_output.put_line('      Name =>  ' || SEI_TABLE_SPACE);
    dbms_output.put_line('      Loging =>  ' || sys.diutil.bool_to_int(SEI_TABLE_SPACE_LOG));
    dbms_output.put_line('      Compression =>  ' || sys.diutil.bool_to_int(SEI_TABLE_SPACE_COMP));
    dbms_output.put_line('    SEI TMP TableSpace');
    dbms_output.put_line('      Name =>  ' || SEI_TMP_TABLE_SPACE);
    
    IF v_CUSTOM_TABLE_SPACE_PATH is not null THEN
        dbms_output.put_line('    Custom datafile path detected =>  ' || v_CUSTOM_TABLE_SPACE_PATH);
    END IF;
    dbms_output.new_line;
    
    /***********************************************************/
    /*                   TableSpace / Users                    */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* TableSpace / Users */');
    dbms_output.put_line('/**********************/');
    -- Check if the user SEI exists
    SELECT COUNT(1) INTO v_SEI_USER_CHECK FROM DBA_USERS WHERE USERNAME = SEI_USER;
    
    -- Main TableSpace
    IF SEI_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE TABLESPACE '
                                  || SEI_TABLE_SPACE
                                  || ' DATAFILE '''
                                  || v_CUSTOM_TABLE_SPACE_PATH || SEI_TABLE_SPACE
                                  || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M'
                                  || v_SEI_TABLE_SPACE_COMP
                                  || v_SEI_TABLE_SPACE_LOG;
            dbms_output.put_line('  TableSpace "' || SEI_TABLE_SPACE || '" created');
        EXCEPTION
            WHEN TablespaceExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || SEI_TABLE_SPACE || '" already exists');
                NULL;
            WHEN FileExistsExcep THEN
                dbms_output.put_line('  The file associated to the TableSpace "' || SEI_TABLE_SPACE || '" already exists');
                NULL;
        END;
    ELSE
        dbms_output.put_line('  The SEI_TABLE_SPACE variable was not filled so tablespace will not be created');
    END IF;
    
    -- Temp TableSpace
    IF SEI_TMP_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLESPACE '
                                  || SEI_TMP_TABLE_SPACE
                                  || ' TEMPFILE '''
                                  || v_CUSTOM_TABLE_SPACE_PATH || SEI_TMP_TABLE_SPACE
                                  || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M';                                
            dbms_output.put_line('  TableSpace "' || SEI_TMP_TABLE_SPACE || '" created');
        EXCEPTION
            WHEN TablespaceExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || SEI_TMP_TABLE_SPACE || '" already exists');
            NULL;
            WHEN FileExistsExcep THEN
                dbms_output.put_line('  The file associated to the TableSpace "' || SEI_TMP_TABLE_SPACE || '" already exists');
            NULL;
        END;
    ELSE
        dbms_output.put_line('  The SEI_TMP_TABLE_SPACE variable was not filled so tablespace will not be created');
    END IF;
    
    -- Creating user SEI
    IF ( v_SEI_USER_CHECK = 0 ) THEN
        EXECUTE IMMEDIATE 'CREATE USER '
                          || SEI_USER
                          || ' IDENTIFIED BY '
                          || SEI_USER_PWD
                          || '  DEFAULT TABLESPACE '
                          || SEI_TABLE_SPACE
                          || ' TEMPORARY TABLESPACE '
                          || SEI_TMP_TABLE_SPACE
                          || ' QUOTA UNLIMITED ON '
                          || SEI_TABLE_SPACE;
        dbms_output.put_line('  UserName "' || SEI_USER || ' created');
    ELSE
        dbms_output.put_line('  The UserName "' || SEI_USER || ' already exists');

        IF ( REVOKE_EXIST_PERM = true ) THEN
            dbms_output.put_line('/*******************************/');
            dbms_output.put_line('/*    Revoking Permissions     */');
            dbms_output.put_line('/*******************************/');
            
            dbms_output.put_line('  Revoking granted roles ...');
            for r in ( select * from dba_role_privs
                    where grantee = SEI_USER )
            loop
                EXECUTE IMMEDIATE 'revoke ' || r.granted_role ||' from ' || r.grantee;
                dbms_output.put_line('    Revoked: revoke ' || r.granted_role ||' from ' || r.grantee ||';');
            end loop;
            
            dbms_output.put_line('  Revoking granted system privileges ...');
            
            for r in ( select * from dba_sys_privs
                    where grantee = SEI_USER )
            loop
                EXECUTE IMMEDIATE 'revoke ' || r.privilege ||' from ' || r.grantee;
                dbms_output.put_line('    Revoked: revoke ' || r.privilege ||' from ' || r.grantee ||';');
            end loop;
            
            dbms_output.put_line('  Revoking granted access privileges ...');
            
            for r in ( select * from dba_tab_privs
                    where grantee = SEI_USER )
            loop
                EXECUTE IMMEDIATE 'revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee;
                dbms_output.put_line('    Revoked: revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee ||';');
            end loop;
        ELSE
            dbms_output.put_line('  Current permissions of "' || SEI_USER || ' won''t be impacted');
        END IF;
        
    END IF;
    
    /***********************************************************/
    /*                       Permissions                       */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/*    Permissions     */');
    dbms_output.put_line('/**********************/');
    
    -- Grant General Authorization for SEI
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CONNECT, CREATE TYPE, CREATE VIEW, CREATE SYNONYM, CREATE TRIGGER, RESOURCE TO '
    || SEI_USER;
    dbms_output.put_line('  Username '|| SEI_USER || ' has been granted the following permissions on his own shemas:');
    dbms_output.put_line('    - CREATE PROCEDURE');
    dbms_output.put_line('    - CREATE SESSION');
    dbms_output.put_line('    - CREATE TABLE');
    dbms_output.put_line('    - CONNECT');
    dbms_output.put_line('    - CREATE TYPE');
    dbms_output.put_line('    - CREATE VIEW');
    dbms_output.put_line('    - CREATE SYNONYM');
    dbms_output.put_line('    - CREATE TRIGGER');
END;

Step 3: Create the BILicense user and schema

  • Run the 1_ConfigDB.sql script again.

    Configure the parameters as follows:

    1. The SEI_USER parameter must be set to BILicense.

    2. The SEI_TABLE_SPACE parameter should be set to BILicense but you can change it another name.

    3. The SEI_TMP_TABLE_SPACE parameter should be set to BILicense_TMP but you can change it another name.

    4. Set the other parameters as needed.

Step 4: Create the SEICube user

This user will have access to the cube data; this includes access to all schemas (users). You can exclude access for specific tables.

  • Run the 2_DataDB.sql script.

    Configure the parameters as needed, as described in the script instructions. Make sure to provide the access permissions using these parameters:

    • SCHEMA_LIST

    • EXCLUSION_TB_LIST

    • EXCLUSION_VW_LIST

/* Script to create users for SEI Cube.
** 
** # Description
** The purpose of this script is to create an user specific for the SEI application.
** The script will create an Oracle user with an associated TableSpace (or can use an existing one)
** The script will also give some permissions on other schemas (users)
** 
** # Permissions
** The SEI_SEED user will have all rights on his own schema
** The SEI_SEED user will have the right to read all tables from the specified schema in the variable `SCHEMA_LIST`
** The SEI_SEED user will have the right to read all views from the specified schema in the variable `SCHEMA_LIST`
** The SEI_SEED user will have the right to create any trigger across the database
** It will be possible to specify some exclusions tables/views to not give access, see more info in the How to use it section
** This script will remove all existing permissions on the existing user
** 
** # How to use it
** - The script needs to be run from a user with administrative permissions on the server
** - Fill the variables after the comment `Change the values`
**   * SEI_CUSTOM_USER: Name of the SEI Cube user
**   * SEI_CUSTOM_USER_PWD: Password for the user
**   * SEI_CUSTOM_TABLE_SPACE: Name of the main table space for the user
**   * SEI_CUSTOM_TABLE_SPACE_LOG: Enable the logging for the main table space
**   * SEI_CUSTOM_TABLE_SPACE_COMP: Enable the compression for the main table space (Compression has to be enabled server wide)
**   * SEI_CUSTOM_TMP_TABLE_SPACE: Name of the temporary table space
**   * ENABLE_LOGING: Enable the script to print some logs
**   * REVOKE_EXIST_PERM: If the user already exists, define if we want to remove the permissions already granted
**
**   The following parameters are for the permissions
**   * SCHEMA_LIST: List of the schemas (users) tables/views that our user will be able to READ (SELECT) only
**   * EXCLUSION_TB_LIST: List of the tables to exclude for the grant select. Format is "SCHEMA"."TABLE"
**   * EXCLUSION_VW_LIST: List of the views to exclude for the grant select. Format is "SCHEMA"."VIEW"
** - Run the script
**
** # Things to know
** !!! In Oracle, to give the READ access on another schema, we need to do it table by table.
**     => By doing so the grants are point in time and if new tables are added in the future, the script will need to be re-run (it will only update the table grants).
** !!! In Oracle, to give the CREATE TABLE/TRIGGER permission on another schema, the user needs to have a server wide permission so it can create them in amy schema.
*/

set serveroutput on;

DECLARE
    /*************************/
    /* Variables declaration */
    /*************************/
    SEI_CUSTOM_USER                 VARCHAR2(1000);
    SEI_CUSTOM_USER_PWD             VARCHAR2(1000);
    SEI_CUSTOM_TABLE_SPACE          VARCHAR2(1000);
    SEI_CUSTOM_TABLE_SPACE_LOG      BOOLEAN;
    SEI_CUSTOM_TABLE_SPACE_COMP     BOOLEAN;
    SEI_CUSTOM_TMP_TABLE_SPACE      VARCHAR2(1000);
    SCHEMA_LIST                     dbms_sql.varchar2_table;
    EXCLUSION_TB_LIST               dbms_sql.varchar2_table;
    EXCLUSION_VW_LIST               dbms_sql.varchar2_table;
    ENABLE_LOGING                   BOOLEAN;
    REVOKE_EXIST_PERM               BOOLEAN;
    -- Other variables
    v_CUSTOM_TABLE_SPACE_PATH       VARCHAR2(1000);
    v_SEI_CUSTOM_USER_CHECK         NUMBER;
    v_SEI_CUSTOM_TABLE_SPACE_LOG    VARCHAR2(1000);
    v_SEI_CUSTOM_TABLE_SPACE_COMP   VARCHAR2(1000);
    v_SEI_CUSTOM_TMP_TS_COMP        VARCHAR2(1000);
    TablespaceExistsExcep            EXCEPTION;
    FileExistsExcep                    EXCEPTION;
    PRAGMA EXCEPTION_INIT(TablespaceExistsExcep,-1543);
    PRAGMA EXCEPTION_INIT(FileExistsExcep,-1119);
BEGIN
    /*********************/
    /* Change the values */
    /*********************/
    -- User information
    SEI_CUSTOM_USER                     := 'SEI_SEED';
    SEI_CUSTOM_USER_PWD                 := 'SEI_SEED_PWD';
     -- TableSpace information
    SEI_CUSTOM_TABLE_SPACE              := 'SEI_SEED';
    SEI_CUSTOM_TABLE_SPACE_LOG          := true;
    SEI_CUSTOM_TABLE_SPACE_COMP         := false;
    
    SEI_CUSTOM_TMP_TABLE_SPACE          := 'SEI_SEED_TMP';
    -- List of schemas
    SCHEMA_LIST(1)                      := 'X3FOLDER';
    -- Exclusion list
    --EXCLUSION_TB_LIST(1)                := '"X3FOLDER"."TABLE"';
    --EXCLUSION_VW_LIST(1)                := '"X3FOLDER"."VIEW"';
    -- Other
    ENABLE_LOGING                       := true;
    REVOKE_EXIST_PERM                   := false;
    
    -- Getting custom datafile path
    SELECT VALUE INTO v_CUSTOM_TABLE_SPACE_PATH FROM v$parameter WHERE NAME ='db_create_file_dest' ;
    IF v_CUSTOM_TABLE_SPACE_PATH is not null THEN
        v_CUSTOM_TABLE_SPACE_PATH := v_CUSTOM_TABLE_SPACE_PATH || '/';
    END IF;
        
    -- Enabling logs
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;
    
    -- Preparing setting for the table spaces
    -- SEI Objetcs
    IF ( SEI_CUSTOM_TABLE_SPACE_LOG = true ) THEN
        v_SEI_CUSTOM_TABLE_SPACE_LOG := '';
    ELSE
        v_SEI_CUSTOM_TABLE_SPACE_LOG := ' NOLOGGING ';
    END IF;
    
    IF ( SEI_CUSTOM_TABLE_SPACE_COMP = true ) THEN
        v_SEI_CUSTOM_TABLE_SPACE_COMP := ' DEFAULT COMPRESS ';
    ELSE
        v_SEI_CUSTOM_TABLE_SPACE_COMP := '';
    END IF;
    
    /***********************************************************/
    /*                        Variables                        */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  SEI Objects Username =>  ' || SEI_CUSTOM_USER);
    dbms_output.put_line('  SEI Objects Password => ' || SEI_CUSTOM_USER_PWD);
    dbms_output.put_line('  /* Table space informations */');
    dbms_output.put_line('    SEI Objects TableSpace');
    dbms_output.put_line('      Name =>  ' || SEI_CUSTOM_TABLE_SPACE);
    dbms_output.put_line('      Loging =>  ' || sys.diutil.bool_to_int(SEI_CUSTOM_TABLE_SPACE_LOG));
    dbms_output.put_line('      Compression =>  ' || sys.diutil.bool_to_int(SEI_CUSTOM_TABLE_SPACE_COMP));
    dbms_output.put_line('    SEI Objects TMP TableSpace');
    dbms_output.put_line('      Name =>  ' || SEI_CUSTOM_TMP_TABLE_SPACE);
    IF v_CUSTOM_TABLE_SPACE_PATH is not null THEN
        dbms_output.put_line('    Custom datafile path detected =>  ' || v_CUSTOM_TABLE_SPACE_PATH);
    END IF;
    FOR i IN SCHEMA_LIST.FIRST .. SCHEMA_LIST.LAST
    LOOP
        dbms_output.put_line('  Schemas =>  ' || SCHEMA_LIST(i));
    END LOOP;
    IF EXCLUSION_TB_LIST.count > 0 THEN
        FOR i IN EXCLUSION_TB_LIST.FIRST .. EXCLUSION_TB_LIST.LAST
        LOOP
            dbms_output.put_line('  Exclusion tables =>  ' || EXCLUSION_TB_LIST(i));
        END LOOP;
    END IF;
    IF EXCLUSION_VW_LIST.count > 0 THEN
        FOR i IN EXCLUSION_VW_LIST.FIRST .. EXCLUSION_VW_LIST.LAST
        LOOP
            dbms_output.put_line('  Exclusion views =>  ' || EXCLUSION_VW_LIST(i));
        END LOOP;
    END IF;
    dbms_output.new_line;
    
    /***********************************************************/
    /*                   TableSpace / Users                    */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* TableSpace / Users */');
    dbms_output.put_line('/**********************/');
    -- Check if the user SEI Objects exists
    SELECT COUNT(1) INTO v_SEI_CUSTOM_USER_CHECK FROM DBA_USERS WHERE USERNAME = SEI_CUSTOM_USER;
    
    -- Main TableSpace
    IF SEI_CUSTOM_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE TABLESPACE '
                                  || SEI_CUSTOM_TABLE_SPACE
                                  || ' DATAFILE '''
                                  || v_CUSTOM_TABLE_SPACE_PATH || SEI_CUSTOM_TABLE_SPACE
                                  || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M'
                                  || v_SEI_CUSTOM_TABLE_SPACE_COMP
                                  || v_SEI_CUSTOM_TABLE_SPACE_LOG;
            dbms_output.put_line('  TableSpace "' || SEI_CUSTOM_TABLE_SPACE || '" created');
        EXCEPTION
            WHEN TablespaceExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || SEI_CUSTOM_TABLE_SPACE || '" already exists');
            NULL;
            WHEN FileExistsExcep THEN
                dbms_output.put_line('  The file associated to the TableSpace "' || SEI_CUSTOM_TABLE_SPACE || '" already exists');
            NULL;
        END;
    ELSE
        dbms_output.put_line('  The SEI_CUSTOM_TABLE_SPACE variable was not filled so tablespace will not be created');
    END IF;
    
    -- Temp TableSpace
    IF SEI_CUSTOM_TMP_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLESPACE '
                                  || SEI_CUSTOM_TMP_TABLE_SPACE
                                  || ' TEMPFILE '''
                                  || v_CUSTOM_TABLE_SPACE_PATH || SEI_CUSTOM_TMP_TABLE_SPACE
                                  || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M';
            dbms_output.put_line('  TableSpace "' || SEI_CUSTOM_TMP_TABLE_SPACE || '" created');
        EXCEPTION
            WHEN TablespaceExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || SEI_CUSTOM_TMP_TABLE_SPACE || '" already exists');
            NULL;
            WHEN FileExistsExcep THEN
                dbms_output.put_line('  The file associated to the TableSpace "' || SEI_CUSTOM_TMP_TABLE_SPACE || '" already exists');
            NULL;
        END;
    ELSE
        dbms_output.put_line('  The SEI_CUSTOM_TMP_TABLE_SPACE variable was not filled so tablespace will not be created');
    END IF;
    
    -- Creating user SEI Objects
    IF ( v_SEI_CUSTOM_USER_CHECK = 0 ) THEN
        EXECUTE IMMEDIATE 'CREATE USER '
                          || SEI_CUSTOM_USER
                          || ' IDENTIFIED BY '
                          || SEI_CUSTOM_USER_PWD
                          || '  DEFAULT TABLESPACE '
                          || SEI_CUSTOM_TABLE_SPACE
                          || ' TEMPORARY TABLESPACE '
                          || SEI_CUSTOM_TMP_TABLE_SPACE
                          || ' QUOTA UNLIMITED ON '
                          || SEI_CUSTOM_TABLE_SPACE;
        dbms_output.put_line('  UserName "' || SEI_CUSTOM_USER || ' created');
    ELSE
        dbms_output.put_line('  The UserName "' || SEI_CUSTOM_USER || ' already exists');

        IF ( REVOKE_EXIST_PERM = true ) THEN
            dbms_output.put_line('/*******************************/');
            dbms_output.put_line('/*    Revoking Permissions     */');
            dbms_output.put_line('/*******************************/');
            
            dbms_output.put_line('  Revoking granted roles ...');
            for r in ( select * from dba_role_privs
                    where grantee = SEI_CUSTOM_USER )
            loop
                EXECUTE IMMEDIATE 'revoke ' || r.granted_role ||' from ' || r.grantee;
                dbms_output.put_line('    Revoked: revoke ' || r.granted_role ||' from ' || r.grantee ||';');
            end loop;
            
            dbms_output.put_line('  Revoking granted system privileges ...');
            
            for r in ( select * from dba_sys_privs
                    where grantee = SEI_CUSTOM_USER )
            loop
                EXECUTE IMMEDIATE 'revoke ' || r.privilege ||' from ' || r.grantee;
                dbms_output.put_line('    Revoked: revoke ' || r.privilege ||' from ' || r.grantee ||';');
            end loop;
            
            dbms_output.put_line('  Revoking granted access privileges ...');
            
            for r in ( select * from dba_tab_privs
                    where grantee = SEI_CUSTOM_USER )
            loop
                EXECUTE IMMEDIATE 'revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee;
                dbms_output.put_line('    Revoked: revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee ||';');
            end loop;
        ELSE
            dbms_output.put_line('  Current permissions of "' || SEI_CUSTOM_USER || ' won''t be impacted');
        END IF;
    END IF;
    
    /***********************************************************/
    /*                       Permissions                       */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/*    Permissions     */');
    dbms_output.put_line('/**********************/');
    
    -- Grant General Authorization for SEI Objects
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CONNECT, CREATE TYPE, CREATE VIEW, CREATE SYNONYM, CREATE TRIGGER, RESOURCE TO '
    || SEI_CUSTOM_USER;
    dbms_output.put_line('  Username '|| SEI_CUSTOM_USER || ' has been granted the following permissions on his own shemas:');
    dbms_output.put_line('    - CREATE PROCEDURE');
    dbms_output.put_line('    - CREATE SESSION');
    dbms_output.put_line('    - CREATE TABLE');
    dbms_output.put_line('    - CONNECT');
    dbms_output.put_line('    - CREATE TYPE');
    dbms_output.put_line('    - CREATE VIEW');
    dbms_output.put_line('    - CREATE SYNONYM');
    dbms_output.put_line('    - CREATE TRIGGER');
    
    -- Grant Trigger Authorization for SEI Objects
    EXECUTE IMMEDIATE 'GRANT CREATE ANY TRIGGER TO ' || SEI_CUSTOM_USER;
    dbms_output.put_line('  Username '|| SEI_CUSTOM_USER || ' has been granted the CREATE ANY TRIGGER permissions');
    dbms_output.put_line('    !!! The CREATE ANY TRIGGER applies server wide !!!');
    
    -- Grant READ permission on schemas for SEI Objects users
    FOR i IN SCHEMA_LIST.FIRST .. SCHEMA_LIST.LAST
    LOOP
        dbms_output.put_line('  Iterating on tables for schema ' || SCHEMA_LIST(i));
        FOR x IN (
            SELECT table_name FROM dba_all_tables WHERE owner = SCHEMA_LIST(i)
        ) LOOP
            EXECUTE IMMEDIATE 'GRANT SELECT ON "'
                              || SCHEMA_LIST(i)
                              || '"."'
                              || x.table_name
                              || '" TO '
                              || SEI_CUSTOM_USER;
            dbms_output.put_line('    Granting SELECT permissions on '
                                 || SCHEMA_LIST(i)
                                 || '.'
                                 || x.table_name);
        END LOOP;
        dbms_output.put_line('  Iterating on views for schema ' || SCHEMA_LIST(i));
        FOR x IN (
            SELECT
                view_name
            FROM
                dba_views
            WHERE
                    owner = SCHEMA_LIST(i)
                AND view_name <> 'LEGGTEYGRP'
        ) LOOP
            EXECUTE IMMEDIATE 'GRANT SELECT ON "'
                              || SCHEMA_LIST(i)
                              || '"."'
                              || x.view_name
                              || '" TO '
                              || SEI_CUSTOM_USER;
            dbms_output.put_line('  Granting SELECT permissions on '
                                 || SCHEMA_LIST(i)
                                 || '.'
                                 || x.view_name);
        END LOOP;
    END LOOP;
    
    -- Revoking permissions
    dbms_output.put_line('  Revoking permissions');
    IF EXCLUSION_TB_LIST.count > 0 THEN
        FOR i IN EXCLUSION_TB_LIST.FIRST .. EXCLUSION_TB_LIST.LAST
        LOOP
            EXECUTE IMMEDIATE 'REVOKE SELECT ON '
                                  || EXCLUSION_TB_LIST(i)
                                  || ' FROM '
                                  || SEI_CUSTOM_USER;
            dbms_output.put_line('    Revoking select access for table ' || EXCLUSION_TB_LIST(i));
        END LOOP;
    END IF;
    IF EXCLUSION_VW_LIST.count > 0 THEN
        FOR i IN EXCLUSION_VW_LIST.FIRST .. EXCLUSION_VW_LIST.LAST
        LOOP
            EXECUTE IMMEDIATE 'REVOKE SELECT ON '
                                  || EXCLUSION_TB_LIST(i)
                                  || ' FROM '
                                  || SEI_CUSTOM_USER;
            dbms_output.put_line('    Revoking select access for view ' || EXCLUSION_VW_LIST(i));
        END LOOP;
    END IF;
END;

Step 5: Install the SEI packages

After creating the Oracle users, you need to run the SEI executables to create the Oracle tables.

  1. Perform the steps to install the License Server.

  2. Perform the steps to install the Central Point.

  3. Perform the steps to install the SEI application (basic or advanced installation).

Additional Scenarios

Adding space to the Oracle database

If you need to add additional space to a table, you can run the script below to add 32 GB of space.

  1. Run the 3_AddNewDataFile.sql script.

    Set these parameters as described in the script instructions:

    • TABLE_SPACE

    • DATA_FILE_NAME

    • ENABLE_LOGING

/* Script to add a new datafile to an existing table space.
** 
** # Description
** The purpose of this script is to add a new datafile to a table space to avoid storage issue.
** 
** # How to use it
** - The script needs to be run from a user with administrative permissions on the server
** - Fill the variables after the comment `Change the values`
**   * TABLE_SPACE: Name of the table space to update
**   * DATA_FILE_NAME: Name of the data file to add
**   * ENABLE_LOGING: Enable the script to print some logs
** - Run the script
**
** # Things to know
** !!! You can change the size, maxsize, etc. directly in the script to accomodate your need
*/

set serveroutput on;

DECLARE
    /*************************/
    /* Variables declaration */
    /*************************/
    TABLE_SPACE             VARCHAR2(1000);
    DATA_FILE_NAME          VARCHAR2(1000);
    ENABLE_LOGING           BOOLEAN;
    -- Other variables
    v_CUSTOM_TABLE_SPACE_PATH       VARCHAR2(1000);
    TablespaceExistsExcep    EXCEPTION;
    FileExistsExcep            EXCEPTION;
    PRAGMA EXCEPTION_INIT(TablespaceExistsExcep,-1543);
    PRAGMA EXCEPTION_INIT(FileExistsExcep,-1537);
BEGIN
    /*********************/
    /* Change the values */
    /*********************/
    TABLE_SPACE         := 'SEI_SEED';
    DATA_FILE_NAME      := 'SEI_SEED_NEW2';
    ENABLE_LOGING       := true;
    
    -- Enabling logs
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;
    
    -- Getting custom datafile path
    SELECT VALUE INTO v_CUSTOM_TABLE_SPACE_PATH FROM v$parameter WHERE NAME ='db_create_file_dest' ;
    IF v_CUSTOM_TABLE_SPACE_PATH is not null THEN
        v_CUSTOM_TABLE_SPACE_PATH := v_CUSTOM_TABLE_SPACE_PATH || '/';
    END IF;
    
    /***********************************************************/
    /*                        Variables                        */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  Table space name =>  ' || TABLE_SPACE);
    dbms_output.put_line('  Datafile name =>  ' || DATA_FILE_NAME);
    IF v_CUSTOM_TABLE_SPACE_PATH is not null THEN
        dbms_output.put_line('    Custom datafile path detected =>  ' || v_CUSTOM_TABLE_SPACE_PATH);
    END IF;
    
    /***********************************************************/
    /*                 TableSpace / Datafile                   */
    /***********************************************************/
    dbms_output.put_line('/*************************/');
    dbms_output.put_line('/* TableSpace / Datafile */');
    dbms_output.put_line('/*************************/');
    IF TABLE_SPACE is not null THEN
        IF DATA_FILE_NAME is not null THEN
            BEGIN
                EXECUTE IMMEDIATE 'ALTER TABLESPACE '
                                      || TABLE_SPACE
                                      || ' ADD DATAFILE '''
                                      || v_CUSTOM_TABLE_SPACE_PATH || DATA_FILE_NAME
                                      || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M';
                dbms_output.put_line('  TableSpace "' || TABLE_SPACE || '" updated to use also the datafile ' || v_CUSTOM_TABLE_SPACE_PATH || DATA_FILE_NAME || '.DBF');
            EXCEPTION
                WHEN TablespaceExistsExcep THEN
                    dbms_output.put_line('  The TableSpace "' || TABLE_SPACE || '" already exists');
                NULL;
                WHEN FileExistsExcep THEN
                    dbms_output.put_line('  The date file "' || DATA_FILE_NAME || '" already exists');
                NULL;
            END;
        ELSE
            dbms_output.put_line('  The DATA_FILE_NAME variable was not filled so databfile will not be created');
        END IF;
    ELSE
        dbms_output.put_line('  The TABLE_SPACE variable was not filled so tablespace will not be updated');
    END IF;
END;

Removing the Oracle users and tablespaces

  1. Use the SEI executables to uninstall the SEI components.

  2. Run the 9_UninstallDB.sql script as many times as needed to remove each user. 

/* Script to uninstall users.
** 
** # Description
** The purpose of this script is to properly uninstall a SEI user.
** 
** # How to use it
** - The script needs to be run from a user with administrative permissions on the server
** - Fill the variables after the comment `Change the values`
**   * SEI_USER: Name of the SEI Cube user
**   * ENABLE_LOGING: Enable the script to print some logs
** - Run the script
*/

set serveroutput on;

DECLARE
    /*************************/
    /* Variables declaration */
    /*************************/
    SEI_USER                   VARCHAR2(1000);
    SEI_TABLE_SPACE            VARCHAR2(1000);
    SEI_TMP_TABLE_SPACE        VARCHAR2(1000);
    ENABLE_LOGING              BOOLEAN;
    -- Other variables
    v_SEI_USER_CHECK           NUMBER;
    TablespaceDOESNTExistsExcep       EXCEPTION;
    PRAGMA EXCEPTION_INIT(TablespaceDOESNTExistsExcep,-959);
BEGIN
    /*********************/
    /* Change the values */
    /*********************/
    -- User information
    SEI_USER                    := 'SEI';
    -- TableSpace information
    SEI_TABLE_SPACE             := 'SEI';
    SEI_TMP_TABLE_SPACE         := 'SEI_TMP';
    -- Other
    ENABLE_LOGING               := true;
    
    -- Enabling logs
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;

    /***********************************************************/
    /*                        Variables                        */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  SEI Objects Username =>  ' || SEI_USER);
    dbms_output.put_line('  /* Table space informations */');
    dbms_output.put_line('    SEI TableSpace');
    dbms_output.put_line('      Name =>  ' || SEI_TABLE_SPACE);
    dbms_output.put_line('    SEI TMP TableSpace');
    dbms_output.put_line('      Name =>  ' || SEI_TMP_TABLE_SPACE);
    dbms_output.new_line;
    
    /***********************************************************/
    /*                   TableSpace / Users                    */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* TableSpace / Users */');
    dbms_output.put_line('/**********************/');
    -- Check if the user SEI Objects exists
    SELECT COUNT(1) INTO v_SEI_USER_CHECK FROM DBA_USERS WHERE USERNAME = SEI_USER;
    
    -- User
    IF ( v_SEI_USER_CHECK = 1 ) THEN
        -- Killing sessions
        FOR r IN (select sid,serial# from v$session where username = SEI_USER)
        LOOP
            EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.sid  || ',' 
                || r.serial# || ''' IMMEDIATE';
            dbms_output.put_line('  Killing session with serial "' || r.serial# || ' for user ' || SEI_USER);
        END LOOP;
        -- Can be usefull to manually kill the session
        --SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session where username=SEI_USER;
        EXECUTE IMMEDIATE 'DROP USER ' || SEI_USER || ' CASCADE';
        dbms_output.put_line('  UserName "' || SEI_USER || ' dropped');
    ELSE
        dbms_output.put_line('  The UserName "' || SEI_USER || ' doesn''t exists');
    END IF;
    
    -- Main TableSpace
    IF SEI_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLESPACE '
                                || SEI_TABLE_SPACE
                                || ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
                dbms_output.put_line('  TableSpace "' || SEI_TABLE_SPACE || '" dropped');
            EXCEPTION
                WHEN TablespaceDOESNTExistsExcep THEN
                    dbms_output.put_line('  The TableSpace "' || SEI_TABLE_SPACE || '" doesn''t exists');
                    NULL;
            END;
        ELSE
            dbms_output.put_line('  The SEI_TABLE_SPACE variable was not filled so tablespace will not be dropped');
    END IF;
    
    -- Temp TableSpace
    IF SEI_TMP_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLESPACE '
                                || SEI_TMP_TABLE_SPACE
                                || ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
                dbms_output.put_line('  TableSpace "' || SEI_TMP_TABLE_SPACE || '" dropped');
            EXCEPTION
                WHEN TablespaceDOESNTExistsExcep THEN
                    dbms_output.put_line('  The TableSpace "' || SEI_TMP_TABLE_SPACE || '" doesn''t exists');
                    NULL;
            END;
        ELSE
            dbms_output.put_line('  The SEI_TABLE_SPACE variable was not filled so tablespace will not be dropped');
    END IF;
END;

Additional Oracle Information

Please see the sections below for additional information about Oracle:

  • SID: Oracle System ID. It uniquely identifies a database (for example: ORCL).

  • User: In Oracle, users and schemas are essentially the same thing. A user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

  • Tablespace: An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data. Each tablespace consists of one or more files called datafiles, which are physical structures that conform to the operating system on which Oracle is running.

    Note  

    SEI supports Oracle tablespace functionalities such as DEFAULT ROW STORE COMPRESS ADVANCED. However, many Oracle tablespace functionalities (including the one previously mentioned) are additional features that must be purchased separately from Oracle. Please contact your Oracle DBA if you would like to learn more about purchasing licenses for additional tablespace functionalities.

  • Temp Tablespace: A temporary tablespace, as its name implies, stores the temporary data that only exists during the database session. Oracle uses temporary tablespaces to improve the concurrency of multiple sort operations which do not fit in memory. The TEMP tablespace can be shared by multiple users.

  • Datafile: Datafiles are physical files of the operating system that store the data of all logical structures in the database. They must be explicitly created for each tablespace. Oracle assigns each datafile two file numbers, an absolute file number and a relative file number, that are used to uniquely identify it. Multiple datafiles can be added to the same tablespace (regular or temp).

  • Block Size: Size of the block for the data. The size of the block can be different by tablespace but it is usually the same. The size of the block will have a direct impact on the maximum size of the datafile.

  • Block size: The default block size is often 8 Kb so the datafile that can have a maximum of 32 GB.

    To get the current block size of the database, run the following SQL command:

    select value from v$parameter where name = 'db_block_size';

    Block size Maximum datafile size Maximum database size
    2 KB 8 GB 512 TB
    4 KB 16 GB 1 PB
    8 KB 32 GB 2 PB
    16 KB 64 GB 4 PB
    32 KB 128 GB 8 PB

  • Tablespace and datafile: By default, the tablespace contains only one data file. Based on the block size, the default size of a tablespace will only be 32 GB. You need to add more datafiles to a tablespace to store more data (regular or temp tablespace). The maximum number of datafiles that can be added is 65 536 files by database, hence the maximum database size mentioned above (for example: 32 GB x 65 536 = 2 PB). The maximum number of datafiles in a tablespace is 1022 (32 TB for an 8 Kb block size).

    To add more datafiles to a tablespace, run the following SQL command (adjust this command with your own name and desired sizes):

    ALTER TABLESPACE YOUR_TABLE_SPACE ADD DATAFILE YOURNAME.DBF' size 1024m autoextend on maxsize 32767m;

    To get the list of datafiles attached to a tablespace, run the following SQL command:

    SELECT TABLESPACE_NAME,FILE_NAME From DBA_DATA_FILES;

  • Temp tablespace: The temp tablespace can grow quite big once OLAP Manager has loaded the data (full load). However, the used space remains stable for subsequent loads because it will not be allocated anymore.

    Oracle uses temporary tablespaces to improve the concurrency of multiple sort operations that do not fit in memory.

    To see the space allocation of a temporary tablespace, run the following command:

    SELECT * FROM dba_temp_free_space;

    To add more datafiles to an existing tablespace, run the following SQL (adjust this command with your own name and desired sizes):

    ALTER TABLESPACE YOUR_TABLE_SPACE ADD TEMPFILE 'YOURNAME.DBF' size 1024m autoextend on maxsize 32767m ;

  • Logging: In Oracle logging is activated by default on tablespace creation. In the case of the SEI data warehouse, logging is not required because we simply reload the data warehouse instead of using the undo/redo feature. Since logging has a direct impact on the temp tablespace size, it is recommended to deactivate it whenever possible.

    To create a new tablespace without logging, run this command:

    create tablespace your_table_space datafile 'your_name.DBF' size 1024m autoextend on maxsize 32767m NOLOGGING;

    To remove logging for an existing tablespace:

    ALTER TABLESPACE SEICUBE3 NOLOGGING;

  • Compression: There are multiple ways of compressing data in Oracle, but the one we are describing here for SEI is Advanced Row Compression. Advanced Row Compression has many benefits. One significant advantage is Oracle’s ability to read compressed blocks (data and indexes) directly in memory, without uncompressing the blocks. This improves performance thanks to the reduction in I/O, and the reduction in system calls related to the I/O operations. Furthermore, the buffer cache becomes more efficient by storing data without having to add memory.

    To create a new tablespace with compression and without logging, run this command:

    create tablespace your_table_space datafile 'your_name.DBF' size 1024m autoextend on maxsize 32767m DEFAULT ROW STORE COMPRESS ADVANCED NOLOGGING;

    The table below describes a logging and compression test on a cube where the source database had more than 190 million rows. The generated master slice generated resulted in 111 million rows and 3 other slices were over 94 million rows. All scenarios clearly show that logging must be deactivated. It is strongly recommended to use compression since the time to load was not much different and the storage was greatly reduced.

    Block size Compression Logging Loading time Temp size Data size Total
    8 Kb Yes Yes 1h28 24.7 68.8 93.5
    8 Kb No Yes 1h16 83.2 106 189.2
    8 Kb Yes No 1h28 25.2 70.2 95.4
    8 Kb No No 1h12 28 108 136
    16 Kb No No 1h12 25.2 106 131.2

  • User and access: Users are created with no access to SEI by default. You need to create a specific SEI user in the datasource configuration to avoid tablespace limitations.

    To create a user and attach the created tablespace, run this command:

    CREATE USER YOUR_USER IDENTIFIED BY YOUR_PASSWORD DEFAULT TABLESPACE YOUR_TABLESPACE

    TEMPORARY TABLESPACE YOUR_TEMP_TABLESPACE_TMP QUOTA UNLIMITED ON YOUR_TABLESPACE;

    After creating the SEI user, you need to grant the following minimum access to that user:

    GRANT create procedure, create session, create table, connect,

    create type, create view, create synonym, create trigger, resource TO YOUR_USER;

    Note  

    For any Oracle datasource the SEI user must be put as custom schema in the data source definition. This will allow the tracking table or custom objects to be created in this specific user instead of the source. Triggers are the only exception to this rule.

    You also need to grant the SEI user access to all Oracle users (databases) that you will want to read as a source. The level of access will depend on what you want to do (read only, create OLAP Cube, write back, etc.).

    To give the SEI user access to a source, run this command (change SOURCE_USER and YOUR_USER):

    begin

    for x in ( SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = SOURCE_USER' )

    loop

    BEGIN

    execute immediate 'GRANT SELECT ON SOURCE_USER.”' || x.TABLE_NAME || '” TO YOUR_USER';

    END;

    end loop;

    end;

     

    begin

    for x in ( select VIEW_NAME from DBA_VIEWS WHERE OWNER = ' SOURCE_USER ' )

    loop

    BEGIN

    execute immediate 'grant select on SOURCE_USER. ”' || x.VIEW_NAME || '” to YOUR_USER ';

    END;

    end loop;

    end;

  • Triggers: Triggers must be created directly in the configuration of SEI users in OLAP Manager with the proper security level. This is required to avoid the case where the ERP will insert rows in a table with a trigger that will not have the proper access to the tracking tables.

    To add the proper authorization level to the user:

    grant create any trigger to YOUR_USER;

    The data source is configured with the SEI schema and the SEI user. You will put SEED in the source schema and SEICube in the SEI schema.

    When you build the OLAP Cube, the tracking table and the trigger will be created in the SEI user, and no table will be created in the source user. The trigger will thus have automatically access to the tracking table.

    When a record is inserted in the source user (ex : SEED.GACCENTRYD) from a source user (ex: SEED), the data will be inserted and the trigger will be executed with the authorization of SEI user since it resides in the source user.