CREATE OR REPLACE Procedure SP_Create_TableSpace(SpaceName in varchar2)
as
SpaceCount number;
strFile varchar2(200);
strSql varchar2(2000);
begin
Select Count(tablespace_name) into SpaceCount
From User_tablespaces
Where Upper(TableSpace_name)=upper(SpaceName);
if SpaceCount>0 then
strSql:='Drop Tablespace ' || SpaceName || ' Including Contents AND DATAFILES';
execute immediate strSql;
end if;
Select file_name into strFile
From dba_data_files
Where upper(TableSpace_name)='SYSTEM';
strFile:=substr(strFile,1,instr(strFile,'/',-1)) || SpaceName || '.ora';
strSql:='CREATE TABLESPACE ' || SpaceName ||' LOGGING
DATAFILE ''' || strFile || ''' SIZE 5M AUTOEXTEND
ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE
MANAGEMENT AUTO' ;
dbms_output.put_line(strSql);
execute immediate strSql;
end;
--解决了在存储过程中用dba_data_files(字典表)提示(Ora-00942 error:table or view does not exist)的问题。
GRANT SELECT ANY DICTIONARY TO test;
--再加增删表空间的权限
Grant Create Tablespace to test;
Grant Drop Tablespace to test;
--调用
Declare
name varchar2(18);
Begin
name:='Charge';
SP_Create_TableSpace(name);
End;