.Net程序员学用Oracle系列(4)：四个基本概念

1、表空间
- 1.1、创建、修改及删除表空间
- 1.2、为对象指定表空间
2、SCHEMA
3、用户
- 3.1、创建用户、删除用户
- 3.2、修改用户口令和锁定状态
4、权限
- 4.1、角色
- 4.2、授权、撤销授权
5、总结
- 5.1、通过类比来理解概念
- 5.2、创建用户的一般步骤

对大多数人而言，熟练 Oracle 数据库是比较困难的。在工作中，我见过用了十多年 Oracle 却没弄清楚用户和 SCHEMA 区别的人；也多次遇到工作好几年却不能独自装好 Oracle 服务端和客户端的人，但工作数年却装不好 SQL Server 的情况从没见过，我想这或许能在一定程度上说明问题！

在我刚刚接触 Oracle 的时候，（性格使然）非常想把表空间、SCHEMA 等概念理解透彻，于是查阅了大量帖子（当时工资极低，不舍得花钱买书），有些讲得极其晦涩，有些讲得甚至有错，结果耗费了我大量时间和精力，理解上也还是稀里糊涂的。

1、表空间

表空间是 Oracle 数据库的逻辑划分，每个 Oracle 数据库至少有一个表空间。数据库中的表、索引等所有的数据库对象都存放在指定的表空间中，但表空间主要是为了存放表，所以被称作表空间。一个表空间只能属于一个数据库，表空间还能更进一步分成更细的逻辑存储单位（段、区、块）。实际上表空间是由一个或多个数据文件组成的，所以 Oracle 的数据逻辑上是保存在表空间里的，而物理上却是保存在数据文件中的。

每个 Oracle 数据库都有且只有一个 SYSTEM 表空间，SYSTEM 表空间在创建数据库的时候被创建，用于存放数据字典。一般数据库对象（如表、索引等）不应存放在 SYSTEM 表空间中，而应存放到非 SYSTEM 表空间中去。每个 Oracle 数据库可以有多个非 SYSTEM 表空间，非 SYSTEM 表空间可以控制分配给每个用户的空间，即配额。

总的来说，表空间的作用就是管理空间资源的。可根据用途将表空间分为 3 类：

永久表空间：用于存放表、索引、视图、存储过程等固有的对象和数据。
临时表空间：用于存放短期活动的数据，如排序数据等，不包含任何永久对象。
撤销表空间：用于存放撤销信息，如数据在修改前就会被放入撤销表空间。撤销表空间是自动管理的，撤销表空间中的段、区都是自动分配和自动释放的，我们只需要保证磁盘空间有足够的大小即可。

1.1、创建、修改及删除表空间

创建表空间：创建表空间的语法选项多达数十个，详情请参考《Oracle Database SQL Reference》。对普通开发人员而言，如果把所有选项都写出来，那实在是太麻烦了，好在 Oracle 提供了大量的默认选项，以便简单、快速的创建表空间。

示例一（创建普通永久表空间）：

CREATE TABLESPACE myspace -- 指定表空间名称

  DATAFILE 'D:\ts1.dbf' SIZE 64M,'D:\ts2.dbf' -- 指定数据文件列表，需同时指定初始大小

  AUTOEXTEND ON NEXT 8M -- 指定表空间下一次增长的大小

  MAXSIZE 1024M -- 指定表空间最大大小

  EXTENT MANAGEMENT LOCAL -- 指定为本地管理表空间

  SEGMENT SPACE MANAGEMENT AUTO  -- 指定为段自动管理

示例二（应该是最简洁的创建表空间写法，数据文件被存储到当前数据库默认的数据文件存放目录下）：

CREATE TABLESPACE ts DATAFILE 'ts.dbf' SIZE 20M;

修改表空间：一般修改表空间都是因为空间不足，需要扩充，下面将给出三种扩充表空间的方法示例。

示例一（增加数据文件）：

ALTER TABLESPACE myspace ADD DATAFILE 'D:\ts3.dbf' SIZE 128M;

示例二（修改数据文件的大小，即修改表空间的大小）：

ALTER DATABASE DATAFILE 'D:\ts3.dbf' RESIZE 256M;

示例三（允许数据文件自动增长，及允许表空间自动增长）：

ALTER DATABASE DATAFILE 'D:\ts3.dbf' AUTOEXTEND ON NEXT 8M MAXSIZE 512M;

可通过 SQL 修改表空间的状态，示例：

ALTER TABLESPACE myspace READ ONLY;  -- 将 myspace 表空间修改为只读

ALTER TABLESPACE myspace READ WRITE; -- 将 myspace 表空间修改为读写

ALTER TABLESPACE myspace OFFLINE;    -- 将 myspace 表空间修改为脱机状态

ALTER TABLESPACE myspace ONLINE;     -- 将 myspace 表空间修改为联机状态

删除表空间：一般来说，除 SYSTEM 表空间外，其它表空间都是可以删除的。

语法：

DROP TABLESPACE tablespace_name

  [INCLUDING CONTENTS [{AND DATAFILES/KEEP DATAFILES}] [CASCADE CONSTRAINTS]];

示例：

-- 删除表空间（默认只在控制文件和数据字典中删除与表空间和数据文件相关的信息）

DROP TABLESPACE myspace;

-- 删除表空间、对象及数据文件

DROP TABLESPACE myspace INCLUDING CONTENTS AND DATAFILES;

-- 删除表空间、对象、数据文件及约束

DROP TABLESPACE myspace INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

1.2、为对象指定表空间

可以为 Oracle 中的部分对象（如用户、表、索引）指定存放表空间，如果不特别指定，就会被存储到默认表空间，事实上默认表空间也是可以修改的。

-- 查询当前数据库默认永久表空间和默认临时表空间

SELECT T.PROPERTY_NAME, T.PROPERTY_VALUE

FROM SYS.DATABASE_PROPERTIES T

WHERE t.property_name IN ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

ALTER DATABASE DEFAULT TABLESPACE myspace;      -- 修改当前数据库默认表空间为 myspace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE ts; -- 修改当前数据库默认临时表空间为 ts

示例一（为用户指定表空间）：

CREATE USER fox IDENTIFIED BY tiger DEFAULT TABLESPACE myspace; -- 创建用户时指定默认表空间

ALTER USER fox DEFAULT TABLESPACE ts; -- 将用户的默认表空间修改为 ts

示例二（为表指定表空间）：

CREATE TABLE t_case(cid NUMBER(9),cname VARCHAR2(50)) TABLESPACE myspace; -- 创建表时指定表空间

ALTER TABLE t_case MOVE TABLESPACE ts; -- 将表移动到 ts 表空间

示例三（为索引指定表空间）：

CREATE INDEX uk_name ON t_case(cname) TABLESPACE myspace; -- 创建索引时指定表空间

ALTER INDEX uk_name REBUILD TABLESPACE ts; -- 将索引移动到 ts 表空间

2、SCHEMA

SCHEMA（模式/方案）既无法直接创建，也无法直接删除，如果不注重理解，甚至都无法感知它的存在；因此对没有数据库经验的人来说，的确不太好理解。demo.t_staff、owner.object 之类的写法是非常常见的，不知道大家是否真的理解；我在接触数据库之后很长一段时间都以为这里的 demo 和 owner 指的是用户名，但实际上这里的 demo 和 owner 都是指 SCHEMA 名。

SCHEMA 是数据库中用户所拥有对象的集合，为了区分各个集合，我们需要给每个集合都取一个名字。常见的数据库如 Oracle、SQL Server 等，都会在创建用户后给用户一个缺省的 SCHEMA，且名称与用户名相同。Oracle 中虽然有 CREATE SCHEMA 语句，但是它并不是用来创建 SCHEMA 的，要创建 SCHEMA 只能通过创建用户的方式；这样一来用户与 SCHEMA 就一一对应了，所以即便分不清用户名和 SCHEMA 名一般也不会有问题。

访问数据库对象的时候，如果没有指明该对象属于哪一个 SCHEMA，系统就会自动在对象名前面加上缺省的 SCHEMA 名，也就是说对象的完整名称为 SCHEMA.object。类似的如果创建对象时不指定该对象的 SCHEMA，系统也会为该对象指定用户的缺省 SCHEMA。不同的 SCHEMA 之间没有直接的关系，它们的表可以同名，也可以互相引用（前提是必须有权限），如果没有操作别的 SCHEMA 的根权，那么用户就只能操作它自己的 SCHEMA 中的对象。

3、用户

要想访问某个数据库，就得先登录那个数据库，而登录必然需要用户名和密码，这很好理解。要想访问某个数据库对象，也得先知道这个对象所属用户，尽管访问对象时实际是用 SCHEMA 来限定对象名。

3.1、创建用户、删除用户

创建用户：创建用户的语法选项也有很多，详情请参考《Oracle Database SQL Reference》。同样 Oracle 也提供了大量的默认选项，以便简单、快速的创建用户。示例：

-- 创建一个用户 user1，并设置密码为 user1。注意：密码不能太简单，否则不能创建

CREATE USER user1 IDENTIFIED BY user1;

-- 创建一个用户 user2，并指定默认表空间为 myspace，同时指定临时表空间为 temp，一般不需要建立临时表空间和撤销表空间，除非要把它们转移到其它磁盘以提高性能

CREATE USER user2 IDENTIFIED BY user2

  DEFAULT TABLESPACE myspace TEMPORARY TABLESPACE temp;

删除用户：当某些用户不再被需要时，可以将其删除。示例：

DROP USER user1;         -- 删除 user1 用户

DROP USER user2 CASCADE; -- 删除 user2 用户，并删除 user2 拥有数据库对象

3.2、修改用户口令和锁定状态

如果你忘记了某个用户的密码，可以通过 DBA 用户来修改它的密码。示例：

ALTER USER fox IDENTIFIED BY fox123; -- 把 fox 用户的密码改为 fox123

ALTER USER fox PASSWORD EXPIRE; -- 强制 fox 用户的口令过期

ALTER USER fox ACCOUNT LOCK;   -- 锁定 fox 用户

ALTER USER fox ACCOUNT UNLOCK; -- 给 fox 用户解锁

ALTER USER fox QUOTA 128M ON users; -- 设置 fox 用户在 users 表空间的配额

4、权限

权限是相对用户来说的，权限允许用户执行特定的数据库动作或访问特定的数据库对象。如果你明知某个用户拥有某张表，结果你访问的时候却报“表或视图不存在”，那说明你没有访问这个表的权限。但在 Oracle 里没权限的操作并不总是报这个错，有可能是别的错或不报错，所以如果对 Oracle 的权限没有一个系统性的认识，有时候是很难甄别某些错误的。

Oracle 10g 中的权限很多，有 100 多种，但可清晰的分为两类：

系统权限：访问数据库的能力，允许用户执行特定的数据库动作，如连接实例、创建表、创建视图、创建触发器等。
对象权限：维护数据库的能力，允许用户操作特定的数据库对象，如对数据的增删改查、执行过程、修改表结构等。每种对象的权限都不相同。对象的所有者拥有对象的所有权限。对象的所有者可以将自己的对象权限赋予其它用户或角色。

4.1、角色

角色就是一组相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理，同时也实现了动态权限管理。角色主要分为两类，预定义角色和自定义角色。Oracle 10g 提供了数十个预定义角色，常见的角色有 CONNECT、RESOURCE、DBA、PUBLIC 等。鉴于普通开发中，程序员无需对角色深入了解，这里仅给出几个自定义角色的简单示例：

CREATE ROLE myrole NOT IDENTIFIED;                           -- 创建角色，不需要验证

CREATE ROLE role1 IDENTIFIED BY role1;                       -- 创建角色，要验证

GRANT INSERT,DELETE,UPDATE,SELECT ON demo.t_staff TO myrole; -- 为角色授权

GRANT myrole TO demo;                                        -- 为用户分配角色

DROP ROLE myrole;                                            -- 删除角色

4.2、授权、撤销授权

授权：既可以通过角色为用户授权，也可以直接授予用户某个特定权限。Oracle 对权限管理比较严格，普通用户之间默认是不能互相访问的，需要互相授权。

示例一（通过角色为用户授权，当用户拥有某个角色后，相应的也就具备了该角色所包含的所有权限）：

GRANT CONNECT,RESOURCE,DBA TO fox; -- 授予 fox 用户 CONNECT,RESOURCE,DBA 三个角色

ALTER USER fox DEFAULT ROLE CONNECT; -- 设置用户的默认角色

ALTER USER fox DEFAULT ROLE ALL EXCEPT DBA; -- 除了这个角色其它的角色都授予

ALTER USER fox DEFAULT ROLE NONE;

示例二（直接授予用户权限，拥有下面三个授权之后，用户才能对表进行 CRUD 操作）：

GRANT CREATE SESSION TO fox; -- 会话权限，有这个权限之后用户才能登录

GRANT CREATE TABLE TO fox; -- 建表权限，有这个权限之后用户才能创建表

GRANT UNLIMITED TABLESPACE TO fox; -- 不受限表空间权限，可以对数据库中所有表空间进行操作

示例三（进行 DML 操作的对象权限，对象权限可以控制到列，但查询和删除不能控制到列）：

GRANT SELECT ON demo.t_staff TO fox; -- 把对 t_staff 表的查询权限授予 fox

GRANT ALL ON demo.t_staff TO fox; -- 把对 t_staff 表的增删改查、索引、外键等权限授予 fox

GRANT UPDATE(staff_name) ON demo.t_staff TO fox; -- 把对 t_staff 表中 staff_name 列的修改权限授予 fox

GRANT INSERT(staff_name) ON demo.t_staff TO fox; -- 把对 t_staff 表中 staff_name 列的插入权限授予 fox

一些常见的授权语句补充：

GRANT CREATE SESSION TO fox; -- 授予 fox 用户创建 session 的权限，即登录权限

GRANT UNLIMITED TABLESPACE TO fox; --  授予 fox 用户使用表空间的权限

GRANT CREATE TABLE TO fox; -- 授予 fox 用户创建表的权限

GRANT DROP ANY TABLE TO fox; -- 授予 fox 用户删除表的权限，ANY 表示用户在所有模式中都有该权限

GRANT INSERT ANY TABLE TO fox; -- 授予 fox 用户插入表数据的权限

GRANT UPDATE ANY TABLE TO fox; -- 授予 fox 用户修改表数据的权限

GRANT SELECT ANY TABLE TO fox; -- 授予 fox 用户查询表的权限

撤销授权：与授权语法结构相同，只需要将 GRANT 换成、TO 换成 FROM 即可。另外，撤销对象权限的用户必须是要撤销对象权限的对象的拥有者。

示例：

REVOKE CREATE SESSION FROM fox;

REVOKE CREATE TABLE FROM fox;

REVOKE SELECT ON demo.t_staff FROM fox;

REVOKE ALL ON demo.t_staff FROM fox;

另外，可以在授予对象权限语句后面加上WITH GRANT OPTION，这样将权限授予某用户时，该用户也拥有了授予其他用户对象权限的功能。这种写法被称之为及联授予，撤销权限的时候也会及联删除，换成WITH ADMIN OPTION就不会级联删除了。

5、总结

本文主要讲述了 Oracle 中最基本的 4 个概念（也可以说是 5 个，因为角色也是一个），并重点讲述了创建及删除表空间和用户。其实这些操作对普通开发人员来说并不常用，但必须了解，因为这些是数据库基础中的基础。

Oracle 中的理论概念可以很深入，尤其是 Oracle 的体系结构，相当复杂，不过我认为对普通开发人员来讲完全没必要搞懂 Oracle 的体系结构。事实上像体系结构里的 SGA、PGA 等概念我自己也是浅尝辄止、不求甚解，毕竟咱不是研究 Oracle 的。

5.1、通过类比来理解概念

起初，我也觉得表空间、模式等概念不太好理解。等我终于理解的差不多了，我发现可以类比现实中的事物来理解：如果说程序员(Programer)是管道工，那 Oracle 数据库(Database)就是小区，而表空间(Tablespace)就是小区内的每幢房子，模式(Schema)就是小区内的每户人家，用户(User)就是房奴或租房者，权限(Privilege)就是房子的钥匙，角色(Role)就是物业或房东之类的中介。

于是乎：小区里有一或多幢房子，正如数据库里有一或多个表空间；一幢房子可以住多个人，一个人也可以拥有多套房子，正如一个表空间可以被多个用户使用，一个用户也可以使用多个表空间；某个人一旦成了房奴或交了房租，他就有了住所——家，比如他叫张三，那他的房子就被认为是张三的家，正如一旦创建了用户，就有了与用户同名的模式；房奴即便买了房子也得有钥匙才能回家，管道工要想去他家就得先得到房奴或中介的许可，正如用户要访问数据库中某个模式下的对象得先登录数据库，其它用户要访问就得先取得授权；

5.2、创建用户的一般步骤

实际项目开发中，我一般都是先创建表空间，然后创建用户并指定密码和默认表空间，最后再授予用户必需的权限。表空间这块儿我一般只是粗略的评估下应用的数据情况，设置下表空间的初始大小和增长策略，专业的 DBA 往往还会仔细考虑表空间配额。我一般会授予用户比较大权限（其实这并不好），专业的 DBA 还会对用户权限做更加精细化的管理。

下面给出创建用户的一般步骤：

1、登录管理员。首先需要以 sysdba 的身份登录一个已有用户，或者以普通身份登录一个拥有 DBA 角色的用户（估计是系统权限CREATE USER的作用，未验证），才有权限继续进行后面的操作。

2、创建表空间。指定表空间的名称为demo_space，数据文件为D:\demo_space.dbf，初始大小为 64 MB，下一次增长 8 MB，最大大小为 1024 MB，区管理为本地管理，主要是为提高数据库分配空间的效率。

CREATE TABLESPACE demo_space DATAFILE 'D:\demo_space.dbf'

SIZE 64m AUTOEXTEND ON NEXT 8m MAXSIZE 1024m EXTENT MANAGEMENT LOCAL;

3、创建新用户。执行用户为demo_user，用户密码为demo_pwd，默认表空间为demo_space。

CREATE USER demo_user IDENTIFIED BY demo_pwd DEFAULT TABLESPACE demo_space;

4、授予用户权限。

GRANT CONNECT,RESOURCE TO demo_user;

彻底删除用户及用户数据和表空间及数据文件

DROP USER demo_user CASCADE;

DROP TABLESPACE demo_space INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

本文链接：http://www.cnblogs.com/hanzongze/p/oracle-basicconcept.html

版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！

巴特西