oracle使用脚本创建数据表空间和用户
-- 创建表空间（企业库）-----
declare v_rowcount number(5);
begin
select count(*) into v_rowcount from dual
where exists (select 1 from dba_tablespaces where tablespace_name='PLSTWHEPT');
If v_rowcount = 0 Then
Execute immediate 'CREATE TABLESPACE PLSTWHEPT DATAFILE ''E:\app\lukelu\oradata\PLSTWHEPT.dbf'' SIZE 50M
AUTOEXTEND ON NEXT 1M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO';
End If;
end;
/
www.2cto.com
-- 创建表空间（用户库）
declare v_rowcount number(5);
begin
select count(*) into v_rowcount from dual
where exists (select 1 from dba_tablespaces where tablespace_name='PLSTWMWH1');
If v_rowcount = 0 Then
Execute immediate 'CREATE TABLESPACE PLSTWMWH1 DATAFILE ''E:\app\lukelu\oradata\PLSTWMWH1.dbf'' SIZE 50M
AUTOEXTEND ON NEXT 1M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO';
End If;
end;
/
---- 创建用户并把用户赋予某个空间----
declare v_rowcount number(5);
begin
select count(*) into v_rowcount from dual
where exists (select 1 from all_users where username= 'plstwms') ;
If v_rowcount = 0 Then
Execute immediate 'create user plstwms identified by "999666" DEFAULT TABLESPACE PLSTWHEPT TEMPORARY TABLESPACE TEMP';
End If;
end;
/
grant create any table to plstwms ;
grant dba to plstwms;
grant execute on DBMS_LOCK to plstwms;
grant select on DBA_OBJECTS to plstwms;
grant create any sequence to plstwms;
---- 创建用户----
declare v_rowcount number(5);
begin
select count(*) into v_rowcount from dual
where exists (select 1 from all_users where username= 'plstwms1') ;
If v_rowcount = 0 Then
Execute immediate 'create user plstwms1 identified by "999666" DEFAULT TABLESPACE PLSTWMWH1 TEMPORARY TABLESPACE TEMP';
End If;
end;
/
grant create any table to plstwms1 ;
grant dba to plstwms1;
grant execute on DBMS_LOCK to plstwms1;
grant select on DBA_OBJECTS to plstwms1;
grant create any sequence to plstwms1;