--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 (+); data_ts_path varchar2 (513); data_ts_size varchar2 (; index_ts_name varchar2 (); Index_ts_path varchar2 (513); index_ts_size varchar2 (in); Lob_ts_ name varchar2 (; ) lob_ts_path VARCHAR2 (513); lob_ts_size varchar2 (; bcreatets ) boolean; vsql varchar2 (4000); begin execute immediate ' select file_name from sys.dba_data_files where tablespace_name = ' SYSTEM ' & nbsp 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; &nbs p; I: = i-1; end loop; Default_data_path: = substr (system_file, 1, i); Dbms_output.put_line (DEFA Ult_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 & nbsp From User_tablespaces where tablespace_name = ' pipdb_dat1 '; if (cnt = 0) then & nbsp; data_ts_name: = ' pipdb_dat1 '; bcreatets : = true; else bcreatets: = false; End if; else & nbsp; Select COUNT (*) into cnt from User_ tablespaces where tablespace_name = data_ts_name; if (cnt = 0) then bcreatets: = true; else bcreatets: = false;& nbsp; End if; End if; if bcreatets then data_ts_path: = ' ${pm.or.data_path} '; & nbsp; 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 '; &n bsp; 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 &nb Sp 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; & nbsp else bcreatets: = false; End if; End if; if BCreateTS then& nbsp; 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 Selec T 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& nbsp; 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  &NBsp; pls_integer; data_ts_name varchar2 (; data_ts_path varchar2 (513); Data_ ts_size varchar2 (+); index_ts_name varchar2 (30); index_ts_path varchar2 (513); index_ts_size varchar2 ( ; lob_ts_name varchar2 (); lob_ts_path varchar2 (513); lob_ts_size varchar2 (; ) bcreatets boolean; vsql varchar2 (4000); begin execute immediate ' select file_name from sys.dba_data_files where tablespace_name = ' SYSTEM ' & nbsp 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; D Efault_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 sel ECT 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 '; &n bsp; 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 &nb Sp Select COUNT (*) into cnt from user_tablespaces where tablespace_name = ' pipdb_ndx1 '; if (cnt = 0) then & nbsp 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 Selec T 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 '; &nb Sp 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 Database Creation table Space
