Oracle_表空间、数据库对象

1、Oracle数据库对象

数据库对象是数据库的组成部分，常常用 CREATE 命令进行创建，可以使用 ALTER 命令修改，用 DROP 执行删除操作。前面已经接触过的数据库对象有表、用户等。

• 同义词：就是给数据库对象一个别名。
• 序列：Oracle 中实现增长的对象。
•  视图：预定义的查询，作为表一样的查询使用，是一张虚拟表。
• 索引：对数据库表中的某些列进行排序，便于提高查询效率。

2、同义词

同义词（Synonym）是数据库对象的一个别名，Oracle 可以为表、视图、序列、过程、函数、程序包等指定一个别名。同义词有两种类型：

• 私有同义词：拥有 CREATE SYNONYM 权限的用户（包括非管理员用户）即可创建私有同义词，创建的私有同义词只能由当前用户使用。
• 公有同义词：系统管理员可以创建公有同义词，公有同义词可以被所有用户访问。创建同义词的语法是：

语法结构：同义词

CREATE [OR REPLACE] [PUBLIC] SYSNONYM [schema.]synonym_name FOR [schema.]object_name

语法解析：

1. CREATE [OR REPLACE：]表示在创建同义词时，如果该同义词已经存在，那么就用新创建的同义词代替旧同义词。
2. PULBIC：创建公有同义词时使用的关键字，一般情况下不需要创建公有同义词。③ Oracle 中一个用户可以创建表、视图等多种数据库对象，一个用户和该用户下的所有数据库对象的集合称为 Schema（中文称为模式或者方案），用户名就是 Schema名。一个数据库对象的全称是：用户名.对象名，即 schema.object_name。

如果一个用户有权限访问其他用户对象时，就可以使用全称来访问。比如：

代码演示：System 用户访问 Scott 用户的 Emp 表

代码解析：
管理员用户可以访问任何用户的数据库对象，SYSTEM 用户访问 SCOTT 用户的 EMP表时，必须使用 SCOTT.EMP。

案例 1：创建一个用户 XiaoMei，该用户拥有 CONNECT 角色和 RESOURCE 角色。为 SCOTT用户的 EMP 表创建同义词，并通过同义词访问该 EMP 表。

代码演示：创建同义词并访问

SQL> CREATE USER XiaoMei IDENTIFIED BY XiaoMei;
User created.
SQL> GRANT CONNECT TO XiaoMei;
Grant succeeded.
SQL> GRANT RESOURCE TO XiaoMei;
Grant succeeded.
SQL>  GRANT CREATE SYNONYM TO XiaoMei;
Grant succeeded.
SQL> conn XiaoMei/XiaoMei
Connected.
SQL> CREATE SYNONYM MyEmp FOR SCOTT.EMP;
Synonym created.
SQL> SELECT * FROM MYEMP;
SELECT * FROM MYEMP
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn scott/tiger
Connected.
SQL>  GRANT ALL ON EMP TO XiaoMei;
Grant succeeded.
SQL> CONNECT XiaoMei/XiaoMei
Connected.
SQL>  SELECT ENAME,JOB,SAL FROM MyEmp WHERE SAL>2000;

ENAME      JOB              SAL
---------- --------- ----------
JONES      MANAGER         2975
BLAKE      MANAGER         2850
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
FORD       ANALYST         3000

6 rows selected.

代码解析：

• 在管理员用户下创建用户 XiaoMei，对用户 XiaoMei 授予 CONNECT 和 RESOURCE 角色。为了 XiaoMei 能够创建序列，必须授予系统权限：CREATE SYNONYM。
• 在 XiaoMei 用户下，为 SCOTT.EMP 创建私有同义词 MyEmp，同义词 MyEmp 只能在XiaoMei 用户下使用。访问 MyEmp 就是访问 SCOTT.EMP 对象。
• 访问 MyEmp 对象出错：对象不存在。因为 XiaoMei 如果访问 MyEmp，就相当于访问 SCOTT.EMP 对象，那么 SCOTT 用户必须对 XiaoMei 授予相应的权限。④ SCOTT 用户下，把 EMP 表的所有权限（增删改查）授予 XiaoMei。
• 对 MyEmp 执行查询操作。MyEmp 就可以像在本地的表一样使用。

删除同义词使用的语法是：

语法结构：删除同义词

DROP [PUBLIC] SYNONYM [schema.]sysnonym_name

语法解析：

1. PUBLIC：删除公共同义词。
2. 同义词的删除只能被拥有同义词对象的用户或者管理员删除。
3. 此命令只能删除同义词，不能删除同义词下的源对象。

3. 序列

序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列，序列中的可以升序生成，也可以降序生成。创建序列的语法是：

语法结构：创建序列

CREATE SEQUENCE sequence_name
[START WITH num]
[INCREMENT BY increment]
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE num|NOCACHE]

语法解析：

1. START WITH：从某一个整数开始，升序默认值是 1，降序默认值是-1。
2. INCREMENT BY：增长数。如果是正数则升序生成，如果是负数则降序生成。升序默认值是 1，降序默认值是-1。
3. MAXVALUE：指最大值。
4. NOMAXVALUE：这是最大值的默认选项，升序的最大值是：1027，降序默认值是-1。
5. MINVALUE：指最小值。
6. NOMINVALUE：这是默认值选项，升序默认值是 1，降序默认值是-1026。
7. CYCLE：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最小值后，从最大值重新开始。
8. NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认 NOCYCLE。
9. CACHE：使用 CACHE 选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。Oracle默认会生产 20 个序列号。
10. NOCACHE：不预先在内存中生成序列号。

案例 2：创建一个从 1 开始，默认最大值，每次增长 1 的序列，要求 NOCYCLE，缓存中有 30 个预先分配好的序列号。

代码演示：生成序列号

使用 ALTER SEQUENCE 可以修改序列，在修改序列时有如下限制：
1. 不能修改序列的初始值。
2. 最小值不能大于当前值。
3. 最大值不能小于当前值。

使用 DROP SEQUENCE 命令可以删除一个序列对象。

代码演示：序列修改和删除

4、视图

视图（View）实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的 SELECT…FROM 即可。
视图具有以下优点：

1. 可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表。
2. 可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。
3. 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。
4. 从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。

语法结构：创建视图

CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name AS
SELECT查询
[WITH READ ONLY CONSTRAINT]

语法解析：

1. OR REPLACE：如果视图已经存在，则替换旧视图。
2. FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。
3. NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。
4. WITH READ ONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行 insert 操作），WITH READ ONLY 说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

案例 3：基于 EMP 表和 DEPT 表创建视图

代码解析：

• 对视图可以像表一样进行查询。该视图中隐藏了员工的工资。
• 删除视图可以使用“DROP VIEW 视图名称”，删除视图不会影响基表的数据。

5. 索引

创建索引就是对某些特定列中的数据排序，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，Oracle 会自动的引用该索引，先从索引表中查询出符合条件记录的 ROWID，由于 ROWID 是记录的物理地址，因此可以根据 ROWID 快速的定位到具体的记录，表中的数据非常多时，引用索引带来的查询效率非常可观。

• 如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该列创建索引。
• 当从很多行的表中查询少数行时，也要考虑创建索引。有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的 10%时，索引就非常有用。

Oracle 数据库会为表的主键和包含唯一约束的列自动创建索引。索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。

语法结构：创建索引

CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])

语法解析：

1. UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。
2. index_name：指定索引名。
3. tabl_name：指定要为哪个表创建索引。
4. column_name：指定要对哪个列创建索引。我们也可以对多列创建索引；这种索引称为组合索引。

案例 4：为 EMP 表的 ENAME 列创建唯一索引，为 EMP 表的工资列创建普通索引，把JOB 列先变为小写再创建索引。

代码解析：

• 第一句为 SCOTT.EMP 表的 ENAME 列创建唯一索引。
• 第二句为 SCOTT.EMP 表的 SAL 列创建索引。
• 第三句在查询中可能经常使用 job 的小写作为条件的表达式，因此创建索引时，可以先对JOB 列中的所有值转换为小写后创建索引，而这时需要使用 lower 函数，这种索引称为基于函数的索引。

在 select 语句查询时，Oracle 系统会自动为查询条件上的列应用索引。索引就是对某一列进行排序，因此在索引列上，重复值越少，索引的效果越明显。
Oracle 可以为一些列值重复非常多且值有限的列（比如性别列）上创建位图索引。

6. 表空间

在数据库系统中，存储空间是较为重要的资源，合理利用空间，不但能节省空间，还可以提高系统的效率和工作性能。Oracle 可以存放海量数据，所有数据都在数据文件中存储。而数据文件大小受操作系统限制，并且过大的数据文件对数据的存取性能影响非常大。同时Oracle 是跨平台的数据库，Oracle 数据可以轻松的在不同平台上移植，那么如何才能提供统一存取格式的大容量呢？Oracle 采用表空间来解决。

表空间只是一个逻辑概念，若干操作系统文件（文件可以不是很大）可以组成一个表空间。表空间统一管理空间中的数据文件，一个数据文件只能属于一个表空间。一个数据库空间由若干个表空间组成。如图所示：

Oracle 中所有的数据（包括系统数据），全部保存在表空间中，常见的表空间有：

• 系统表空间：存放系统数据，系统表空间在数据库创建时创建。表空间名称为SYSTEM。存放数据字典和视图以及数据库结构等重要系统数据信息，在运行时如果 SYSTEM 空间不足，对数据库影响会比较大，虽然在系统运行过程中可以通过命令扩充空间，但还是会影响数据库的性能，因此有必要在创建数据库时适当的把数据文件设置大一些。
• TEMP 表空间：临时表空间，安装数据库时创建，可以在运行时通过命令增大临时表空间。临时表空间的重要作用是数据排序。比如当用户执行了诸如 Order by 等命令后，服务器需要对所选取数据进行排序，如果数据很大，内存的排序区可能装不下太大数据，就需要把一些中间的排序结果写在硬盘的临时表空间中。
• 用户表自定义空间：用户可以通过 CREATE TABLESPACE 命令创建表空间。

创建表空间需要考虑数据库对分区（Extent，一个 Oracle 分区是数据库文件中一段连续的空间，Oracle 分区是 Oracle 管理中最小的单位）的管理，比如当一个表创建后先申请一个分区，在 Insert 执行过程中，如果分区数据已满，需要重新申请另外的分区。如果一个数据库中的分区大小不一，创建表空间时需要考虑一系列问题。因此在 Oracle8i 之后，创建表空间都推荐使用“本地管理表空间”，这种表空间中的分区是一个固定大小的值，创建表空间的语法是：

语法结构：创建表空间

CREATE TABLESPACE 空间名称
DATAFILE '文件名1' SIZE 数字M
[,'文件名2' SIZE 数字….]EXTENT MANAGEMENT LOCAL UNIFORM SIZE 数字M

语法解析：

1. 文件名包括完整路径和文件名，每个数据文件定义了文件的初始大小，初始大小一般以“M”为单位。一个表空间中可以有多个数据文件。
2. EXTENT MANAGEMENT LOCAL 指明表空间类型是：本地管理表空间。本地管理表空间要求 Oracle 中的数据分区（Extent）大小统一。
3. UNIFORM SIZE：指定每个分区的统一大小。

案例 5：创建一个表空间，包含两个数据文件大小分别是 10MB，5MB，要求 extent 的大小统一为 1M。

代码演示：创建表空间

CREATE TABLESPACE MYSPACE
  DATAFILE '/home/oracle/A.ORA' SIZE 10M
  AUTOEXTEND ON 
  NEXT 50M MAXSIZE 20480M 
  EXTENT MANAGEMENT LOCAL;

必须是管理员用户才能创建表空间，当表空间的空间不足时可以使用 ALTER TABLESPACE命令向表空间中追加数据文件扩充表空间。

代码演示：扩充表空间

ALTER TABLESPACE MYSPACE ADD DATAFILE '/home/oracle/B.ORA' SIZE 10M 

表空间可以在不使用时删除，使用 DROP TABLESPACE 命令。
 数据库的所有数据全部在某一表空间中存放，在创建用户时，可以为用户指定某一表空间，那么该用户下的所有数据库对象（比如表）默认都存储在该空间中。

代码演示：为某一用户指定默认表空间

CREATE USER ACONG IDENTIFIED BY ACONG DEFAULT TABLESPACE MYSPACE

在创建表时，表中数据存放在用户的默认表空间中，也可以通过 tablespace 子句为表指定表中数据存放在其他表空间中。

代码演示：为表指定表空间

CREATE TABLE SCORES
 (
  ID NUMBER ,
  TERM VARCHAR2(2),
  STUID VARCHAR2(7) NOT NULL,
  EXAMNO VARCHAR2(7) NOT NULL,
  WRITTENSCORE NUMBER(4,1) NOT NULL,
  LABSCORE NUMBER(4,1) NOT NULL
)
TABLESPACE MYSPACE

创建索引时也可以为索引指定表空间。

代码演示：为索引指定表空间

CREATE INDEX UQ_ID ON SCORES(ID) TABLESPACE MYSPACE;

表和索引一旦创建，表空间无法修改。