Oracle表空间和用户的建立与删除

发表于 分类于

推荐：

Oracle 11g 临时表空间管理

oracle11g下清理临时表空间

表空间和用户的建立顺序

第一步：建立表空间。
在设计数据库的时候，首先需要设计表空间。我们需要考虑，是只建立一个表空间呢，还是需要建立多个表空间，以及各个表空间的存放位置、磁盘限额等等。
到底设计多少个表空间合理，没有统一的说法，这主要根据企业的实际需求去判断。如企业需要对用户进行磁盘限额控制的，则就需要根据用户的数量来设置表空间。当企业的数据容量比较大，而其又对数据库的性能有比较高的要求时，就需要根据不同类型的数据，设置不同的表空间，以提高其输入输出性能。
第二步：建立用户，并制定用户的默认表空间。
在建立用户的时候，我们建议数据库管理员要指定用户的默认表空间。因为我们在利用CREATE语句创建数据库对象，如数据库表的时候，其默认是存储在数据库的当前默认空间。若不指定用户默认表空间的话，则用户每次创建数据库对象的时候，都要指定表空间，显然，这不是很合理。
另外要注意，不同的表空间有不同的权限控制。用户对于表空间A具有完全控制权限，可能对于表空间B就只有查询权限，甚至连连接的权限的都没有。所以，合理为用户配置表空间的访问权限，也是提高数据库安全性的一个方法。

查看表空间及其大小

1
select tablespace_name,file_name,status,bytes from dba_data_files;

创建表空间和用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建
create tablespace tablespace_name datafile 'D:\oracle\oradata\tablespace_name.dbf' size 256m;

-- 设置表空间超载时自动增长:
	-- 表空间将满时会自动增加256m大小空间:
	alter database datafile 'D:\oracle\oradata\tablespace_name.dbf' autoextend on next 256m;
	-- 表空间每次增加256M，累计增加到1024M后将不再增加:
	alter database datafile 'D:\oracle\oradata\tablespace_name.dbf' autoextend on next 256m maxsize 1024m;

-- 创建用户（指定默认表空间，所以要先创建表空间）:
create user username identified by passwd default tablespace tablespace_name temporary tablespace temp;
-- 授予用户权限:
grant dba,connect,resource to username;
-- 修改用户密码(处于锁定状态的用户需先解锁):
alter user username identified by new_passwd;
-- 解锁用户:
alter user username account unlock;

删除用户和表空间

删除是创建的相反顺序，先删除用户，再删除表空间。

先删除表空间再删除用户可能是允许的。但也有说这样操作会报错，因为表空间对象还在被用户使用，我没这样正式用过。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 删除用户:
drop user username cascade;
-- 删除表空间:
	-- 删除空表空间，不包含物理文件
	drop tablespace tablespace_name; -- tablespace_name不加.dbf后缀哦！！！
	-- 删除空表空间，包含物理文件
	drop tablespace tablespace_name including datafiles; 
	-- 删除空(非空)表空间，不包含物理文件
	drop tablespace tablespace_name including contents;
	-- 删除空(非空)表空间，包含物理文件
	drop tablespace tablespace_name including contents and datafiles;
	-- 如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
	drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

	-- including 说明
    including contents -- 删除表空间及对象；
    including contents and datafiles -- 删除表空间、对象及物理文件(数据文件tablespace_name.dbf)；
    including contents CASCADE CONSTRAINT -- 删除关联；

最终脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建表空间和用户
	--创建表空间 Linux
	create tablespace mars_data datafile '/u01/app/oracle/oradata/orcl/mars_data.dbf' size 256m;
	--创建表空间 Windows
	create tablespace mars_data datafile 'F:/oracle19c/oradata/ORCL/orclpdb/mars_data.dbf' size 256m;
	--表空间将满时会自动增加256m大小空间
	alter database datafile 'F:/oracle19c/oradata/ORCL/orclpdb/mars_data.dbf' autoextend on next 256m;
	--创建用户,指定默认表空间
	create user mars identified by 123456789 default tablespace mars_data temporary tablespace temp;
	--授予用户权限
	grant dba,connect,resource to mars;

-- 删除用户和表空间
	--删除用户
	drop user mars cascade;
	--删除空(非空)表空间，包含物理文件
	drop tablespace mars_data including contents and datafiles;