Creating an Oracle User and Tablespace

For Oracle users, as of 2021 R1.1 and build 119 of Sage X3, the Sage X3 template no longer creates an Oracle user and tablespace for SEI use automatically. This allows SEI users to configure an Oracle user and tablespace to their own preferences and to avoid connecting to databases as their system user.

  • 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.

The example code below contains the minimum rights required for the ERP template to function properly. All you have to do is change the values of three variables to match your information (/* Change the values */ section).

Copy 
DECLARE
v_USERCHECK NUMBER; -- Checks if User already exists
v_X3_SCHEMA varchar2(1000); -- Your X3 Folder Schema
v_CUSTOM_SCHEMA varchar2(1000); -- Your Custom Schema for Nectari Objects
v_PASSWORD varchar2(1000); -- The password associated with the above user
v_TABLESPACE_PATH varchar2(1000); -- The path to store your tablespace if you have set a default location in Oracle
BEGIN
    /* Change the values */
    v_X3_SCHEMA := 'SEED';
    v_CUSTOM_SCHEMA := 'SEI_SEED';
    v_PASSWORD := 'your_password';
    
    SELECT COUNT(1) INTO v_USERCHECK FROM DBA_USERS WHERE USERNAME = v_CUSTOM_SCHEMA;
    SELECT VALUE INTO v_TABLESPACE_PATH FROM v$parameter WHERE NAME ='db_create_file_dest' ;  
 
IF (v_USERCHECK = 0) THEN
    IF (v_TABLESPACE_PATH IS NULL) THEN
        EXECUTE IMMEDIATE 'CREATE TABLESPACE '|| v_CUSTOM_SCHEMA ||' DATAFILE '''|| v_CUSTOM_SCHEMA ||'.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M NOLOGGING'; 
        EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLESPACE '|| v_CUSTOM_SCHEMA ||'_TMP TEMPFILE '''|| v_CUSTOM_SCHEMA ||'_TMP.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M';
    ELSE
        EXECUTE IMMEDIATE 'CREATE TABLESPACE '|| v_CUSTOM_SCHEMA ||' DATAFILE '''|| v_TABLESPACE_PATH ||''' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M NOLOGGING'; 
        EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLESPACE '|| v_CUSTOM_SCHEMA ||'_TMP TEMPFILE '''|| v_TABLESPACE_PATH ||''' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M';
    END IF;
     
    EXECUTE IMMEDIATE 'CREATE USER '|| v_CUSTOM_SCHEMA ||' IDENTIFIED BY '|| v_PASSWORD ||'  DEFAULT TABLESPACE '|| v_CUSTOM_SCHEMA ||' TEMPORARY TABLESPACE '|| v_CUSTOM_SCHEMA ||'_TMP QUOTA UNLIMITED ON '|| v_CUSTOM_SCHEMA;
        
    -- Grant General Authorization
        EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CONNECT, CREATE TYPE, CREATE VIEW, CREATE SYNONYM, CREATE TRIGGER, RESOURCE TO '|| v_CUSTOM_SCHEMA;
        
    -- Required if we need to create triggers base on a table of a different user (Datasource in Olap with trigger tracking)
    EXECUTE IMMEDIATE 'GRANT CREATE ANY TRIGGER TO '|| v_CUSTOM_SCHEMA;
     
    -- Grant required authorization to other users as sources (or destination for write back)
        FOR X IN (SELECT TABLE_NAME FROM DBA_ALL_TABLES WHERE OWNER = v_X3_SCHEMA) 
        LOOP 
    EXECUTE IMMEDIATE 'GRANT INSERT,UPDATE,DELETE,SELECT,ALTER ON '|| v_X3_SCHEMA ||'.' || X.TABLE_NAME || ' TO '|| v_CUSTOM_SCHEMA;
        END LOOP; 
     
    -- Grant required authorization to read views in other users
        FOR X IN (SELECT VIEW_NAME FROM DBA_VIEWS WHERE OWNER = v_X3_SCHEMA AND VIEW_NAME <> 'LEGGTEYGRP')
        LOOP 
    EXECUTE IMMEDIATE 'GRANT SELECT ON '|| v_X3_SCHEMA ||'.' || X.VIEW_NAME || ' TO '|| v_CUSTOM_SCHEMA;
        END LOOP; 
END IF;
END;