Creating an Oracle User and Tablespace

For Oracle users, as of 2021 R1.1 and build 119 of Sage X3, the Sage X3 template will no longer automatically create an Oracle User and Tablespace for SEI use. This is to give SEI users the freedom to configure an Oracle User and Tablespace to their own preferences and to avoid connecting to databases as their system user.

Note

SEI supports Oracle Tablespace functionalities such as DEFAULT ROW STORE COMPRESS ADVANCED. However, please note that 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.

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

Example Code

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;