6 Appendix A: Sample Script for Creating Oracle Tablespaces

DECLARE

cursor mcrspos_tablespace_check is 
    select tablespace_name 
    from dba_tablespaces
    where tablespace_name = 'MCRSPOS';
v_tablespace varchar2(40);
v_path VARCHAR2(100);
sql_stmt VARCHAR2(10000);

BEGIN

open mcrspos_tablespace_check;
fetch mcrspos_tablespace_check into v_tablespace;
    if mcrspos_tablespace_check%NOTFOUND
    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;
close mcrspos_tablespace_check;
END;
/

DECLARE

cursor MCRSCACHE_tablespace_check is 
    select tablespace_name 
    from dba_tablespaces
    where tablespace_name = 'MCRSCACHE';
v_tablespace varchar2(40);
v_path VARCHAR2(100);
sql_stmt VARCHAR2(10000);

BEGIN

open MCRSCACHE_tablespace_check;
fetch MCRSCACHE_tablespace_check into v_tablespace;
    if MCRSCACHE_tablespace_check%NOTFOUND
    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 128M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO'; 
  
 execute immediate sql_stmt;
 
    end if;
close MCRSCACHE_tablespace_check;
END;
/