6 Appendix A: Sample Script for Creating Oracle Tablespaces
This sample script is meant for creating Oracle Tablespaces for Simphony versions prior to the release of Simphony version 19.2.1. Beginning with Simphony version 19.2.1, the installation application automatically creates all necessary 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;
/