使用存储过程创建一个表空间

 

set echo off
set serveroutput on
create or replace procedure sp_tmp_create_tablespace(p_in_tablespace_name in varchar2,
                                               p_in_tablespace_path in varchar2,
                                               p_in_tablespace_size in number,
                                               p_in_extend_size in number) as
  v_tablespace_count number(8);
  v_sql              varchar2(2000);
  v_errmsg           varchar2(300);
begin
  select count(*) into v_tablespace_count from user_tablespaces where upper(tablespace_name)=upper(p_in_tablespace_name);
  if v_tablespace_count = 0 then
    v_sql := 'CREATE TABLESPACE ' || p_in_tablespace_name || ' NOLOGGING DATAFILE '''|| p_in_tablespace_path || '/' ||

p_in_tablespace_name || '.dbf'' SIZE ' || p_in_tablespace_size || 'M AUTOEXTEND ON NEXT ' ||p_in_extend_size || ' MAXSIZE

UNLIMITED';
    execute immediate v_sql;
    dbms_output.put_line('tablespce ' || p_in_tablespace_name || ' has created!');
  else
    dbms_output.put_line('tablespce ' || p_in_tablespace_name || 'already exists.');
  end if;
    exception
    when others then
      v_errmsg:=SQLERRM;
      dbms_output.put_line(v_errmsg);
      return;
end sp_tmp_create_tablespace;
/
exec sp_tmp_create_tablespace('tabspaceName','/oradata/sanexcfg',2048,64);

drop procedure sp_tmp_create_tablespace;