oracle表空间 数据文件 用户的创建及删除

oracle数据库：数据库对象以及表数据都存储在表空间中，创建用户时可以指定对应的表空间。这样用户可以在各自的表空间中操作数据，互不干扰。

1. 表空间创建

若不清楚表空间对应文件的路径，可以登录系统用户(sys/system)查看已有表空间对应数据文件的路径

select * from dba_data_files;

• 临时表空间

用作数据库运算过程中的数据存储，或临时表数据存储等。用完之后系统会自动清理。若不创建则使用系统默认的临时表空间TEMP

CREATE TEMPORARY TABLESPACE tempname --表空间名称
TEMPFILE '/oracle/oradata/orcl/temp001.dbf' --文件路径
SIZE 200m --初始大小
AUTOEXTEND ON NEXT 32m --自动扩展每次多少
MAXSIZE 2048m --最大多少（也可以设置为UNLIMITED，则最大为32g）
EXTENT MANAGEMENT LOCAL; 

• 永久性表空间

存储数据库对象，以及表数据。与临时表空间最大的区别是数据永久性，不会被系统自动清空。

CREATE TABLESPACE DB_DATA
DATAFILE '/oracle/oradata/orcl/DB_DATA01.dbf'
SIZE 32m
AUTOEXTEND ON NEXT 32m
MAXSIZE 2048m
EXTENT MANAGEMENT LOCAL;

2. 数据文件新增及修改

• 为表空间添加数据文件

当表空间对应的数据文件已经增长到上限值（自己设置的上限值，或者32g），此时则需要为该表空间新增一个数据文件。

ALTER TABLESPACE DB_DATA ADD DATAFILE '/oracle/oradata/orcl/DB_DATA02.dbf' 
SIZE 50M
AUTOEXTEND ON NEXT 5M 
MAXSIZE 100M;

• 已存在的数据文件属性修改

ALTER DATABASE DATAFILE '/oracle/oradata/orcl/DB_DATA02.dbf'
AUTOEXTEND ON NEXT 5M 
MAXSIZE 100M;

• 已存在的数据文件大小调整

ALTER DATABASE DATAFILE '/oracle/oradata/orcl/DB_DATA02.dbf'
RESIZE 100M;

3. 用户创建

sys用户是超级用户，具有最高权限，具有sysdba角色，有create database的权限
system用户是管理操作员，权限也很大。具有sysoper角色，没有create database的权限

登录系统用户(sys/system)，创建一般用户。用户指定的profile是限制数据库用户使用资源的一种手段

-- 创建用户 
create user username --用户名
identified by password --密码
  default tablespace DB_DATA --指定永久性表空间
  temporary tablespace TEMP --指定临时表空间
  profile DEFAULT; --默认的profile

--查看profile的各个参数信息
select * from dba_profiles where profile='DEFAULT';  

4. 用户授权

oracle中权限分为系统权限和对象权限。可以直接授权，也可以通过授予角色得到角色的权限。对于表空间默认是无限额的，不建议让用户在其他表空间随意建表。一般授予 connect，resource即可。

--把角色授予用户，该用户也将拥有该角色的权限
grant connect to username; --连接数据库
grant resource to username; --创建数据库实体（表，过程等）
grant dba to username; --创建数据库结构

--针对表空间使用
grant unlimited tablespace to username; --用户可以再其他表空间随意建表，且无限额

--系统权限（此处列举几个）
grant create cluster to username;
grant create procedure to username;
grant create synonym to username;
grant create trigger to username;
grant create view to username;
grant create JOB to username;
grant drop any table to username;

--对象权限（此处列举几个）
grant delete any table to username;
grant update any table to username;
grant insert any table to username;
grant select any table to username;

5、删除用户

删除用户时，经常会碰到有人正在连接，无法删除。需要登录系统用户(sys/system)批量强制关闭连接。

--执行查询出来的内容即可
select 'alter system kill session' '' || sid || ',' || serial# || ''';'
from v$session 
where username = 'XXX'; --填写需要删除的用户名

清除连接之后，就可以在sys用户下删除用户了，若用户下已存在对象，则需要带上 cascade，否则执行报错。

drop user XXX cascade;

6、删除表空间

--删除表空间
drop tablespace XXX;
--删除表空间同时删除对应的数据文件
drop tablespace XXX including contents and datafiles;

ps：以上脚本执行顺序：创建表空间-->创建用户。删除用户-->删除表空间。

对于权限，角色，用户的说明，可以参看 https://www.cnblogs.com/mohai/archive/2013/05/10/3071166.html