6 Appendix A: Sample Script for Creating Oracle Tablespaces

Create the following Simphony Oracle Database Tablespaces on the database server or servers:

/* MCRSPOS */
DECLARE
    CURSOR mcrspos_tablespace_check IS
    SELECT tablespace_name
    FROM dba_tablespaces
    WHERE tablespace_name = 'MCRSPOS';

    CURSOR mcrspos_platform_check_w IS
    SELECT substr(platform_name, 1, 9) 
    FROM gv$database;

    CURSOR mcrspos_platform_check_l IS
    SELECT substr(platform_name, 1, 5) 
    FROM gv$database;
    
    CURSOR mcrspos_rac_check IS
    select value from gv$parameter 
    where name = 'cluster_database';
    
    CURSOR mcrspos_asm_check IS
    select substr(file_name, 1,1) 
    from dba_data_files;

    v_tablespace   VARCHAR2(40);
    v_path         VARCHAR2(100);
    sql_stmt       VARCHAR2(10000);
    v_platform_w   VARCHAR2(40);
    v_platform_l   VARCHAR2(40);
    v_rac          VARCHAR2(10);
    v_asm          VARCHAR2(10);
    
BEGIN
    OPEN mcrspos_tablespace_check;
    OPEN mcrspos_platform_check_w;
    OPEN mcrspos_platform_check_l;
    OPEN mcrspos_rac_check;
    OPEN mcrspos_asm_check;
    FETCH mcrspos_tablespace_check INTO v_tablespace;
    FETCH mcrspos_platform_check_w INTO v_platform_w;
    FETCH mcrspos_platform_check_l INTO v_platform_l;
    FETCH mcrspos_rac_check INTO v_rac;
    FETCH mcrspos_asm_check INTO v_asm;
    IF mcrspos_tablespace_check%NOTFOUND AND v_platform_w = 'Microsoft' THEN
        SELECT substr(file_name, 1,((instr(file_name, '\', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;

        sql_stmt := 'CREATE TABLESPACE MCRSPOS LOGGING DATAFILE ' || '''' || v_path || 'MCRSPOS01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
        EXECUTE IMMEDIATE sql_stmt;
     ELSE  
        IF mcrspos_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac <> 'TRUE' THEN
            SELECT substr(file_name, 1,((instr(file_name, '/', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;

            sql_stmt := 'CREATE TABLESPACE MCRSPOS LOGGING DATAFILE ' || '''' || v_path || 'MCRSPOS01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
     ELSE  
        IF mcrspos_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac = 'TRUE' AND v_asm = '+' THEN

            sql_stmt := 'CREATE TABLESPACE MCRSPOS DATAFILE ''+DATA'' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE ENCRYPTION USING ''AES256'' DEFAULT NOCOMPRESS STORAGE(ENCRYPT) SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
     ELSE
        IF mcrspos_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac = 'TRUE' AND v_asm = '/' THEN
            SELECT substr(file_name, 1,((instr(file_name, '/', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;
        
            sql_stmt := 'CREATE TABLESPACE MCRSPOS LOGGING DATAFILE ' || '''' || v_path || 'MCRSPOS01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
        END IF;
        END IF;
        END IF;
    END IF;
    CLOSE mcrspos_tablespace_check;
    CLOSE mcrspos_platform_check_w;
    CLOSE mcrspos_platform_check_l;
    CLOSE mcrspos_rac_check;
    CLOSE mcrspos_asm_check;
END;
/
/* MCRSCACHE */
DECLARE
    CURSOR mcrscache_tablespace_check IS
    SELECT tablespace_name
    FROM dba_tablespaces
    WHERE tablespace_name = 'MCRSCACHE';

    CURSOR mcrscache_platform_check_w IS
    SELECT substr(platform_name, 1, 9) 
    FROM gv$database;

    CURSOR mcrscache_platform_check_l IS
    SELECT substr(platform_name, 1, 5) 
    FROM gv$database;
    
    CURSOR mcrscache_rac_check IS
    select value from gv$parameter 
    where name = 'cluster_database';
        
        CURSOR mcrscache_asm_check IS
    select substr(file_name, 1,1) 
    from dba_data_files;

    v_tablespace   VARCHAR2(40);
    v_path         VARCHAR2(100);
    sql_stmt       VARCHAR2(10000);
    v_platform_w   VARCHAR2(40);
    v_platform_l   VARCHAR2(40);
    v_rac          VARCHAR2(10);
    v_asm          VARCHAR2(10);
    
BEGIN
    OPEN mcrscache_tablespace_check;
    OPEN mcrscache_platform_check_w;
    OPEN mcrscache_platform_check_l;
    OPEN mcrscache_rac_check;
        OPEN mcrscache_asm_check;
    FETCH mcrscache_tablespace_check INTO v_tablespace;
    FETCH mcrscache_platform_check_w INTO v_platform_w;
    FETCH mcrscache_platform_check_l INTO v_platform_l;
    FETCH mcrscache_rac_check INTO v_rac;
        FETCH mcrscache_asm_check INTO v_asm;
    IF mcrscache_tablespace_check%NOTFOUND AND v_platform_w = 'Microsoft' THEN
        SELECT substr(file_name, 1,((instr(file_name, '\', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;

        sql_stmt := 'CREATE TABLESPACE MCRSCACHE LOGGING DATAFILE ' || '''' || v_path || 'MCRSCACHE01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
        EXECUTE IMMEDIATE sql_stmt;
     ELSE  
        IF mcrscache_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac <> 'TRUE' THEN
            SELECT substr(file_name, 1,((instr(file_name, '/', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;

            sql_stmt := 'CREATE TABLESPACE MCRSCACHE LOGGING DATAFILE ' || '''' || v_path || 'MCRSCACHE01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
     ELSE  
        IF mcrscache_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac = 'TRUE' AND v_asm = '+' THEN

            sql_stmt := 'CREATE TABLESPACE MCRSCACHE DATAFILE ''+DATA'' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE ENCRYPTION USING ''AES256'' DEFAULT NOCOMPRESS STORAGE(ENCRYPT) SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
         ELSE
        IF mcrscache_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac = 'TRUE' AND v_asm = '/' THEN
            SELECT substr(file_name, 1,((instr(file_name, '/', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;
        
            sql_stmt := 'CREATE TABLESPACE MCRSCACHE LOGGING DATAFILE ' || '''' || v_path || 'MCRSCACHE01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
        END IF;
        END IF;
        END IF;
    END IF;
    CLOSE mcrscache_tablespace_check;
    CLOSE mcrscache_platform_check_w;
    CLOSE mcrscache_platform_check_l;
    CLOSE mcrscache_rac_check;
        CLOSE mcrscache_asm_check;
END;
/
/* MCRSPOS_PAR */
DECLARE
    CURSOR mcrspos_par_tablespace_check IS
    SELECT tablespace_name
    FROM dba_tablespaces
    WHERE tablespace_name = 'MCRSPOS_PAR';

    CURSOR mcrspos_par_platform_check_w IS
    SELECT substr(platform_name, 1, 9) 
    FROM gv$database;

    CURSOR mcrspos_par_platform_check_l IS
    SELECT substr(platform_name, 1, 5) 
    FROM gv$database;
    
    CURSOR mcrspos_par_rac_check IS
    select value from gv$parameter 
    where name = 'cluster_database';
    
    CURSOR mcrspos_par_asm_check IS
    select substr(file_name, 1,1) 
    from dba_data_files;

    v_tablespace   VARCHAR2(40);
    v_path         VARCHAR2(100);
    sql_stmt       VARCHAR2(10000);
    sql_stmt2       VARCHAR2(10000);
    v_platform_w   VARCHAR2(40);
    v_platform_l   VARCHAR2(40);
    v_rac          VARCHAR2(10);
    v_asm          VARCHAR2(10);
    
BEGIN
    OPEN mcrspos_par_tablespace_check;
    OPEN mcrspos_par_platform_check_w;
    OPEN mcrspos_par_platform_check_l;
    OPEN mcrspos_par_rac_check;
    OPEN mcrspos_par_asm_check;
    FETCH mcrspos_par_tablespace_check INTO v_tablespace;
    FETCH mcrspos_par_platform_check_w INTO v_platform_w;
    FETCH mcrspos_par_platform_check_l INTO v_platform_l;
    FETCH mcrspos_par_rac_check INTO v_rac;
    FETCH mcrspos_par_asm_check INTO v_asm;
    IF mcrspos_par_tablespace_check%NOTFOUND AND v_platform_w = 'Microsoft' THEN
        SELECT substr(file_name, 1,((instr(file_name, '\', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;

        sql_stmt := 'CREATE TABLESPACE MCRSPOS_PAR LOGGING DATAFILE ' || '''' || v_path || 'MCRSPOS_PAR01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
        EXECUTE IMMEDIATE sql_stmt;
        sql_stmt2 := 'ALTER TABLESPACE MCRSPOS_PAR ADD DATAFILE ' || '''' || v_path || 'MCRSPOS_PAR02.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
        EXECUTE IMMEDIATE sql_stmt2;
     ELSE  
        IF mcrspos_par_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac <> 'TRUE' THEN
            SELECT substr(file_name, 1,((instr(file_name, '/', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;

            sql_stmt := 'CREATE TABLESPACE MCRSPOS_PAR LOGGING DATAFILE ' || '''' || v_path || 'MCRSPOS_PAR01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
            sql_stmt2 := 'ALTER TABLESPACE MCRSPOS_PAR ADD DATAFILE ' || '''' || v_path || 'MCRSPOS_PAR02.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
            EXECUTE IMMEDIATE sql_stmt2;
     ELSE  
        IF mcrspos_par_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac = 'TRUE' AND v_asm = '+' THEN

            sql_stmt := 'CREATE TABLESPACE MCRSPOS_PAR DATAFILE ''+DATA'' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
            sql_stmt2 := 'ALTER TABLESPACE MCRSPOS_PAR ADD DATAFILE ''+DATA'' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
            EXECUTE IMMEDIATE sql_stmt2;
     ELSE
        IF mcrspos_par_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac = 'TRUE' AND v_asm = '/' THEN
            SELECT substr(file_name, 1,((instr(file_name, '/', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;
        
            sql_stmt := 'CREATE TABLESPACE MCRSPOS_PAR LOGGING DATAFILE ' || '''' || v_path || 'MCRSPOS_PAR01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
            sql_stmt2 := 'ALTER TABLESPACE MCRSPOS_PAR ADD DATAFILE ' || '''' || v_path || 'MCRSPOS_PAR02.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
            EXECUTE IMMEDIATE sql_stmt2;
        END IF;
        END IF;
        END IF;
    END IF;
    CLOSE mcrspos_par_tablespace_check;
    CLOSE mcrspos_par_platform_check_w;
    CLOSE mcrspos_par_platform_check_l;
    CLOSE mcrspos_par_rac_check;
    CLOSE mcrspos_par_asm_check;
END;
/
/* MCRSPOS_NDX */
DECLARE
    CURSOR mcrspos_ndx_tablespace_check IS
    SELECT tablespace_name
    FROM dba_tablespaces
    WHERE tablespace_name = 'MCRSPOS_NDX';

    CURSOR mcrspos_ndx_platform_check_w IS
    SELECT substr(platform_name, 1, 9) 
    FROM gv$database;

    CURSOR mcrspos_ndx_platform_check_l IS
    SELECT substr(platform_name, 1, 5) 
    FROM gv$database;
    
    CURSOR mcrspos_ndx_rac_check IS
    select value from gv$parameter 
    where name = 'cluster_database';
    
    CURSOR mcrspos_ndx_asm_check IS
    select substr(file_name, 1,1) 
    from dba_data_files;

    v_tablespace   VARCHAR2(40);
    v_path         VARCHAR2(100);
    sql_stmt       VARCHAR2(10000);
    sql_stmt2       VARCHAR2(10000);
    v_platform_w   VARCHAR2(40);
    v_platform_l   VARCHAR2(40);
    v_rac          VARCHAR2(10);
    v_asm          VARCHAR2(10);
    
BEGIN
    OPEN mcrspos_ndx_tablespace_check;
    OPEN mcrspos_ndx_platform_check_w;
    OPEN mcrspos_ndx_platform_check_l;
    OPEN mcrspos_ndx_rac_check;
    OPEN mcrspos_ndx_asm_check;
    FETCH mcrspos_ndx_tablespace_check INTO v_tablespace;
    FETCH mcrspos_ndx_platform_check_w INTO v_platform_w;
    FETCH mcrspos_ndx_platform_check_l INTO v_platform_l;
    FETCH mcrspos_ndx_rac_check INTO v_rac;
    FETCH mcrspos_ndx_asm_check INTO v_asm;
    IF mcrspos_ndx_tablespace_check%NOTFOUND AND v_platform_w = 'Microsoft' THEN
        SELECT substr(file_name, 1,((instr(file_name, '\', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;

        sql_stmt := 'CREATE TABLESPACE MCRSPOS_NDX LOGGING DATAFILE ' || '''' || v_path || 'MCRSPOS_NDX01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
        EXECUTE IMMEDIATE sql_stmt;
        sql_stmt2 := 'ALTER TABLESPACE MCRSPOS_NDX ADD DATAFILE ' || '''' || v_path || 'MCRSPOS_NDX02.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
        EXECUTE IMMEDIATE sql_stmt2;
     ELSE  
        IF mcrspos_ndx_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac <> 'TRUE' THEN
            SELECT substr(file_name, 1,((instr(file_name, '/', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;

            sql_stmt := 'CREATE TABLESPACE MCRSPOS_NDX LOGGING DATAFILE ' || '''' || v_path || 'MCRSPOS_NDX01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
            sql_stmt2 := 'ALTER TABLESPACE MCRSPOS_NDX ADD DATAFILE ' || '''' || v_path || 'MCRSPOS_NDX02.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
            EXECUTE IMMEDIATE sql_stmt2;
     ELSE  
        IF mcrspos_ndx_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac = 'TRUE' AND v_asm = '+' THEN

            sql_stmt := 'CREATE TABLESPACE MCRSPOS_NDX DATAFILE ''+DATA'' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
            sql_stmt2 := 'ALTER TABLESPACE MCRSPOS_NDX ADD DATAFILE ''+DATA'' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
            EXECUTE IMMEDIATE sql_stmt2;
     ELSE
        IF mcrspos_ndx_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac = 'TRUE' AND v_asm = '/' THEN
            SELECT substr(file_name, 1,((instr(file_name, '/', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;
        
            sql_stmt := 'CREATE TABLESPACE MCRSPOS_NDX LOGGING DATAFILE ' || '''' || v_path || 'MCRSPOS_NDX01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
            sql_stmt2 := 'ALTER TABLESPACE MCRSPOS_NDX ADD DATAFILE ' || '''' || v_path || 'MCRSPOS_NDX02.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
            EXECUTE IMMEDIATE sql_stmt2;
        END IF;
        END IF;
        END IF;
    END IF;
    CLOSE mcrspos_ndx_tablespace_check;
    CLOSE mcrspos_ndx_platform_check_w;
    CLOSE mcrspos_ndx_platform_check_l;
    CLOSE mcrspos_ndx_rac_check;
    CLOSE mcrspos_ndx_asm_check;
END;
/
/* MCRSPOS_LOB */
DECLARE
    CURSOR mcrspos_lob_tablespace_check IS
    SELECT tablespace_name
    FROM dba_tablespaces
    WHERE tablespace_name = 'MCRSPOS_LOB';

    CURSOR mcrspos_lob_platform_check_w IS
    SELECT substr(platform_name, 1, 9) 
    FROM gv$database;

    CURSOR mcrspos_lob_platform_check_l IS
    SELECT substr(platform_name, 1, 5) 
    FROM gv$database;
    
    CURSOR mcrspos_lob_rac_check IS
    select value from gv$parameter 
    where name = 'cluster_database';
    
    CURSOR mcrspos_lob_asm_check IS
    select substr(file_name, 1,1) 
    from dba_data_files;

    v_tablespace   VARCHAR2(40);
    v_path         VARCHAR2(100);
    sql_stmt       VARCHAR2(10000);
    sql_stmt2       VARCHAR2(10000);
    v_platform_w   VARCHAR2(40);
    v_platform_l   VARCHAR2(40);
    v_rac          VARCHAR2(10);
    v_asm          VARCHAR2(10);
    
BEGIN
    OPEN mcrspos_lob_tablespace_check;
    OPEN mcrspos_lob_platform_check_w;
    OPEN mcrspos_lob_platform_check_l;
    OPEN mcrspos_lob_rac_check;
    OPEN mcrspos_lob_asm_check;
    FETCH mcrspos_lob_tablespace_check INTO v_tablespace;
    FETCH mcrspos_lob_platform_check_w INTO v_platform_w;
    FETCH mcrspos_lob_platform_check_l INTO v_platform_l;
    FETCH mcrspos_lob_rac_check INTO v_rac;
    FETCH mcrspos_lob_asm_check INTO v_asm;
    IF mcrspos_lob_tablespace_check%NOTFOUND AND v_platform_w = 'Microsoft' THEN
        SELECT substr(file_name, 1,((instr(file_name, '\', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;

        sql_stmt := 'CREATE TABLESPACE MCRSPOS_LOB LOGGING DATAFILE ' || '''' || v_path || 'MCRSPOS_LOB01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
        EXECUTE IMMEDIATE sql_stmt;
        sql_stmt2 := 'ALTER TABLESPACE MCRSPOS_LOB ADD DATAFILE ' || '''' || v_path || 'MCRSPOS_LOB02.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
        EXECUTE IMMEDIATE sql_stmt2;
     ELSE  
        IF mcrspos_lob_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac <> 'TRUE' THEN
            SELECT substr(file_name, 1,((instr(file_name, '/', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;

            sql_stmt := 'CREATE TABLESPACE MCRSPOS_LOB LOGGING DATAFILE ' || '''' || v_path || 'MCRSPOS_LOB01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
            sql_stmt2 := 'ALTER TABLESPACE MCRSPOS_LOB ADD DATAFILE ' || '''' || v_path || 'MCRSPOS_LOB02.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
            EXECUTE IMMEDIATE sql_stmt2;
     ELSE  
        IF mcrspos_lob_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac = 'TRUE' and v_asm = '+' THEN

            sql_stmt := 'CREATE TABLESPACE MCRSPOS_LOB DATAFILE ''+DATA'' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
            sql_stmt2 := 'ALTER TABLESPACE MCRSPOS_LOB ADD DATAFILE ''+DATA'' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
            EXECUTE IMMEDIATE sql_stmt2;
     ELSE
        IF mcrspos_lob_tablespace_check%NOTFOUND AND v_platform_l = 'Linux' AND v_rac = 'TRUE' AND v_asm = '/' THEN
            SELECT substr(file_name, 1,((instr(file_name, '/', - 1, 1)))) INTO v_path FROM dba_data_files WHERE ROWNUM < 2;
        
            sql_stmt := 'CREATE TABLESPACE MCRSPOS_LOB LOGGING DATAFILE ' || '''' || v_path || 'MCRSPOS_LOB01.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            EXECUTE IMMEDIATE sql_stmt;
            sql_stmt2 := 'ALTER TABLESPACE MCRSPOS_LOB ADD DATAFILE ' || '''' || v_path || 'MCRSPOS_LOB02.dbf' || '''' || ' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
            EXECUTE IMMEDIATE sql_stmt2;
        END IF;
        END IF;
        END IF;
    END IF;
    CLOSE mcrspos_lob_tablespace_check;
    CLOSE mcrspos_lob_platform_check_w;
    CLOSE mcrspos_lob_platform_check_l;
    CLOSE mcrspos_lob_rac_check;
    CLOSE mcrspos_lob_asm_check;
END;
/