Create Oracle Users

To prepare for your Nectari installation, create dedicated Oracle users and tablespaces by running the provided SQL scripts. These scripts must be executed before you install Nectari (required as of Version 2022 Release 1.3). Running them ensures each user has the right tablespace and permissions, resulting in a secure and well-organized Oracle environment tailored for Nectari.

Be sure to customize user names, passwords, and tablespace names according to your organization’s standards and requirements. Understanding core Oracle concepts—such as storage structure, logging, compression, and security—helps you set up a reliable and efficient foundation for your deployment.

note

These scripts must be run with a user who has administrative permissions on the Oracle database.

Revoke existing users permissions

If an Oracle user is already present in your Nectari environment, you must revoke this user's permissions before continuing with new setup. Run the 0_RevokePermissions.sql script and set the NECTARI_USER parameter to the intended Nectari username (for example, NECTARI). This script should be executed by an administrator on the Oracle database.

Show the 0_RevokePermissions.sql example script

/*
  Script to revoke permissions on an existing Oracle user for Nectari/SEI.
  How to use:
    - Run as a user with administrative privileges on the Oracle database.
    - Update the variables under the 'Change the values' section.
        * NECTARI_USER: Name of the Nectari user to revoke.
        * ENABLE_LOGING: Set to 'true' to print logs.
    - Execute the script.
  Note:
    - The script prints logs summarizing actions taken.
    - Ensure you have the necessary backups and approvals before revoking permissions.
*/

set serveroutput on;

DECLARE
    -- Variable declarations
    NECTARI_USER         VARCHAR2(1000);
    ENABLE_LOGING        BOOLEAN;
    v_NECTARI_USER_CHECK NUMBER; -- Other variable

BEGIN
    -- Change the values for your environment
    NECTARI_USER   := 'NECTARI_SEED'; -- User information
    ENABLE_LOGING  := true; -- Other
        
    -- Enable or disable logs
    IF (ENABLE_LOGING = true) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;
    
    -- Output variable summary
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  NECTARI Username =>  ' || NECTARI_USER);
    dbms_output.new_line;
    
    -- TableSpace / Users
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/*    Permissions     */');
    dbms_output.put_line('/**********************/');
    -- Check if the user exists
    SELECT COUNT(1) INTO v_NECTARI_USER_CHECK FROM DBA_USERS WHERE USERNAME = NECTARI_USER;
    
    IF ( v_NECTARI_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 = NECTARI_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 = NECTARI_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 = NECTARI_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 "' || NECTARI_USER || ' doesn''t exists');
    END IF;
END;

Create users

Create Nectari user and schema

Run the 1_ConfigDB.sql script. Configure the script parameters to match your environment:

Set the NECTARI_USER parameter to NECTARI.
Set the NECTARI_TABLE_SPACE parameter to NECTARI, or another name if preferred.
Set the NECTARI_TMP_TABLE_SPACE parameter to NECTARI_TMP, or another name if preferred.
Adjust other parameters to fit your environment and security policies.

The script creates a dedicated Oracle user, assigns required tablespaces, and applies correct permissions, ensuring that Nectari operates securely within its own schema.

Show the 1_ConfigDB.sql example script

/*
  Script to create a specific Oracle user and schema for Nectari/SEI.
  Description:
    This script creates (or updates) a user for the Nectari application, assigns main and temporary tablespaces, grants required permissions, and (optionally) revokes previously-granted permissions for the user.
    Run this script with administrative privileges on your Oracle database.
  Parameter guide:
    - NECTARI_USER: Oracle username for the Nectari/SEI application.
    - NECTARI_USER_PWD: Password for NECTARI_USER.
    - NECTARI_TABLE_SPACE: Name of the main tablespace.
    - NECTARI_TABLE_SPACE_LOG: Enable logging for the tablespace.
    - NECTARI_TABLE_SPACE_COMP: Enable compression for the tablespace.
    - NECTARI_TMP_TABLE_SPACE: Name of the temporary tablespace.
    - ENABLE_LOGING: Set to true to print logs.
    - REVOKE_EXIST_PERM: Set to true to revoke all previously granted permissions if NECTARI_USER exists.
  Notes:
    - Change parameter values under the 'Change the values' section.
    - The script removes all existing permissions from NECTARI_USER if REVOKE_EXIST_PERM is set to true.
    - Always adjust user, tablespace, and password fields according to organizational practice.
*/

set serveroutput on;

DECLARE
  -- Variable declarations
  NECTARI_USER              VARCHAR2(1000);
  NECTARI_USER_PWD          VARCHAR2(1000);
  NECTARI_TABLE_SPACE       VARCHAR2(1000);
  NECTARI_TABLE_SPACE_LOG   BOOLEAN;
  NECTARI_TABLE_SPACE_COMP  BOOLEAN;
  NECTARI_TMP_TABLE_SPACE   VARCHAR2(1000);
  ENABLE_LOGING             BOOLEAN;
  REVOKE_EXIST_PERM         BOOLEAN;
  v_CUSTOM_TABLE_SPACE_PATH VARCHAR2(1000);
  v_NECTARI_USER_CHECK      NUMBER;
  v_NECTARI_TABLE_SPACE_LOG VARCHAR2(1000);
  v_NECTARI_TABLE_SPACE_COMP VARCHAR2(1000);
  TablespaceExistsExcep     EXCEPTION;
  FileExistsExcep           EXCEPTION;
  PRAGMA EXCEPTION_INIT(TablespaceExistsExcep, -1543);
  PRAGMA EXCEPTION_INIT(FileExistsExcep, -1119);

BEGIN
    -- Change the values as needed for your environment
    -- User information
    NECTARI_USER            := 'NECTARI';
    NECTARI_USER_PWD        := 'NECTARI_PWD';
    -- TableSpace information
    NECTARI_TABLE_SPACE     := 'NECTARI';
    NECTARI_TABLE_SPACE_LOG := true;
    NECTARI_TABLE_SPACE_COMP:= false;
    NECTARI_TMP_TABLE_SPACE := 'NECTARI_TMP';
    -- Other
    ENABLE_LOGING           := true;
    REVOKE_EXIST_PERM       := false;
    
    -- Fetch custom datafile path if defined
    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;
        
    -- Enable/disable logs
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;
    
    -- Prepare tablespace settings
    IF ( NECTARI_TABLE_SPACE_LOG = true ) THEN
        v_NECTARI_TABLE_SPACE_LOG := '';
    ELSE
        v_NECTARI_TABLE_SPACE_LOG := ' NOLOGGING ';
    END IF;
    
    IF ( NECTARI_TABLE_SPACE_COMP = true ) THEN
        v_NECTARI_TABLE_SPACE_COMP := ' DEFAULT COMPRESS ';
    ELSE
        v_NECTARI_TABLE_SPACE_COMP := '';
    END IF;
    
    -- Output variables summary
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  NECTARI Username =>  ' || NECTARI_USER);
    dbms_output.put_line('  NECTARI Password => ' || NECTARI_USER_PWD);
    dbms_output.put_line('  /* Table space informations */');
    dbms_output.put_line('    NECTARI TableSpace');
    dbms_output.put_line('      Name =>  ' || NECTARI_TABLE_SPACE);
    dbms_output.put_line('      Loging =>  ' || sys.diutil.bool_to_int(NECTARI_TABLE_SPACE_LOG));
    dbms_output.put_line('      Compression =>  ' || sys.diutil.bool_to_int(NECTARI_TABLE_SPACE_COMP));
    dbms_output.put_line('    NECTARI TMP TableSpace');
    dbms_output.put_line('      Name =>  ' || NECTARI_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;

    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* TableSpace / Users */');
    dbms_output.put_line('/**********************/');
    -- Tablespace creation (main/temporary)
    SELECT COUNT(1) INTO v_NECTARI_USER_CHECK FROM DBA_USERS WHERE USERNAME = NECTARI_USER;
    
    IF NECTARI_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE TABLESPACE '
                || NECTARI_TABLE_SPACE
                || ' DATAFILE '''
                || v_CUSTOM_TABLE_SPACE_PATH || NECTARI_TABLE_SPACE
                || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M'
                || v_NECTARI_TABLE_SPACE_COMP
                || v_NECTARI_TABLE_SPACE_LOG;
            dbms_output.put_line('  TableSpace "' || NECTARI_TABLE_SPACE || '" created');
        EXCEPTION
            WHEN TablespaceExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || NECTARI_TABLE_SPACE || '" already exists');
                NULL;
            WHEN FileExistsExcep THEN
                dbms_output.put_line('  The file associated to the TableSpace "' || NECTARI_TABLE_SPACE || '" already exists');
                NULL;
        END;
    ELSE
        dbms_output.put_line('  The NECTARI_TABLE_SPACE variable was not filled so tablespace will not be created');
    END IF;
    
    -- Temp TableSpace
    IF NECTARI_TMP_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLESPACE '
                                  || NECTARI_TMP_TABLE_SPACE
                                  || ' TEMPFILE '''
                                  || v_CUSTOM_TABLE_SPACE_PATH || NECTARI_TMP_TABLE_SPACE
                                  || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M';                                
            dbms_output.put_line('  TableSpace "' || NECTARI_TMP_TABLE_SPACE || '" created');
        EXCEPTION
            WHEN TablespaceExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || NECTARI_TMP_TABLE_SPACE || '" already exists');
            NULL;
            WHEN FileExistsExcep THEN
                dbms_output.put_line('  The file associated to the TableSpace "' || NECTARI_TMP_TABLE_SPACE || '" already exists');
            NULL;
        END;
    ELSE
        dbms_output.put_line('  The NECTARI_TMP_TABLE_SPACE variable was not filled so tablespace will not be created');
    END IF;
    
    -- Create or update user
    IF ( v_NECTARI_USER_CHECK = 0 ) THEN
        EXECUTE IMMEDIATE 'CREATE USER '
            || NECTARI_USER
            || ' IDENTIFIED BY '
            || NECTARI_USER_PWD
            || '  DEFAULT TABLESPACE '
            || NECTARI_TABLE_SPACE
            || ' TEMPORARY TABLESPACE '
            || NECTARI_TMP_TABLE_SPACE
            || ' QUOTA UNLIMITED ON '
            || NECTARI_TABLE_SPACE;
        dbms_output.put_line('  UserName "' || NECTARI_USER || ' created');
    ELSE
        dbms_output.put_line('  The UserName "' || NECTARI_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 = NECTARI_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 = NECTARI_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 = NECTARI_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 "' || NECTARI_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 NECTARI/SEI
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CONNECT, CREATE TYPE, CREATE VIEW, CREATE SYNONYM, CREATE TRIGGER, RESOURCE TO '
    || NECTARI_USER;
    dbms_output.put_line('  Username '|| NECTARI_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;

Create BILicense user and schema

Run the 1_ConfigDB.sql script. Configure the script parameters to match your environment:

Set the NECTARI_USER parameter to BILicense.
Set the NECTARI_TABLE_SPACE parameter to BILicense, or another name if preferred.
Set the NECTARI_TMP_TABLE_SPACE parameter to BILicense_TMP, or another name if preferred.
Adjust other parameters to fit your environment and security policies.

Create NectariCube user

The NectariCube user is granted access to all cube data, including read access to schemas (users) you specify, with the option to exclude specific tables or views. To set up this user, run the 2_DataDB.sql script and configure the required parameters as described in the script’s instructions. Use the SCHEMA_LIST parameter to specify which schemas the user can access, and the EXCLUSION_TB_LIST and EXCLUSION_VW_LIST parameters to exclude any tables or views as needed.

Running this script creates a dedicated Oracle user, configures tablespace assignments, and ensures the user has the correct data permissions for working with Nectari cube data.

Show the 2_DataDB.sql example script

/*
  Script to create a dedicated Oracle user for NECTARI/SEI Cube.
  Description:
    - Creates a specific Oracle user for the NECTARI/SEI application, with an associated tablespace (or uses an existing one).
    - Grants required permissions on other schemas (users) as needed.
  Permissions granted:
    - The NECTARI_CUBE user receives full rights on their own schema.
    - Read (SELECT) access to all tables and views from schemas listed in the SCHEMA_LIST variable.
    - The ability to create triggers across the database.
    - Optionally, you may exclude specific tables or views from the granted access using the EXCLUSION_TB_LIST and EXCLUSION_VW_LIST variables.
    - If the user already exists, this script will first remove all their current permissions (when configured).
  How to use:
    - Run from a user with administrative privileges on the Oracle server.
    - Update the variables under the 'Change the values' section:
        * NECTARI_CUSTOM_USER: Name for the NECTARI/SEI Cube user.
        * NECTARI_CUSTOM_USER_PWD: Password for the user.
        * NECTARI_CUSTOM_TABLE_SPACE: Name of the main tablespace.
        * NECTARI_CUSTOM_TABLE_SPACE_LOG: Enable logging for the tablespace.
        * NECTARI_CUSTOM_TABLE_SPACE_COMP: Enable compression for the tablespace (requires server-wide compression).
        * NECTARI_CUSTOM_TMP_TABLE_SPACE: Name of the temporary tablespace.
        * ENABLE_LOGING: Set to true to enable script logging.
        * REVOKE_EXIST_PERM: If true, will remove existing permissions for the user if one exists.
    - Specify permission parameters:
        * SCHEMA_LIST: List of schemas whose tables/views the user is granted read access to.
        * EXCLUSION_TB_LIST: List of tables to not grant access (format: "SCHEMA"."TABLE").
        * EXCLUSION_VW_LIST: List of views to not grant access (format: "SCHEMA"."VIEW").
    - Run the script.
  Important notes:
    - In Oracle, access grants for other schemas must be done table by table.
      → If new tables are later added to a schema, you must re-run the script to update grants.
    - CREATE ANY TRIGGER and similar permissions require server-wide privileges for cross-schema use.
*/

set serveroutput on;

DECLARE
    -- Variable declarations
    NECTARI_CUSTOM_USER                VARCHAR2(1000);
    NECTARI_CUSTOM_USER_PWD            VARCHAR2(1000);
    NECTARI_CUSTOM_TABLE_SPACE         VARCHAR2(1000);
    NECTARI_CUSTOM_TABLE_SPACE_LOG     BOOLEAN;
    NECTARI_CUSTOM_TABLE_SPACE_COMP    BOOLEAN;
    NECTARI_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_NECTARI_CUSTOM_USER_CHECK        NUMBER;
    v_NECTARI_CUSTOM_TABLE_SPACE_LOG   VARCHAR2(1000);
    v_NECTARI_CUSTOM_TABLE_SPACE_COMP  VARCHAR2(1000);
    v_NECTARI_CUSTOM_TMP_TS_COMP       VARCHAR2(1000);
    TablespaceExistsExcep              EXCEPTION;
    FileExistsExcep                    EXCEPTION;
    PRAGMA EXCEPTION_INIT(TablespaceExistsExcep, -1543);
    PRAGMA EXCEPTION_INIT(FileExistsExcep, -1119);

BEGIN
    -- Change the values as needed for your environment
    -- User credentials
    NECTARI_CUSTOM_USER          := 'NECTARI_SEED';
    NECTARI_CUSTOM_USER_PWD      := 'NECTARI_SEED_PWD';
    -- Tablespace configuration
    NECTARI_CUSTOM_TABLE_SPACE       := 'NECTARI_SEED';
    NECTARI_CUSTOM_TABLE_SPACE_LOG   := true;
    NECTARI_CUSTOM_TABLE_SPACE_COMP  := false;
    NECTARI_CUSTOM_TMP_TABLE_SPACE   := 'NECTARI_SEED_TMP';
    -- Schemas to grant SELECT access (add/adjust as needed)
    SCHEMA_LIST(1) := 'X3FOLDER';
    -- Exclusions (uncomment and customize if needed)
    -- EXCLUSION_TB_LIST(1) := '"X3FOLDER"."TABLE"';  -- Exclude this table from SELECT grants
    -- EXCLUSION_VW_LIST(1) := '"X3FOLDER"."VIEW"';   -- Exclude this view from SELECT grants
    -- Script options
    ENABLE_LOGING       := true;   -- Set to true to show log output
    REVOKE_EXIST_PERM   := false;  -- Set to true to revoke all existing permissions from the user
    
    -- Determine custom datafile path (if set in database parameters)
    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;
        
    -- Enable or disable script logs for step-by-step output
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;
    
    -- Prepare tablespace settings for logging and compression options
    IF ( NECTARI_CUSTOM_TABLE_SPACE_LOG = true ) THEN
        v_NECTARI_CUSTOM_TABLE_SPACE_LOG := '';
    ELSE
        v_NECTARI_CUSTOM_TABLE_SPACE_LOG := ' NOLOGGING ';
    END IF;
    
    IF ( NECTARI_CUSTOM_TABLE_SPACE_COMP = true ) THEN
        v_NECTARI_CUSTOM_TABLE_SPACE_COMP := ' DEFAULT COMPRESS ';
    ELSE
        v_NECTARI_CUSTOM_TABLE_SPACE_COMP := '';
    END IF;
    
    -- Output variables summary
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  NECTARI Objects Username =>  ' || NECTARI_CUSTOM_USER);
    dbms_output.put_line('  NECTARI Objects Password => ' || NECTARI_CUSTOM_USER_PWD);
    dbms_output.put_line('  /* Table space informations */');
    dbms_output.put_line('    NECTARI Objects TableSpace');
    dbms_output.put_line('      Name =>  ' || NECTARI_CUSTOM_TABLE_SPACE);
    dbms_output.put_line('      Loging =>  ' || sys.diutil.bool_to_int(NECTARI_CUSTOM_TABLE_SPACE_LOG));
    dbms_output.put_line('      Compression =>  ' || sys.diutil.bool_to_int(NECTARI_CUSTOM_TABLE_SPACE_COMP));
    dbms_output.put_line('    NECTARI Objects TMP TableSpace');
    dbms_output.put_line('      Name =>  ' || NECTARI_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;
    
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* TableSpace / Users */');
    dbms_output.put_line('/**********************/');
    -- Check if the NECTARI/SEI Cube user exists
    SELECT COUNT(1) INTO v_NECTARI_CUSTOM_USER_CHECK FROM DBA_USERS WHERE USERNAME = NECTARI_CUSTOM_USER;
    
    IF NECTARI_CUSTOM_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE TABLESPACE '
                || NECTARI_CUSTOM_TABLE_SPACE
                || ' DATAFILE '''
                || v_CUSTOM_TABLE_SPACE_PATH || NECTARI_CUSTOM_TABLE_SPACE
                || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M'
                || v_NECTARI_CUSTOM_TABLE_SPACE_COMP
                || v_NECTARI_CUSTOM_TABLE_SPACE_LOG;
            dbms_output.put_line('  TableSpace "' || NECTARI_CUSTOM_TABLE_SPACE || '" created');
        EXCEPTION
            WHEN TablespaceExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || NECTARI_CUSTOM_TABLE_SPACE || '" already exists');
            NULL;
            WHEN FileExistsExcep THEN
                dbms_output.put_line('  The file associated to the TableSpace "' || NECTARI_CUSTOM_TABLE_SPACE || '" already exists');
            NULL;
        END;
    ELSE
        dbms_output.put_line('  The NECTARI_CUSTOM_TABLE_SPACE variable was not filled so tablespace will not be created');
    END IF;
    
    -- Create temporary tablespace if not null
    IF NECTARI_CUSTOM_TMP_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLESPACE '
                || NECTARI_CUSTOM_TMP_TABLE_SPACE
                || ' TEMPFILE '''
                || v_CUSTOM_TABLE_SPACE_PATH || NECTARI_CUSTOM_TMP_TABLE_SPACE
                || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M';
            dbms_output.put_line('  TableSpace "' || NECTARI_CUSTOM_TMP_TABLE_SPACE || '" created');
        EXCEPTION
            WHEN TablespaceExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || NECTARI_CUSTOM_TMP_TABLE_SPACE || '" already exists');
            NULL;
            WHEN FileExistsExcep THEN
                dbms_output.put_line('  The file associated to the TableSpace "' || NECTARI_CUSTOM_TMP_TABLE_SPACE || '" already exists');
            NULL;
        END;
    ELSE
        dbms_output.put_line('  The NECTARI_CUSTOM_TMP_TABLE_SPACE variable was not filled so tablespace will not be created');
    END IF;
    
    -- Create user if not already existing; otherwise, process existing user as per settings
    IF ( v_NECTARI_CUSTOM_USER_CHECK = 0 ) THEN
        EXECUTE IMMEDIATE 'CREATE USER '
            || NECTARI_CUSTOM_USER
            || ' IDENTIFIED BY '
            || NECTARI_CUSTOM_USER_PWD
            || '  DEFAULT TABLESPACE '
            || NECTARI_CUSTOM_TABLE_SPACE
            || ' TEMPORARY TABLESPACE '
            || NECTARI_CUSTOM_TMP_TABLE_SPACE
            || ' QUOTA UNLIMITED ON '
            || NECTARI_CUSTOM_TABLE_SPACE;
        dbms_output.put_line('  UserName "' || NECTARI_CUSTOM_USER || ' created');
    ELSE
        dbms_output.put_line('  The UserName "' || NECTARI_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 = NECTARI_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 = NECTARI_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 = NECTARI_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 "' || NECTARI_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 privileges to the NECTARI/SEI Cube user
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CONNECT, CREATE TYPE, CREATE VIEW, CREATE SYNONYM, CREATE TRIGGER, RESOURCE TO '
    || NECTARI_CUSTOM_USER;
    dbms_output.put_line('  Username '|| NECTARI_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 CREATE ANY TRIGGER (applies server-wide—use with caution)
    EXECUTE IMMEDIATE 'GRANT CREATE ANY TRIGGER TO ' || NECTARI_CUSTOM_USER;
    dbms_output.put_line('  Username '|| NECTARI_CUSTOM_USER || ' has been granted the CREATE ANY TRIGGER permissions');
    dbms_output.put_line('    !!! The CREATE ANY TRIGGER applies server wide !!!');
    
    -- Grant SELECT on tables and views from each schema in SCHEMA_LIST
    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 '
                || NECTARI_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 '
                || NECTARI_CUSTOM_USER;
            dbms_output.put_line('  Granting SELECT permissions on '
                || SCHEMA_LIST(i)
                || '.'
                || x.view_name);
        END LOOP;
    END LOOP;
    
    -- Revoke SELECT for excluded tables
    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 '
                || NECTARI_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 '
                || NECTARI_CUSTOM_USER;
            dbms_output.put_line('    Revoking select access for view ' || EXCLUSION_VW_LIST(i));
        END LOOP;
    END IF;
END;

Install Nectari package

After creating the required Oracle users, run the Nectari executables to set up the necessary Oracle tables for your deployment. This step completes the initial database structure and prepares your environment for use with Nectari.

Additional database management

Add space to an Oracle tablespace

When you need to add storage to an existing tablespace, use the 3_AddNewDataFile.sql script. Configure the parameters in the script to match your environment:

Set TABLE_SPACE to the target tablespace.
Set DATA_FILE_NAME to your chosen new datafile name.
Set ENABLE_LOGING if you want detailed output.

By default, this example will add 32 GB of additional capacity, but you can adjust the size and maxsize within the script to meet your needs.

Show the 3_AddNewDataFile example script

/*
  Script to add a new datafile to an existing tablespace.
  Description:
    Adds a new datafile to an Oracle tablespace to increase available storage and prevent space issues.
  Usage:
    - Run as a user with administrative privileges on the Oracle server.
    - Update the variables under the 'Change the values' section:
        * TABLE_SPACE: Name of the tablespace to update.
        * DATA_FILE_NAME: Name for the new datafile.
        * ENABLE_LOGING: Set to true to enable script logging.
    - Execute the script.
  Notes:
    - You can edit the datafile size, maxsize, and related attributes in the EXECUTE IMMEDIATE statement to fit your needs.
*/
set serveroutput on;

DECLARE
    -- Variable declarations
    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 as needed for your environment
    TABLE_SPACE         := 'NECTARI_SEED';
    DATA_FILE_NAME      := 'NECTARI_SEED_NEW2';
    ENABLE_LOGING       := true;
    
    -- Enable or disable logging for this session
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;
    
    -- Retrieve the custom datafile path (if set in DB init params)
    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;
    
    -- Output variables summary
    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;
    
    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;

Remove Oracle users and tablespaces

To uninstall Nectari components and clean up users and tablespaces, use the Nectari executables if available, and run the 9_UninstallDB.sql script as many times as needed to remove each user.

Show the `9_UninstallDB.sql example script

/*
  Script to uninstall a NECTARI/SEI Oracle user and associated tablespaces.
  Description:
    Drops the specified NECTARI/SEI user, including terminating open sessions and removing tablespaces and datafiles.
  Usage:
    - Run as a user with administrative privileges on the Oracle server.
    - Edit the variables below the "Change the values" section:
        * NECTARI_USER: Name of the NECTARI/SEI user to remove.
        * NECTARI_TABLE_SPACE: (Optional) Associated main tablespace.
        * NECTARI_TMP_TABLE_SPACE: (Optional) Associated temporary tablespace.
        * ENABLE_LOGING: Set to true to print logs.
    - Execute the script.
  Notes:
    - Confirm all data is backed up as tablespace and datafiles will be deleted.
    - Script can be reused for additional users/tablespaces by changing variable values.
*/
set serveroutput on;

DECLARE
    -- Variable declarations
    NECTARI_USER            VARCHAR2(1000);
    NECTARI_TABLE_SPACE     VARCHAR2(1000);
    NECTARI_TMP_TABLE_SPACE VARCHAR2(1000);
    ENABLE_LOGING           BOOLEAN;
    -- Other variables
    v_NECTARI_USER_CHECK    NUMBER;
    TablespaceDOESNTExistsExcep EXCEPTION;
    PRAGMA EXCEPTION_INIT(TablespaceDOESNTExistsExcep, -959);
BEGIN
    -- Change the values as needed for your environment
    -- User information
    NECTARI_USER            := 'NECTARI';
    -- TableSpace information
    NECTARI_TABLE_SPACE     := 'NECTARI';
    NECTARI_TMP_TABLE_SPACE := 'NECTARI_TMP';
    -- Other
    ENABLE_LOGING           := true;
    
    -- Enable or disable logs
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;

    -- Output variables summary
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  NECTARI Objects Username =>  ' || NECTARI_USER);
    dbms_output.put_line('  /* Table space informations */');
    dbms_output.put_line('    NECTARI TableSpace');
    dbms_output.put_line('      Name =>  ' || NECTARI_TABLE_SPACE);
    dbms_output.put_line('    NECTARI TMP TableSpace');
    dbms_output.put_line('      Name =>  ' || NECTARI_TMP_TABLE_SPACE);
    dbms_output.new_line;
    
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* TableSpace / Users */');
    dbms_output.put_line('/**********************/');
    -- User check and removal
    SELECT COUNT(1) INTO v_NECTARI_USER_CHECK FROM DBA_USERS WHERE USERNAME = NECTARI_USER;
    
    IF ( v_NECTARI_USER_CHECK = 1 ) THEN
        -- Kill all open sessions for this user
        FOR r IN (select sid,serial# from v$session where username = NECTARI_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 ' || NECTARI_USER);
        END LOOP;
        -- Can be usefull to manually kill the session
        --SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session where username=NECTARI_USER;
        EXECUTE IMMEDIATE 'DROP USER ' || NECTARI_USER || ' CASCADE';
        dbms_output.put_line('  UserName "' || NECTARI_USER || ' dropped');
    ELSE
        dbms_output.put_line('  The UserName "' || NECTARI_USER || ' doesn''t exists');
    END IF;
    
    -- Drop main tablespace, if specified
    IF NECTARI_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLESPACE '
                || NECTARI_TABLE_SPACE
                || ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
            dbms_output.put_line('  TableSpace "' || NECTARI_TABLE_SPACE || '" dropped');
        EXCEPTION
            WHEN TablespaceDOESNTExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || NECTARI_TABLE_SPACE || '" doesn''t exists');
                NULL;
        END;
    ELSE
        dbms_output.put_line('  The NECTARI_TABLE_SPACE variable was not filled so tablespace will not be dropped');
    END IF;
    
    -- Drop temporary tablespace, if specified
    IF NECTARI_TMP_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLESPACE '
                || NECTARI_TMP_TABLE_SPACE
                || ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
            dbms_output.put_line('  TableSpace "' || NECTARI_TMP_TABLE_SPACE || '" dropped');
        EXCEPTION
            WHEN TablespaceDOESNTExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || NECTARI_TMP_TABLE_SPACE || '" doesn''t exists');
                NULL;
        END;
    ELSE
        dbms_output.put_line('  The NECTARI_TABLE_SPACE variable was not filled so tablespace will not be dropped');
    END IF;
END;

Oracle component definitions

Term	Definition
SID (System ID)	Identifies each Oracle database instance uniquely (e.g., `ORCL`).
User/Schema	Defines the Oracle account used for connections, and owns schema objects (tables, views, etc.). In practice, links the account and its schema as the same entity.
Tablespace	Groups logical storage units in the database, using one or more datafiles to store data. Note: Support advanced options (such as `DEFAULT ROW STORE COMPRESS ADVANCED`). Many such features require additional Oracle licensing—consult your DBA before use.
Temporary tablespace	Stores temporary data that exists only for the duration of a session, and supports large sort operations that do not fit in main memory. Shares space among multiple users.
Datafile	Stores the physical data of a tablespace on disk. Assigns each datafile a unique identifier and enables multiple files per tablespace, both for regular and temporary tablespaces.
Block size	Determines the granularity of data storage in each tablespace. Influences the maximum size of datafiles and overall database capacity; is typically consistent for a database.

Storage

Block size

Defines the default data storage granularity, which is often 8 KB and allows each datafile to reach a maximum of 32 GB. To check the current block size of the database, run:

SELECT value FROM v$parameter WHERE name = 'db_block_size';

Block size	Maxmimum detafile size	Maximum datase 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

Start by using only one datafile per tablespace by default, which, with an 8 KB block size, limits the tablespace to 32 GB. Add more datafiles to expand storage for both regular and temporary tablespaces. Remember that you can add up to 65,536 datafiles per database (supporting very large maximum sizes) and up to 1,022 datafiles per tablespace (32 TB at 8 KB block size).
Add a new datafile to a tablespace by running:

ALTER TABLESPACE YOUR_TABLE_SPACE ADD DATAFILE 'YOURNAME.DBF' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M;

Display all datafiles in a tablespace by executing:

SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES;

Temporary tablespaces

Expect the temp tablespace to grow substantially after an initial full OLAP Manager data load, but observe that usage stabilizes on subsequent loads. Leverage temporary tablespaces to enable concurrent sort operations that exceed memory capacity.
Check current space allocation for a temporary tablespace by running:

SELECT * FROM dba_temp_free_space;

Add a new tempfile to an existing tablespace by executing:

ALTER TABLESPACE YOUR_TABLE_SPACE ADD TEMPFILE 'YOURNAME.DBF' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M;

Logging and compression

Logging

Enable logging by default when you create a new tablespace in Oracle. Consider disabling logging for a Nectari data warehouse, since regular data reloads make redo/undo less important and disabling logging can significantly reduce temp tablespace usage.
Create a new tablespace without logging by running:

CREATE TABLESPACE your_table_space DATAFILE 'your_name.DBF' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M NOLOGGING;

Remove logging for an existing tablespace by executing:

ALTER TABLESPACE NectariCUBE3 NOLOGGING;

Compression

Apply Advanced Row Compression in Oracle to optimize Nectari storage. Use this feature to let Oracle read compressed blocks (data and indexes) directly in memory, improving performance by reducing I/O operations and buffer cache usage.
Create a new tablespace with compression and without logging by executing:

CREATE TABLESPACE your_table_space DATAFILE 'your_name.DBF' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M DEFAULT ROW STORE COMPRESS ADVANCED NOLOGGING;

Logging and compression test results

Review the table below to compare logging and compression performance in a test with over 190 million source rows. Observe that all scenarios confirm logging should be deactivated for Nectari data warehouse workloads. Strongly consider enabling compression—loading times remain similar, but storage space is significantly reduced.

Block size	Compression	Logging	Load Time	Temp Size	Data Size	Total (GB)
8 KB	yes	yes	1h28	24.7	68.8	93.5
8 BK	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

Security

User and access

Create users with no default access to Nectari. Add a dedicated Nectari user in the datasource configuration to avoid tablespace restrictions, and attach the correct tablespace when the user is created.
Create a new user and attach the tablespace:

CREATE USER YOUR_USER IDENTIFIED BY YOUR_PASSWORD 
  DEFAULT TABLESPACE YOUR_TABLESPACE
  TEMPORARY TABLESPACE YOUR_TEMP_TABLESPACE_TMP 
  QUOTA UNLIMITED ON YOUR_TABLESPACE;

Grant the minimum required privileges:

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, set the Nectari user as the custom schema in your data source definition. This ensures that tracking tables and custom objects are created in the Nectari user’s schema (not the source schema). Triggers must be managed through the appropriate Nectari security settings.

Grant read access on all tables and views in a source schema:

BEGIN
  FOR x IN (SELECT table_name FROM DBA_TABLES WHERE OWNER = 'SOURCE_USER') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON SOURCE_USER."' || x.table_name || '" TO YOUR_USER';
  END LOOP;
  FOR x IN (SELECT view_name FROM DBA_VIEWS WHERE OWNER = 'SOURCE_USER') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON SOURCE_USER."' || x.view_name || '" TO YOUR_USER';
  END LOOP;
END;

Adjust the level of access depending on your needs (read-only, OLAP cube creation, writeback, etc.).

Triggers

Create triggers directly in the Nectari user configuration within OLAP Manager, ensuring the correct security level. This approach prevents issues where the ERP inserts rows into a table with a trigger but lacks access to the Nectari tracking tables.
Grant the required authorization to your user:

GRANT create any trigger TO YOUR_USER;

Configure your data source with the Nectari schema and user (e.g., use SEED as the source schema and NectariCube as the Nectari schema). When you build the OLAP Cube, place both the tracking table and the trigger in the Nectari schema, not in the source. This setup lets the trigger access the tracking table automatically.

When an ERP process inserts a record in the source (e.g., SEED.GACCENTRYD in SEED), Oracle executes the trigger with the Nectari user’s privileges, ensuring the tracking table is always accessible.

Revoke existing users permissions​

Create users​

Create Nectari user and schema​

Create BILicense user and schema​

Create NectariCube user​

Install Nectari package​

Additional database management​

Add space to an Oracle tablespace​

Remove Oracle users and tablespaces​

Oracle component definitions​

Storage​

Block size​

Tablespace and datafile​

Temporary tablespaces​

Logging and compression​

Logging​

Compression​

Logging and compression test results​

Security​

User and access​

Triggers​