Créer un utilisateur et tablespace Oracle

Pour les utilisateurs d'Oracle, à partir de 2021 R1.1 et UDM build 234, le modèle UDM ne crée plus automatiquement un utilisateur et tablespace Oracle pour l'utilisation de SEI. Ceci permet aux utilisateurs de SEI de configurer un utilisateur et tablespace Oracle selon leurs propres préférences et leur évite de se connecter aux bases de données en tant que leur utilisateur système.

  • SID : ID de système Oracle. Il identifie de manière unique une base de données (par exemple : ORCL).

  • Utilisateur : Les utilisateurs et les schémas sont essentiellement la même chose. Un utilisateur est le compte que vous utilisez pour vous connecter à une base de données, et un schéma est l'ensemble des objets (tables, vues, etc.) qui appartiennent à ce compte.

  • Tablespace : Une base de données Oracle est constituée d'une ou plusieurs unités de stockage logiques appelées tablespaces, qui stockent collectivement toutes les données de la base de données. Chaque tablespace est constitué d'un ou plusieurs fichiers appelés datafiles, qui sont des structures physiques conformes au système d'exploitation sur lequel fonctionne Oracle.

    Note  

    SEI prend en charge les fonctionnalités de tablespace Oracle telles que DEFAULT ROW STORE COMPRESS ADVANCED. Cependant, de nombreuses fonctionnalités de tablespace Oracle (y compris celle mentionnée précédemment) sont des fonctionnalités supplémentaires qui doivent être achetées séparément auprès d'Oracle. Veuillez contacter votre administrateur de bases de données Oracle si vous souhaitez en savoir plus sur l'achat de licences pour des fonctionnalités supplémentaires de tablespace.

  • Temp Tablespace : Un tablespace temporaire, comme son nom l'indique, stocke les données temporaires qui n'existent que pendant la session de la base de données. Oracle utilise les tablespaces temporaires pour améliorer la simultanéité des opérations de tri multiples qui ne tiennent pas en mémoire. Le temp tablespace peut être partagé par plusieurs utilisateurs.

  • Datafile : Les datafiles sont des fichiers physiques du système d'exploitation qui stockent les données de toutes les structures logiques de la base de données. Ils doivent être créés explicitement pour chaque tablespace. Oracle attribue à chaque datafile deux numéros de fichier, un numéro de fichier absolu et un numéro de fichier relatif, qui sont utilisés pour l'identifier de manière unique. Plusieurs datafiles peuvent être ajoutés au même tablespace (régulier ou temporaire).

  • Block Size : Taille du bloc pour les données. La taille du bloc peut être différente selon le tablespace mais elle est généralement la même. La taille du bloc aura un impact direct sur la taille maximale du datafile.

  • Block Size : La taille du bloc par défaut est souvent de 8 Ko donc le datafile a un maximum de 32 Go.

    Pour obtenir la taille de bloc actuelle de la base de données, exécutez la commande SQL suivante :

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

    Taille du bloc Taille maximum du datafile Taille maximum de la base de données
    2 Ko 8 Go 512 To
    4 Ko 16 Go 1 Po
    8 Ko 32 Go 2 Po
    16 Ko 64 Go 4 Po
    32 Ko 128 Go 8 Po

  • Tablespace et datafile : Par défaut, le tablespace ne contient qu'un seul datafile. En fonction de la taille du bloc, la taille par défaut d'un tablespace sera seulement de 32 Go. Vous devez ajouter des datafiles à un tablespace pour stocker davantage de données (tablespace régulier ou temporaire). Le nombre maximum de datafiles qui peuvent être ajoutés est de 65 536 fichiers par base de données, d'où la taille maximale de la base de données mentionnée ci-dessus (par exemple : 32 Go x 65 536 = 2 Po). Le nombre maximum de datafiles dans un tablespace est de 1022 (32 To pour un bloc de 8 Ko).

    Pour ajouter des datafiles à un tablespace, exécutez la commande SQL suivante (ajustez cette commande avec votre propre nom et les tailles souhaitées) :

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

    Pour obtenir la liste des fichiers de données attachés à un tablespace, exécutez la commande SQL suivante :

    SELECT TABLESPACE_NAME,FILE_NAME From DBA_DATA_FILES;

  • Temp tablespace : Le tablespace temporaire peut devenir très grand une fois que le gestionnaire OLAP a chargé les données (chargement complet). Cependant, l'espace utilisé reste stable pour les chargements suivants car il ne sera plus alloué.

    Oracle utilise des tablespaces temporaires pour améliorer la simultanéité des opérations de tri multiples qui ne tiennent pas en mémoire. En outre, Oracle stocke des tables temporaires, des index temporaires, des B-trees temporaires et des LOBs temporaires dans des tablespaces temporaires.

    Pour voir l'allocation d'espace d'un tablespace temporaire, exécutez la commande suivante :

    SELECT * FROM dba_temp_free_space;

    Pour ajouter des fichiers de données à un tablespace existant, exécutez le SQL suivant (ajustez cette commande avec votre propre nom et les tailles souhaitées) :

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

  • Journalisation : Dans Oracle, la journalisation est activée par défaut lors de la création du tablespace. Dans le cas de l'entrepôt de données, la journalisation n'est pas nécessaire car nous rechargeons simplement l'entrepôt de données au lieu d'utiliser la fonction annuler/refaire. La journalisation ayant un impact direct sur la taille du tablespace temporaire, il est recommandé de la désactiver dans la mesure du possible.

    Pour créer un nouveau tablespace sans journalisation, exécutez cette commande :

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

    Pour supprimer la journalisation d'un tablespace:

    ALTER TABLESPACE SEICUBE3 NOLOGGING;

  • Compression: Il existe plusieurs façons de compresser des données dans Oracle, mais celle que nous décrivons ici pour SEI est Advanced Row Compression. Advanced Row Compression a plusieurs avantages. Un avantage significatif est la capacité d'Oracle à lire les blocs compressés (données et index) directement en mémoire, sans décompresser les blocs. Cela améliore les performances grâce à la réduction des entrées/sorties, et à la réduction des appels système liés aux opérations d'entrée/sortie. De plus, le cache tampon devient plus efficace en stockant les données sans avoir à ajouter de la mémoire.

    Pour créer un nouveau tablespace avec compression et sans journalisation, exécutez cette commande :

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

    Le tableau ci-dessous décrit un test de journalisation et de compression sur un cube dont la base de données source comportait plus de 190 millions de lignes. La tranche maîtresse générée contenait 111 millions de rangs et 3 autres tranches contenaient plus de 94 millions de rangs. Tous les scénarios montrent clairement que la compression doit être désactivée. Il est fortement recommandé d'utiliser la compression car le temps de chargement n'était pas très différent et le stockage était grandement réduit.

    Taille du bloc Compression Journalisation Temps de chargement Taille du tablespace temporaire Taille des données Total
    8 Ko Oui Oui 1h28 24.7 TB? 68.8 TB? 93.5 TB?
    8 Ko Non Oui 1h16 83.2 106 189.2
    8 Ko Oui Non 1h28 25.2 70.2 95.4
    8 Ko Non Non 1h12 28 108 136
    16 Ko Non Non 1h12 25.2 106 131.2

  • Utilisateur et accès : Les utilisateurs sont créés sans accès à SEI par défaut. Vous devez créer un utilisateur spécifique SEI dans la configuration de la source de données pour éviter des limitations au niveau du tablespace.

    Pour créer un utilisateur et attacher le tablespace créé, exécutez cette commande :

    CREATE USER YOUR_USER IDENTIFIED BY YOUR_PASSWORD DEFAULT TABLESPACE YOUR_TABLESPACE

    TEMPORARY TABLESPACE YOUR_TEMP_TABLESPACE_TMP QUOTA UNLIMITED ON YOUR_TABLESPACE;

    Après avoir créé l'utilisateur SEI, vous devez lui accorder l'accès minimum suivant :

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

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

    Note  

    Pour toute source de données Oracle, l'utilisateur SEI doit être mis en tant que schéma personnalisé dans la définition de la source de données. Cela permettra à la table de suivi ou aux objets personnalisés d'être créés dans cet utilisateur spécifique au lieu de la source. Les déclencheurs (triggers) sont la seule exception à cette règle.

    Vous devez également accorder à l'utilisateur SEI accès en lecture tous les utilisateurs Oracle (bases de données) que vous voudrez lire comme source. Le niveau d'accès dépendra de ce que vous voulez faire (lecture seule, créer un cube OLAP, réécrire, etc.).

    Pour accorder à l'utilisateur SEI accès à une source, exécutez cette commande (modifiez SOURCE_USER et 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;

  • Déclencheurs : Les déclencheurs doivent être créés directement dans la configuration des utilisateurs SEI à partir du Gestionnaire OLAP avec le niveau d'accès approprié. Ceci est nécessaire pour éviter le cas où l'ERP insère des lignes dans une table avec un déclencheur qui n'aura pas l'accès approprié aux tables de suivi.

    Pour ajouter le niveau d'autorisation approprié à l'utilisateur :

    grant create any trigger to YOUR_USER;

    La source de données est configurée avec le schéma de SEI et l'utilisateur SEI. Vous devez indiquer SEED dans le schéma source et SEICube dans le schéma SEI.

    Lorsque vous construirez le cube OLAP, la table de suivi et le déclencheur seront créés dans l'utilisateur SEI, et aucune table ne sera créée dans l'utilisateur source. Le déclencheur aura donc automatiquement accès à la table de suivi.

    Lorsqu'un enregistrement est inséré dans l'utilisateur source (ex : SEED.GACCENTRYD) à partir d'un utilisateur source (ex : SEED), les données seront insérées et le trigger sera exécuté avec l'autorisation de l'utilisateur SEI puisqu'il réside dans l'utilisateur source.

L'exemple de code ci-dessous contient les droits minimums requis pour que le modèle fonctionne correctement. Il vous suffit de modifier les valeurs des trois variables pour qu'elles correspondent à vos informations (à la section /* Change the values */).

Copier 
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;