Oracle数据库创建表空间
时间：2021-07-01 10:21:17
帮助过：11人阅读
--METADATA NAME:dbtk_or_create_tablespace TYPE:EXEC
DECLARE
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:EXEC
DECLARE
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数据库创建表空间
标签：arc lin text style pip ble sel varchar size
