Oracle資料庫建立資料表空間

標籤：arc   lin   text   style   pip   ble   sel   varchar   size   

--METADATA NAME:dbtk_or_create_tablespace TYPE:EXECDECLARE  default_data_path varchar2(513);  system_file       varchar2(513);  i                 pls_integer;  c                 char(1);  cnt               pls_integer;  data_ts_name      varchar2(30);  data_ts_path      varchar2(513);  data_ts_size      varchar2(30);  index_ts_name     varchar2(30);  index_ts_path     varchar2(513);  index_ts_size     varchar2(30);  lob_ts_name       varchar2(30);  lob_ts_path       varchar2(513);  lob_ts_size       varchar2(30);  bCreateTS         boolean;  vsql              varchar2(4000);BEGIN  execute immediate ‘select file_name from sys.dba_data_files where tablespace_name = ‘‘SYSTEM‘‘‘    into system_file;  i := length(system_file);  while (i > 0) loop    c := substr(system_file, i, 1);    if c = ‘/‘ or c = ‘\‘ then      exit;    end if;    i := i - 1;  end loop;  default_data_path := substr(system_file, 1, i);  dbms_output.put_line(default_data_path);  data_ts_name := ‘${pm.data_tablespace}‘;  if (instr(data_ts_name, ‘${‘) > 0 or length(data_ts_name) = 0) then    select count(*)      into cnt      from user_tablespaces     where tablespace_name = ‘PIPDB_DAT1‘;    if (cnt = 0) then      data_ts_name := ‘PIPDB_DAT1‘;      bCreateTS    := true;    else      bCreateTS := false;    end if;  else    select count(*)      into cnt      from user_tablespaces     where tablespace_name = data_ts_name;    if (cnt = 0) then      bCreateTS := true;    else      bCreateTS := false;    end if;  end if;  if bCreateTS then    data_ts_path := ‘${pm.or.data_path}‘;    if instr(data_ts_path, ‘${‘) > 0 or length(data_ts_path) = 0 or       data_ts_path is null then      data_ts_path := default_data_path || data_ts_name || ‘.DBF‘;    end if;    data_ts_size := ‘${pm.or.data_size}‘;    if instr(data_ts_size, ‘${‘) > 0 or length(data_ts_size) = 0 then      data_ts_size := ‘250M‘;    end if;    vsql := ‘create tablespace ‘ || data_ts_name || ‘ datafile ‘‘‘ ||            data_ts_path || ‘‘‘ size ‘ || data_ts_size ||            ‘ autoextend on next 10M‘;     dbms_output.put_line(vsql);     execute immediate vsql;  end if;  index_ts_name := ‘${pm.index_tablespace}‘;  if (instr(index_ts_name, ‘${‘) > 0 or length(index_ts_name) = 0) then    select count(*)      into cnt      from user_tablespaces     where tablespace_name = ‘PIPDB_NDX1‘;    if (cnt = 0) then      index_ts_name := ‘PIPDB_NDX1‘;      bCreateTS     := true;    else      bCreateTS := false;    end if;  else    select count(*)      into cnt      from user_tablespaces     where tablespace_name = index_ts_name;    if (cnt = 0) then      bCreateTS := true;    else      bCreateTS := false;    end if;  end if;  if bCreateTS then    index_ts_path := ‘${pm.or.index_path}‘;    if instr(index_ts_path, ‘${‘) > 0 or length(index_ts_path) = 0 or       index_ts_path is null then      index_ts_path := default_data_path || index_ts_name || ‘.DBF‘;    end if;    index_ts_size := ‘${pm.or.index_size}‘;    if instr(index_ts_size, ‘${‘) > 0 or length(index_ts_size) = 0 then      index_ts_size := ‘250M‘;    end if;    vsql := ‘create tablespace ‘ || index_ts_name || ‘ datafile ‘‘‘ ||            index_ts_path || ‘‘‘ size ‘ || index_ts_size ||            ‘ autoextend on next 10M‘;     dbms_output.put_line(vsql);     execute immediate vsql;  end if;  lob_ts_name := ‘${pm.lob_tablespace}‘;  if (instr(lob_ts_name, ‘${‘) > 0 or length(lob_ts_name) = 0) then    select count(*)      into cnt      from user_tablespaces     where tablespace_name = ‘PIPDB_LOB1‘;    if (cnt = 0) then      lob_ts_name := ‘PIPDB_LOB1‘;      bCreateTS   := true;    else      bCreateTS := false;    end if;  else    select count(*)      into cnt      from user_tablespaces     where tablespace_name = lob_ts_name;    if (cnt = 0) then      bCreateTS := true;    else      bCreateTS := false;    end if;  end if;  if bCreateTS then    lob_ts_path := ‘${pm.or.lob_path}‘;    if instr(lob_ts_path, ‘${‘) > 0 or length(lob_ts_path) = 0 or       lob_ts_path is null then      lob_ts_path := default_data_path || lob_ts_name || ‘.DBF‘;    end if;    lob_ts_size := ‘${pm.or.lob_size}‘;    if instr(lob_ts_size, ‘${‘) > 0 or length(lob_ts_size) = 0 then      lob_ts_size := ‘250M‘;    end if;    vsql := ‘create tablespace ‘ || lob_ts_name || ‘ datafile ‘‘‘ ||            lob_ts_path || ‘‘‘ size ‘ || lob_ts_size ||            ‘ autoextend on next 10M‘;     dbms_output.put_line(vsql);     execute immediate vsql;  end if;END;--METADATA NAME:dbtk_or_create_tablespace TYPE:EXECDECLARE  default_data_path varchar2(513);  system_file       varchar2(513);  i                 pls_integer;  c                 char(1);  cnt               pls_integer;  data_ts_name      varchar2(30);  data_ts_path      varchar2(513);  data_ts_size      varchar2(30);  index_ts_name     varchar2(30);  index_ts_path     varchar2(513);  index_ts_size     varchar2(30);  lob_ts_name       varchar2(30);  lob_ts_path       varchar2(513);  lob_ts_size       varchar2(30);  bCreateTS         boolean;  vsql              varchar2(4000);BEGIN  execute immediate ‘select file_name from sys.dba_data_files where tablespace_name = ‘‘SYSTEM‘‘‘    into system_file;  i := length(system_file);  while (i > 0) loop    c := substr(system_file, i, 1);    if c = ‘/‘ or c = ‘\‘ then      exit;    end if;    i := i - 1;  end loop;  default_data_path := substr(system_file, 1, i);  dbms_output.put_line(default_data_path);  data_ts_name := ‘${pm.data_tablespace}‘;  if (instr(data_ts_name, ‘${‘) > 0 or length(data_ts_name) = 0) then    select count(*)      into cnt      from user_tablespaces     where tablespace_name = ‘PIPDB_DAT1‘;    if (cnt = 0) then      data_ts_name := ‘PIPDB_DAT1‘;      bCreateTS    := true;    else      bCreateTS := false;    end if;  else    select count(*)      into cnt      from user_tablespaces     where tablespace_name = data_ts_name;    if (cnt = 0) then      bCreateTS := true;    else      bCreateTS := false;    end if;  end if;  if bCreateTS then    data_ts_path := ‘${pm.or.data_path}‘;    if instr(data_ts_path, ‘${‘) > 0 or length(data_ts_path) = 0 or       data_ts_path is null then      data_ts_path := default_data_path || data_ts_name || ‘.DBF‘;    end if;    data_ts_size := ‘${pm.or.data_size}‘;    if instr(data_ts_size, ‘${‘) > 0 or length(data_ts_size) = 0 then      data_ts_size := ‘250M‘;    end if;    vsql := ‘create tablespace ‘ || data_ts_name || ‘ datafile ‘‘‘ ||            data_ts_path || ‘‘‘ size ‘ || data_ts_size ||            ‘ autoextend on next 10M‘;     dbms_output.put_line(vsql);     execute immediate vsql;  end if;  index_ts_name := ‘${pm.index_tablespace}‘;  if (instr(index_ts_name, ‘${‘) > 0 or length(index_ts_name) = 0) then    select count(*)      into cnt      from user_tablespaces     where tablespace_name = ‘PIPDB_NDX1‘;    if (cnt = 0) then      index_ts_name := ‘PIPDB_NDX1‘;      bCreateTS     := true;    else      bCreateTS := false;    end if;  else    select count(*)      into cnt      from user_tablespaces     where tablespace_name = index_ts_name;    if (cnt = 0) then      bCreateTS := true;    else      bCreateTS := false;    end if;  end if;  if bCreateTS then    index_ts_path := ‘${pm.or.index_path}‘;    if instr(index_ts_path, ‘${‘) > 0 or length(index_ts_path) = 0 or       index_ts_path is null then      index_ts_path := default_data_path || index_ts_name || ‘.DBF‘;    end if;    index_ts_size := ‘${pm.or.index_size}‘;    if instr(index_ts_size, ‘${‘) > 0 or length(index_ts_size) = 0 then      index_ts_size := ‘250M‘;    end if;    vsql := ‘create tablespace ‘ || index_ts_name || ‘ datafile ‘‘‘ ||            index_ts_path || ‘‘‘ size ‘ || index_ts_size ||            ‘ autoextend on next 10M‘;     dbms_output.put_line(vsql);     execute immediate vsql;  end if;  lob_ts_name := ‘${pm.lob_tablespace}‘;  if (instr(lob_ts_name, ‘${‘) > 0 or length(lob_ts_name) = 0) then    select count(*)      into cnt      from user_tablespaces     where tablespace_name = ‘PIPDB_LOB1‘;    if (cnt = 0) then      lob_ts_name := ‘PIPDB_LOB1‘;      bCreateTS   := true;    else      bCreateTS := false;    end if;  else    select count(*)      into cnt      from user_tablespaces     where tablespace_name = lob_ts_name;    if (cnt = 0) then      bCreateTS := true;    else      bCreateTS := false;    end if;  end if;  if bCreateTS then    lob_ts_path := ‘${pm.or.lob_path}‘;    if instr(lob_ts_path, ‘${‘) > 0 or length(lob_ts_path) = 0 or       lob_ts_path is null then      lob_ts_path := default_data_path || lob_ts_name || ‘.DBF‘;    end if;    lob_ts_size := ‘${pm.or.lob_size}‘;    if instr(lob_ts_size, ‘${‘) > 0 or length(lob_ts_size) = 0 then      lob_ts_size := ‘250M‘;    end if;    vsql := ‘create tablespace ‘ || lob_ts_name || ‘ datafile ‘‘‘ ||            lob_ts_path || ‘‘‘ size ‘ || lob_ts_size ||            ‘ autoextend on next 10M‘;     dbms_output.put_line(vsql);     execute immediate vsql;  end if;END;

Oracle資料庫建立資料表空間