查看: 2006|回复: 0

[Oracle数据库] oracle创建表空间及用户

发表于 2018-1-14 11:36:47
  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 ;

  搞定!!!!!!



回复

使用道具 举报