Créer un utilisateur et tablespace Oracle

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;