oracle怎样创建表空间及用户

1.首先如果有用户存在需要删除用户的

drop user username cascade;

2.如果删除不了，可能情况当前用户处于连接状态，可以查找session

select username, sid, serial# from v$session WHERE USERNAME=username;

alter system kill session 'sid,serial#'

3.如果想要保险起见就先把当前用户lock掉;

alter user username account lock;

4.如果当前需要删除的表空间(临时)还是别的用户的默认表空间

select * from v$session

select username,default_tablespace from dba_users where username='用户名';

如果还存在两种处理方式:1.修改所查询到的用户默认表空间和临时表空间

alter user naro default tablespace TABLESPACENAME;

alter user naro default temporary tablespace TEMPNAME;

5.至此即可删除表空间(临时)

drop tablespace TABLESPACENAME including contents and datafiles CASCADE CONSTRAINTS;

drop tablespace TEMPSPACENAME including contents and datafiles CASCADE CONSTRAINTS;

6.创建新的表空间(临时)

create tablespace SPACENAME datafile 'E:\database\data' size 200M autoextend on next 200m maxsize 20480m extent management local;

create temporary tablespace TEMPNAME tempfile 'E:\database\temp' size 200M autoextend on next 200m maxsize 20480m extent management local;

7.创建用户赋权和默认表空间

CREATE USER username IDENTIFIED BY NARO ACCOUNT UNLOCK

DEFAULT TABLESPACE SPACENAME TEMPORARY TABLESPACE TEMPNAME ;

8.赋权

GRANT CONNECT,RESOURCE TO username ;

GRANT DBA TO username ;