PL/SQL创造表空间、创建用户、给用户授权

PL/SQL创建表空间、创建用户、给用户授权

PL/SQL创建表空间、创建用户、给用户授权
declare
usrName varchar2(20) := '用户名';
pwd varchar2(20) := '密码';
tsName varchar2(20) := 'xmszcgl_aid';
dfPath varchar2(300) := 'D:\dataSource\xmszcgl_aid.data';
tmpStr varchar2(300);
isDelUsrAndTs boolean := false;
begin
if isDelUsrAndTs then
tmpStr := 'drop user '||usrName||' cascade';
execute immediate tmpStr;
tmpStr := 'drop tablespace '||tsName||' including contents and datafiles';
execute immediate tmpStr;
end if;
tmpStr := 'create tablespace '||tsName||' datafile '''||dfPath||''' size 300m autoextend on next 5m maxsize unlimited';
execute immediate tmpStr;

tmpStr := 'create user '||usrName||' identified by '||pwd||' default tablespace '||tsName;
execute immediate tmpStr;

tmpStr := 'grant connect,resource,dba to '||usrName;
execute immediate tmpStr;
end;