Oracle 11g_数据表对象（8）

1. 数据表概述

数据表(通常简称表)是Oracle数据库中主要的数据存储容器，表中的数据被组织成行和列。表中的每个列有一个名称，并且每个列都具有一个指定的数据类型和大小，如 VARCHAR(30), TIMESTAMP(6) 或 NUMBER(12)。

在Oracle中，有多种类型的表。不同类型的表各有一些特殊的属性，适用于保存某种特殊的数据，进行某些特殊操作，即在某些方面可能比其他类型的表性能更好，如处理速度更快，占用磁盘空间更少。

表一般指的是一个关系表，也可以生成对象表或临时表。其中，对象表是通过用户定义的数据类型生成的，临时表用于存储专用于某个事务或会话的临时数据。

2. 创建数据表

数据库中的每一个表被一个模式(或用户)所拥有，因此表是一种典型的模式对象。在创建数据表时，Oracle将在一个指定的表空间中为其分配存储空间。最初创建的表是一个空的逻辑存储结构，其中不包含任何数据记录。

2.1 数据表的逻辑结构

表是最常见的一种组织数据的方式，一张表一般都具有多个列(即多个字段)。每个字段都具有特定的属性，包括字段名，数据类型，字段长度，约束，默认值等，这些属性在创建表时被确定。从用户的角度来看，数据库中数据的逻辑结构是一张二维的平面表，在表中通过行和列来组织数据。在表中每一行存放一条信息，通常将表中的一行称为一条记录。

Oracle提供了多种内置的列的数据类型，常用到的包括字符类型，数值类型，日期类型，LOB类型于ROWID类型。除了这些类型之外，用户还可以定义数据类型。

（1）字符数据类型

字符数据类型用于声明包含字母，数字数据的字段。对字符数据再进行细分可分为定长字符串和变长字符串两种，它们分别对应着DCHAR数据类型和VARCHAR2数据类型。

** CHAR数据类型。CHAR数据类型用于存储固定长度的字符串。一旦定义了CHAR类型的列，该列就会一直保持声明时所规定的数据长度大小。当为该列的某个单元格(行与列的交叉处就是单元格)赋予长度较短的数值后，空余部分Oracle会用空格自动填充；如果字段保存的字符长度大于规定的长度，则Oracle会产生错误信息。CHAR类型的长度范围为1~2000个字节。

** VARCHAR2数据类型。VARCHAR2数据类型与CHAR类型相似，都用于存储字符串数据。但VARCHAR2类型的字段用于存储变长，而非固定长度的字符串。将字段定义为VARCHAR2数据类型时，该字段的长度将根据实际字符数据的长度自动调整；即如果该列的字符串长度小于定义时的长度，系统不会使用空格填充，而是保留实际的字符串长度。因此，在大多数情况下，都会使用VARCHAR2类型替换CHAR类型。

（2）数值数据类型

数值数据类型的字段用于存储带符号的整数或浮点数。Oracle中的NUMBER数据类型具有精度(PERCISION)和范围(SCALE)两个参数。精度(PERCISION)指定所有数字位的个数，范围(SCALE)指定小数的位数，这两个参数都是可选的。如果插入字段的数据超过指定的位数，Oracle将自动进行四舍五入。例如，字段的数据类型为NUMBER(5,2)，如果插入的数据为3.1415926，则实际上字段中保存的数据为3.14.

（3）日期数据类型

Oracle提供的日期时间数据类型是DATE，它可以存储日期和时间的组合数据。用DATE数据类型存储日期时间比使用字符数据类型进行存储更简单，并且可以借助于Oracle提供的日期时间函数方便处理数据。

在Oracle中，可以使用不同的方法建立日期值。其中最常用的获取日期值的方法是使用SYSDATE函数，调用该函数可以获取当前系统的日期值。除此之外，还可以使用TO_DATE函数将数值或字符串转换为DATE类型。Oracle默认的日期和时间格式由初始化参数NLS_DATE_FORMAT指定，一般为DD-MM-YY。

（4）LOB数据类型

LOB数据类型用于大型的，未被结构化的数据，例如二进制文件，图片文件和其他类型的外部文件。LOB类型的数据可以直接存储在数据库内部，也可以将数据存储在外部文件中，而将指向数据的指针存储在数据库中。LOB数据类型分为BLOB，CLOB 和 BFILE 等3种。

*** BLOB类型。BLOB类型用于存储二进制对象。典型的BLOB存储对象可以包括图像，音频文件，视频文件等。在BLOB类型的字段中能够存储最大为128MB字节的二进制对象。

*** CLOB类型。CLOB类型用于存储字符格式的大型对象，CLOB类型的字段能够存储最大为128MB的对象。Oracle首先把数据转换为Unicode格式的编码，然后再将它存储在数据库中。

*** BFILE类型。BFILE类型用于存储二进制格式的文件。在BFILE类型的字段中可以将最大为128MB的二进制文件作为操作系统文件存储在数据库外部，文件的大小不能超过操作系统的限制；BFILE类型的字段中仅保存二进制文件的指针，并且BFILE字段是只读的，不能通过数据库对其中的数据进行修改。

（5）ROWID数据类型

ROWID数据类型被称为"伪列类型"，用于在Oracle内部保存表中的每条记录的物理地址。在Oracle内部通过ROWID来定位所需记录。由于ROWID实际上保存的是数据记录的物理地址，因此通过ROWID来访问数据记录可以获得最快的访问速度。为了便于使用，Oracle自动为每一个表建立一个名称为ROWID的字段，可以对这个字段进行查询，更新和删除等操作，设置利用ROWID来访问表中的记录以获得最快的操作速度。

注意： 由于ROWID字段是隐式的，用户检索表时不会看到该字段。因此，如果要使用ROWID字段，则必须显式地指定其名称。

2.2 创建一个数据表

创建表通常使用CREATE TABLE的语句。如果用户在自己的模式中创建一个表，则用户必须具有CREATE TABLE系统权限。如果要在其他用户模式中创建表，则必须具有CREATE ANY TABLE的系统权限。此外，用户还必须在指定的表空间中设置一定的配额存储空间。

*** 创建一个学生档案信息表students，该表包括学号，姓名，性别，年龄，系别编号，班级编号和建档日期等信息，代码：

SQL> create table students(
stuno number(10) not null, --学号
stuname varchar2(8),	-- 姓名
sex char(2),	-- 性别
age int,	-- 年龄
departno varchar2(2) not null,	-- 系别编号
classno varchar2(4) not null,	-- 班级编号
regdate date default sysdate	-- 建档日期
);
表已创建。

用户在创建表后，可以通过DESCRIBE命令查看表的描述，示例如下：

SQL> describe students;

如果用户要在其他模式中创建表，则必须在表名称前加上某个模式的名称，示例如下：

用户system在scott模式下创建students表,其示例代码如下。

create table scott.students(...);

另外，还可以在CREATE TABLE语句中使用嵌套子查询，基于已经存在的表或视图来创建新表，而不需要为新表定义字段。在子查询中也可以引用一个或多个表(或视图)，查询结果集中包含的字段即为新表中定义的字段，并且查询到的记录也会被添加到新表中。

*** 使用CREATE TABLE AS SELECT 语句创建students表的一个副本，代码及运行结果：

SQL> create table students_2 
	as select * from students;

表已创建。

当使用CREATE TABLE AS SELECT 语句创建表时，Oracle将通过从students表中复制列来建立表。在创建表后，Oracle就会使用从SELECT语句中返回的行来填充新表。

2.3 数据表的特性

在Oracle中创建表时，表的特性将决定系统如何创建表，如何在磁盘上存储表，以及·表创建后使用何种最终执行方式等。

（1）存储参数

当用户在Oracle中创建表时，Oracle允许用户指定该表如何使用磁盘上的存储空间。

如果仅为表指定了表空间，而没有设置存储参数，则该表将采用其所属表空间的默认存储参数。然而，表空间的默认存储参数不一定对表空间中的每一个表都适用，因此，当表所需要的存储参数与表空间的默认存储参数不匹配时，需要用户在创建表时显式指定存储参数以替换表空间的默认的存储参数。

在创建表时，可以通过使用STORAGE子句来设置存储参数，这样可以控制表中盘区的分配管理方式。

对于本地化管理的表空间而言，如果指定盘区的管理方式为AUTOALLOCATE(即自动化管理)，则可以在STORAGE子句中指定INITIAL, NEXT, 和MINEXTENTS这3个存储参数，Oracle将根据这3个存储参数的值为表分配数据段初始化盘区的大小，以后盘区的分配将由Oracle自动管理。如果指定的盘区管理方式为UNIFORM（即等同大小管理），这时不能为表指定任何STORAGE子句，盘区的大小将是统一大小。

参数NEXT用于指定为存储表中的数据分配的第二个盘区大小。该参数在字典管理的表空间中起作用，而在本地化管理的表空间中不再起作用，因为随后分配的盘区将由Oracle自动决定其大小。参数MINEXTENTS用于指定允许为表中的数据所分配的最小盘区数目，同样，在本地化管理的表空间中该参数不再其作用。

因此，在存储参数中，主要是设置INITIAL参数。该参数用于为表指定分配的第一个盘区大小，以KB或MB为单位。

当为已知大小的数据建立表时，可以将INITIAL设置为一个能容纳所有数据的数值，这样可以将表中所有数据存储在一个盘区从而避免产生碎片。

**** 创建一个用于存储学生档案信息的student_3数据表(该表的结构与前面的students表相同)，并通过STORAGE子句中的INITIAL存储参数为该表分配第一个盘区的大小，代码：

SQL> create table student_3(
stuno number(10) not null,	-- 学号
stuname varchar2(8),		-- 姓名
sex char(2),				-- 性别
age int,				-- 年龄
departno varchar2(2) not null,	-- 系别编号
classno varchar2(4) not null,	-- 班级编号
regdate date default sysdate	-- 建档日期
)tablespace tbsp_1			-- 表空间
storage(initial 256k);		-- 指定为该表分配的第一个盘区的大小

表已创建。

如果想查看student_3表的存储参数情况，可以通过查询user_tables来实现。

**** 在user_tables数据字典中查询表students_3的第一个盘区的大小，代码如下：
SQL> select initial_extent
	from user_tables
	where table_name='STUDENTS_3';

（2）数据块管理参数

对于一般不带有LOB类型的数据表而言，一个数据块可以存放表的多行记录，用户可以设置的数据块管理参数主要分为以下两类：

*** PCTFREE和PCTUSED。
这两个参数用于控制数据块中空闲空间的使用方法。对于本地化管理的表空间而言，如果使用SEGMENT SPACE MANAGEMENT子句设置段的管理方式为AUTO(自动)，则Oracle会对数据库的空闲空间进行自动管理。对于这种情况，不需要用户设置数据块管理参数PCTFREE和PCTUSED。

如果表空间的段管理方式为SEGMENT SPACE MANAGEMENT MANUAL(手动管理)，则用户可以通过设置PCTFREE与PCTUSED参数对数据块中的空闲手动管理。其中，PCTFREE用于指定数据库中必须保留的最小空间比例，当数据库块达到PCTFREE参数的限制后，该数据块将被标记为不可用，默认值为10.例如，如果在CREATE TABLE语句中指定PCTFREE为30,则说明对于该表的数据段，系统将会保留30%的空闲空间，这些空闲空间将用于保存更新记录时增加的数据。很显然，PCTFREE参数值越小，为现有数据行更新所预留的空间越少。如果PCTFREE参数值设置得过高，则浪费磁盘空间，如果PCTFREE参数值设置得过低，则可能会导致由于一个数据块小到无法容纳一行记录而产生迁移记录和链接记录。

参数PCTUSED用于设置数据块是否可用的界限，换言之，为了使数据块能够再次使用，已经占用的存储空间必须低于PCTUSED设置得比例。

说明： 为表设置PCTUSED与PCTUSED参数时，PCTFREE与PCTUSED两个参数值的和必须等于或小于100。一般而言，两个参数的和与100相差越大，存储效率就越高。

设置数据块的PCTFREE和PCTUSED时，用户需要根据数据库的具体应用情况来做出决定。下面是设置PCTUSED和PCTFREE参数的几种情况。

**** 实际应用中，当使用UPDATE操作较多，并且更新操作会增加记录的大小时，可以将PCTFREE值设置得大一些，这样当记录变大时，记录仍然能够保存在原数据块中；而将PCTUSED值设置得比较小，这样在频繁地进行更新操作时，能够减少由于数据块在可用与不可用状态之间反复切换而造成地系统开销。推荐设置PCTFREE为20,而PCTUSED为40.

**** 实际应用中，当使用INSERT和DELETE操作较多，并且UPDATE操作不会增加记录的大小时，可以将PCTFREE参数值设置得比较小，因为大部分更新操作不会增加记录得大小，而PCTUSED参数值设置得比较大，以便尽快重新利用被DELETE操作释放的存储空间。推荐设置参数值PCTFREE为5，而PCTUSED为60.

在CREATE TABLE语句中，可以通过PCTFREE和PCTUSED子句来设置相应的参数。

创建student_4数据表(该表的结构与前面创建的students表相同)，并设置其PCTFREE和PCTUSED参数值分别为20和40，代码及运行结果如下：

SQL> create table student_3(
stuno number(10) not null,	-- 学号
stuname varchar2(8),		-- 姓名
sex char(2),				-- 性别
age int,				-- 年龄
departno varchar2(2) not null,	-- 系别编号
classno varchar2(4) not null,	-- 班级编号
regdate date default sysdate	-- 建档日期
)tablespace tbsp_1			-- 表空间
storage(initial 256k);		-- 指定为该表分配的第一个盘区的大小
pctfree 20				-- 数据块管理参数
pctused 40;				-- 数据块管理参数

**** INITRANS参数
该参数用于指定一个数据块所允许的并发事务数目。当一个事务访问表中的一个数据块时，该事务会在数据块的头部保存一个条目，以标识该事务正在使用这个数据块。当该事务结束后，它所对应的条目将被删除。

在创建表时，Oracle会在表的每个数据块头部分分配可以存储INITRANS个事务条目的空间，这部分空间是永久的，只能用于存储事务条目。当数据库块的头部空间已经存储了INITRANS个事务条目后，如果还有其他事务要访问这个数据块，Oracle将在数据块的空闲空间中为事务分配空间，这部分空间是动态的。当事务结束后，这部分存储空间将被回收以存储其他数据。

能够访问一个数据块的事务总数由MAXTRANS参数决定，在Oracle 11g中，对于单个数据块而言，Oracle默认最大支持255个并发事务。但实际上，MAXTRANS参数已经被废弃。

---- 创建student_5表，并指定在数据块头部存放10个事务条目，代码及运行结果如下：

SQL> create table student_3(
stuno number(10) not null,	-- 学号
stuname varchar2(8),		-- 姓名
sex char(2),				-- 性别
age int,				-- 年龄
departno varchar2(2) not null,	-- 系别编号
classno varchar2(4) not null,	-- 班级编号
regdate date default sysdate	-- 建档日期
)tablespace tbsp_1			-- 表空间
storage(initial 256k);		-- 指定为该表分配的第一个盘区的大小
pctfree 20				
pctused 40				
initrans 10;				-- 数据块管理参数, 10个事务条目。

说明：
由于每个表的应用特性不同，所以应当为各个表分别设置不同的INITRANS参数。在设置INITRANS参数时，如果设置的INITRANS参数值较大，则事务条目将占用过多的存储空间，从而减少用来存储实际数据的存储空间。
只有当一个表有较多事务同时访问时，才应当为其设置较高的INITRANS参数值。

（3）重做日志参数

重做日志记录了数据库中的数据的改变情况，这样，如果发生故障导致数据不能从内存中写入到数据文件中时，就可以从重做日志中获取被操作的数据。这样就可以防止数据丢失，从而提高表中数据的可靠性。
当使用CREATE TABLE语句创建表时，如果使用了NOLOGGING子句，则对该表的创建，删除，修改等操作(即DDL操作)不会记录到日志中，但对该表进行DML操作(INSERT, UPDATE, DELETE)时，系统仍然会产生重做日志记录。
在创建表时，默认情况下使用LOGGING子句，这样对该表的所有操作(包括创建，删除，重命名等操作)都会被记录到重做日志中。
在决定是否使用NOLOGGING子句时，用户必须综合考虑所产生的收益和风险。
使用NOLOGGING子句时，可以节省重做日志文件的存储空间，并减少创建表所需要的时间。但如果没有在重做日志文件中记录对表的操作，可能会无法用数据库恢复操作来恢复丢失的数据。

**** 创建student_6表，并且在创建该表时使用NOLOGGING子句，使用户对该表的创建，删除，修改等操作不被记录到重做日志文件中，代码如下：

SQL> create table student_3(
stuno number(10) not null,	-- 学号
stuname varchar2(8),		-- 姓名
sex char(2),				-- 性别
age int,				-- 年龄
departno varchar2(2) not null,	-- 系别编号
classno varchar2(4) not null,	-- 班级编号
regdate date default sysdate	-- 建档日期
)tablespace tbsp_1			-- 表空间
storage(initial 256k);		-- 指定为该表分配的第一个盘区的大小
pctfree 20				
pctused 40				
initrans 10
nologging;				-- 对DDL操作不产生日志。

（4）缓存参数

当在Oracle中执行全表搜索时，读入缓存中的数据块将会存储在LRU列表最近最少使用的一端。这意味着如果进行查询操作，并且必须向缓存中存储数据时，就会将刚读入的数据块换出缓存。
在建立表时，可以使用CACHE子句改变这种行为，使得在使用CACHE子句建立的表中执行全表搜索时，将读入的数据块放置到LRU中最近最常用使用的一端。

这样，数据库缓存中利用LRU算法对缓存块进行换入，换出调度时，就不会将属于这个表的数据块立即换出，从而提高了针对该表的查询效率。
在创建表时默认使用NOCACHE子句。对于比较小且又经常查询的表，用户在创建表时指定CACHE子句，以便利用系统缓存来提高对该表的查询执行效率。

*** 在user_table数据字典中查询student_6表是否启用了缓存功能。

SQL> select table_name,cache
	from user_tables
	where table_name='STUDENTS_6';

3. 维护数据表

在创建表后，如果发现对表的定义有不满意的地方，还可以对表进行修改。这些修改操作包括增加或删除表中的字段，改变表的存储参数设置以及对表进行增加，删除和重命名等操作。
普通用户只能对自己模式中的表进行修改，如果要对任何模式中的表进行修改操作，则必须具有ALTER ANY TABLE系统权限。

3.1 增加和删除字段

在创建表后，可能需要根据应用需求的变化向表中增加或删除列，用户可以使用ALTER TABLE...ADD语句向表中添加新的字段。

在students_6表中增加一个province(省分)新字段，代码：
SQL> alter table students_6 add(province varchar2(10));

表已更改。

既然可以为数据表添加字段，自然也可以删除数据表中的指定字段，这可以通过ALTER TABLE...DROP语句来实现，但是不能删除表中所有的字段，也不能删除sys模式中的任何表的字段。如果仅需要删除一个字段，则必须在字段名前指定COLUMN关键字。

在students_6表中删除province(省分)字段，代码：

SQL> alter table students_6 drop column province;

表已更改。

如果要在一条语句中删除多个列，则需要将删除的字段名放在括号中，各个字段之间用逗号隔开，并且不能使用关键字COLUMN。

在students_6表中同时删除sex和age字段，代码：

SQL> alter table students_6 drop(sex,age);

表已更改。

说明：在删除字段时，系统将删除表中每条记录对应的字段值，同时释放所有占用的存储空间，并且不会影响到表中其他列的数据。如果要删除一个大型表中的字段，由于需要对每条记录进行处理，因此，删除操作可能会执行很长时间。

3.2 修改字段

除了在表中增加和删除字段外，还可以根据实际情况修改字段的有关属性，包括修改字段的数据类型的长度，数字列的精度，列的数据类型和列的默认值等。修改字段通常使用ALTER TABLE...MODIFY语句，其语法格式如下：

ALTER TABLE table_name MODIFY column_name column_property
* table_name: 表示要修改的列所在的表名称。
* column_name: 要修改列的名称。
* column_property: 要修改列的属性，包括数据类型的长度，数字列的精度，列的数据类型和列的默认值等。

需要注意的是：用户在修改字段时，并不可以随意修改。
通常情况下，把某种数据类型改变为兼容你个的数据类型时，只能把数据类型的长度从低向高改变，不能从高向低改变，否则会出现数据溢出的情况，影响原有数据的精度；
如果表中没有数据时，用户既可以把数据的长度随意更改。

*** 将students_6表中的departno字段的长度由2更改为4，代码：
SQL> alter table students_6 modify departno varchar2(4);

表已更改。

说明： 修改某个字段的默认值只对今后插入的操作起作用，对于先前已经插入的数据并不起作用。

3.3 重命名表

在创建表后，用户可以修改指定表的名称，但用户只能对自己模式中的表进行重命名。重命名表通常使用ALTER TABLE...RENAME语句，语法格式如下：

ALTER TABLE table_old_name RENAME TO table_new_name

对表进行重命名非常容易，但是影响却非常大，在对表的名称进行修改时，需要格外谨慎。虽然Oracle可以自动更新数据字典中的外键，约束定义以及表关系，但是它不能更新数据库中的存储过程，客户应用，以及依赖该对象的其他对象。

*** 将students_6表重命名为students_7，代码：
SQL> alter table students_6 to students_7;

表已更改。

3.4 改变表空间和存储参数

在创建表时，可以通过一些参数指定表的表空间，存储参数等，当然也可以不指定参数而使用默认值。在创建表之后，如果发现这些参数设置得不合理，管理员可以对其进行修改。

（1）修改表空间。

若要将一个"非分区"表移动到一个新的表空间，则可以使用ALTER TABLE...MOVE TABLESPACE语句。
将students_6表由tbsp_1表空间移动到tbsp_2表空间，代码：

SQL> alter table students_6 move tablespace tbsp_2;

表已更改。

说明：由于表空间对应的数据文件不同，所以在移动表空间时会将数据从物理上移动到另一个数据文件。

（2）修改存储参数

修改存储参数，主要是指修改数据块参数PCTFREE和PCTUSED，若改变了这两个参数值，则表中所有的数据块都将收到影响，而不论数据块是否已经使用。修改存储参数一般使用ALTER TABLE语句。

*** 使用ALTER TABLE语句重新设置students_6表的PCTFREE和PCTUSED参数，代码：
SQL> alter table students_6 pctfree 25 pctused 45;

表已更改。

3.5 删除表

数据表在创之后，根据实际情况，用户还可以将其删除。但需要注意的是，一般情况下用户只能删除自己模式中的表，如果要删除其他模式中的表，则必须具有DROP ANY TABLE系统权限。删除表通常使用DROP TABLE语句，其语法格式如下：

SQL> DROP TABLE table_name [CASCADE CONSTRAINTS];

参数table_name表时要删除表的名称。如果该表存在约束，关联的视图和触发器等，则必须使用CASCADE CONSTRAINTS这个可选的子句才能将其删除。

删除表与删除表中的数据不同，当使用DELETE语句进行删除操作时，删除的仅是表中的数据，该表的数据结构仍然存在于子数据库中；DROP TABLE语句删除表的定义时，不仅表中的数据将丢失，而且该表的定义信息(数据结构)也将从数据库中删除，用户就再也不可以向该表中添加数据了，因为该表对象在数据库中已经不存在了。

在删除一个表的结构时，通常Oracle会执行以下操作

（1）删除表中所有的数据。
（2）删除与该表相关的所有索引和触发器。
（3）如果有视图或PL/SQL过程依赖于该表，这些视图或PL/SQL过程将被置于不可用状态。
（4）从数据字典中删除该表的定义。
（5）回收为该表分配的存储空间。

DROP TABLE语句有一个可选子句CASCADE CONSTRAINTS。当使用该参数时，DROP TABLE不仅仅删除该表，而且所引用的这个表的视图，约束或触发器等也都被删除。

删除表students_5以及所有引用这个表的视图，约束或触发器等，代码：

SQL> drop table students_5 cascade constraints;

表已删除。

一般情况下，当某个表被删除之后，实际上它并没有被彻底删除(仅仅是在数据据字典中被除名)，而是把该表放到了回收站中(即它依然占用存储空间)，这样当用户需要还原该表时，就可以使用FLASHBACK TABLE语句(这是一种闪回技术)进行还原。

*** 利用Oracle 11g的闪回功能快速恢复被删除的表students_5,具体操作如下：

SQL> select * from students_5;
				**
select * from students_5
第一行出现错误：
ORA-00942:表或视图不存在

*** 从上面的查询结果可以看出该表已经被删除，那么用户就可以通过查询数据字典视图RECYCLEBIN来了解该表是否在回收站中，代码：
SQL> select object_name,original_name from recyclebin where original_name='STUDENTS_5';

**** 使用FLASHBACK TABLE语句恢复被删除的students_5表，代码：
SQL> flashback table students_5 to before drop;

闪回完成。

*** 这时候再通过SELECT语句查询表student_5，发现该表被恢复了，代码：
SQL> select * from students_5;

为选定行。

如果用户想在删除表时立即释放空间，并且不希望将其放回到回收站中，则可以在DROP TABLE语句中使用PURGE选项，这样该表就被彻底删除了。

3.6 修改表状态

Oracle 11g 推出了一个新特性，就是用户可以将表置于READ ONLY(只读)状态。处于该状态的表不能执行DML和某些DDL操作。在Oracle 11g之前，为了使某个表处于READ ONLY状态，只能通过将整个表空间或者数据库置于READ ONLY状态实现。

*** 将表students_5置于只读的READ ONLY状态，代码：
SQL> alter table students_5 read only;

表已更改。

接下来，可以在user_tables中查询该表的状态，表现在的状态是只读。处于READ ONLY状态的表，用户不能执行DML操作。

*** 把表students_5从tbsp_1表空间移动到tbsp_2表空间。代码：
SQL> alter table students_5 move tablespace tbsp_2;

表已更改。

另外，对于READ ONLY状态的表，用户还可以将其重置于可读写的状态READ WRITE状态。

SQL> alter table studentds_5 read write;

表已更改。

4. 数据完整性和约束性

数据库不仅仅是存储数据，它还必须保证所有存储数据的正确性，因为只有正确的数据才能提供有价值的信息。

如果数据不准确或不一致，那么该数据的完整性就可能受到破坏，从而给数据库本身的可靠性带来问题。为了维护数据库中数据的完整性，在创建表时常常需要定义一些约束。

约束可以限制列的取值范围，强制设定列的取值来自合理的范围等。

在Oracle 11g系统中，约束的类型包括非空约束，主键约束，唯一性约束，外键约束，检查约束和默认约束。

说明： 对约束的定义既可以在CREATE TABLE语句中进行。也可以在ALTER TABLE语句中进行。在实际应用中，通常先定义表的字段，然后再根据实际需要通过ALTER TABLE语句对表添加约束。

4.1 非空约束

非空约束就是限制必须为某个列提供值。空(NULL)值是不存在的它既不是数字0，也不是空字符串，而是不存在，未知的情况。

在表中，若某些字段的值是不可缺少的，那么就可以为该列定义非空约束。这样当插入数据时，如果没有为该列提供数据，那么系统就会出现一个错误。

如果某些列的值是可有可无的，那么可以定义这些列允许空值。这样，在插入数据时，就可以不向该列提供具体的数据(在默认情况下，表中的列是允许为NULL的)。如果某个列的值不允许为NULL，那么就可以使用NOT NULL来标记该列。

**** 创建Books表，要求BookNo(图书编号)，ISBN和PublisherNo(出版社编号)不能为空值。

SQL> create table Books
(
	BookNo number(4) not null,			-- 图数编号，不为空
	BookName varchar2(20),				-- 图数名称
	Author varchar2J(10),				-- 作者
	SalePrice number(9,2),				-- 定价
	PublisherNo varchar2(4) not null,		-- 出版社编号，不为空
	PublishDate date,					-- 出版日期
	ISBN varchar2(20) not null			-- ISBN，不为空
);

表已创建。

在创建完表之后，也可以使用ALTER TABLE MODIFY语句为已经创建的表删除或重定义NOT NULL约束。

**** 为Books表中的BookName(图书名称)字段设置非空约束，代码：

SQL> alter table books modify bookname not null;

表已更改。

如果使用ALTER TABLE...MODIFY语句为表添加NOT NULL约束，并且表中该列数据已经存在NULL值，则向该列添加NOT NULL约束将失败。
这是因为列应用非空约束时，Oracle会试图检查表中的所有行，以验证所有的行在对应的列是否存在NULL值。

另外，使用ALTER TABLE...MODIFY语句还可以删除表的非空约束，实际上也可以理解为修改某个列的值可以为空。

**** 删除Books表中关于BookName列的非空约束，代码：
SQL> alter table books modify bookname null;

4.2 主键约束

主键约束用于唯一地标识表中的每一行记录。在一个表中，最多只能有一个主键约束，主键约束既可以由一个列组成，也可以由两个或两个以上的列组成(这种称为联合主键)。对于表中的每一行数据，主键约束列都是不同的，主键约束同时也具有非空约束的特性。

如果主键约束由一列组成，该主键约束被称为行级约束。如果主键约束由两个或两个以上的列组成时，则该主键约束被称为表级约束。

若要设置某个或某些列为主键约束，通常使用CONSTRAINT...PRIMARY KEY语句来完成。

创建表Books_1，并为该表定义行级主键约束BOOK_PK(主键列为BookNo)，代码：

SQL> create table Books
(
	BookNo number(4) not null,			-- 图数编号，不为空
	BookName varchar2(20),				-- 图数名称
	Author varchar2J(10),				-- 作者
	SalePrice number(9,2),				-- 定价
	PublisherNo varchar2(4) not null,		-- 出版社编号，不为空
	PublishDate date,					-- 出版日期
	ISBN varchar2(20) not null,			-- ISBN，不为空
	constraint BOOK_PK primary key(BookNo)	-- 创建主键和主键约束
);
表已创建。

说明：如果构成主键约束的列有多个(即创建表级约束)，则多个列之间使用英文输入法下的逗号(,)分隔。

如果表在创建时未定义主键约束，用户可以使用ALTER TABLE...ADD CONSTRAINT...PRIMARY KEY语句为该表添加主键约束。

**** 使用ALTER TABLE...ADD语句为Books表添加主键约束，代码：
SQL> alter table Books add constraint Books_PK primary key(BookNo);
表已更改。

在上面的代码中，由于为PRIMARY KEY约束指定名称，这样就必须使用CONSTRAINT关键字。

如果要使用系统自动为其分配的名称(即不指定主键约束的名称)，则可以省略CONSTRAINT关键字，并且在指定列的后面直接使用PRIMARY KEY标记就可以。

*** 创建表Books_2时，在BookNo列上定义了一个由系统自动分配名称的主键约束，代码：

SQL> create table Books
(
	BookNo number(4) primary key,			-- 图数编号,设置为由系统自动分配名称的主键约束
	BookName varchar2(20),				-- 图数名称
	Author varchar2J(10),				-- 作者
	SalePrice number(9,2),				-- 定价
	PublisherNo varchar2(4) not null,		-- 出版社编号，不为空
	PublishDate date,					-- 出版日期
	ISBN varchar2(20) not null,			-- ISBN，不为空
);
表已创建

注意：在上面的代码中，BookNo列的后面可以不使用NOT NULL标记其不许为NULL，因为PRIMARY KEY约束本身就不允许列值为NULL。

同样，也可以使用ALTER TABLE...ADD语句添加由系统自动分配名称的主键约束。

*** 使用ALTER TABLE...ADD语句为Books表在BookNo列上添加由系统自动分配名称的主键约束，代码：

SQL> alter table Books
add primary key(BookNo);
表已更改。

注意：与NOT NULL约束相同，当为表添加主键约束时，如果该表中已经存在数据，并且主键列具有相同的值或存在NULL值，则添加主键约束的操作将失败。

另外，既然可以为表添加主键约束，那么就应该可以删除主键约束，删除PRIMARY KEY约束通常使用ALTER TABLE...DROP语句来完成。

**** 删除Books_1表的主键约束BOOK_PK，代码：
SQL> alter table Books_1
drop constraint BOOK_PK;

表已更改。

4.3 唯一性约束

唯一性约束强调所在的列不允许有相同的值。但是，它的定义要比主键约束弱，即它所在的列允许空值(但主键约束列是不允许为空值的)。

唯一性约束的主要作用是在保证除主键列外，其它列值的唯一性。

在一个表中，根据实际情况可能有多个列的数据都不允许存在相同的值。例如，各种"会员表"的QQ, E-mail等列的值是不允许重复的(但用户可以不提供，这样就必须允许空值)，但是由于在一个表中最多只能有一个主键约束存在，那么如何解决这种多个列都不允许重复数据存在的问题呢？

这就是唯一性约束的作用。若要设置某个列为UNIQUE约束，通常使用CONSTRAINT...UNIQUE标记该列。

创建一个会员表Members，并要求为该表的QQ列定义唯一性约束看，代码：

SQL> create table Members
(
	MemNo number(4) not null,					-- 会员编号
	MemName varchar2(20) not null,				-- 会员名称
	Phone varchar2(20),						-- 联系电话
	Email varchar2(30),						-- 电子邮件地址
	QQ varchar2(20) Constraint QQ_UK unique,			-- QQ号，并设置为UNIQUE约束
	ProvCode varcahr2(2) not null,				-- 省分代码
	OccuCode varchar2(2) not null,				-- 职业代码
	InDate date default sysdate,					-- 入会日期
	Constraint Mem_PK primary key(MemNo)			-- 主键约束列为MemNo
);
表已创建。

如果UNIQUE约束的列有值，则不允许重复，但是可以插入多个NULL值，即该列的空值可以重复。

说明：由于UNIQUE约束列可以存在重复的NULL值，因此为了防止这种情况发生，可以在该列添加NOT NULL约束。
        如果向UNIQUE约束列添加NOT NULL约束，那么这种NUIQUE约束基本上就相当于主键PRIMARY KEY约束了。

除了可以在创建表时定义UNIQUE约束，还可以使用ALTER TABLE...ADD CONSTRAINT...UNIQUE语句为现有的表添加UNIQUE约束。

*** 为members表的email列添加唯一约束，代码：
SQL> alter table members add constraint Email_UK unique(email);
表已更改。

说明：如果要为现有表的多个列同时添加UNIQUE约束，则在括号内使用逗号分隔多个列。

能够为某个列创建唯一约束，当然也可以删除某个列的唯一约束限制，通常使用ALTER TABLE...DROP CONSTRAINT 语句来删除UNIQUE约束。

**** 删除members 表的Email_UK这个唯一约束，代码：
SQL> alter table members drop constraint Email_UK;

表已更改。

4.4 外键约束

外键约束比较复杂，一般的外键约束会使用两个表进行关联(当然也存在同一个表自连接的情况)。外键是指"当前表"(即外键表)引用"另外一个表"(即被引用表)的某个列或某几个列，而"另外一个表"中被引用的列必须具有主键约束或者唯一性约束。

在"另外一个表"中，被引用列中不存在的数据不能出现在"当前表"对应的列中。

一般情况下，当删除被引用表中的数据时，该数据也不能出现外键表的外键列中。如果外键列存储了被引用表中将要被删除的数据，那么对被引用表的删除操作将失败。

最典型的外键约束是hr模式中的employees和department表，在该外键约束中，外键表employees中的外键列department_id将引用被引用表departments中的dempartment_id列，而该列也是departments表的主键。

**** 在hr模式中，创建一个新表employees_temp(该表的结构复制自employees)，并为其添加一个与departments表之间的外键约束，代码：

SQL> create table employees_temp
	as select * from employees
	where department_id=30;	-- 创建一个新表，并将部门编号为30的员工记录插入。
表已创建。

SQL> alter table employees_temp
	add constraint temp_departid_fk
	foreign key(department_id)
	references departments(department_id);	-- 创建外键约束，外键列为department_id
表已更改。

如果外键表的外键列与被引用表的被引用列列名相同，则为外键表定义外键列时可以省略REFERENCES关键字后面的列名称。

SQL> alter table employees_temp
	add constraint temp_departid_fk
	foreign key(department_id)
	references departments;			-- 创建外键约束，外键列为department_id

另外，在定义外键约束时，还可以通过关键字ON指定引用行为的类型。当尝试删除被引用表中的一条记录时，通过引用行为可以确定如何处理外键表中的外键列，引用行为的类型包括3种。

（1）在定义外键约束时，如果使用了关键字NO ACTION，那么当删除被应用表中被引用列的数据时将违反外键约束，该操作将被禁止执行，这也是外键的"默认引用类型"。
（2）在定义外键约束时，如果使用了关键字SET NULL，那么当被应用表中被引用列的数据被删除时，外键表中的外键列被设置为NULL，要使这个关键字起作用，外键列必须支持NULL值。
（3）在定义外键约束时，如果使用了关键字CASCADE，那么阿当被应用表中被引用列的数据被删除时，外键表中对应的数据也将被删除，这种删除方式通常称作"级联删除"，它在实际应用开发中得到比较广泛的应用。

*** 在hr模式中，创建一个新表departments_temp(该表的结构复制自deaprtments),然后在该表与employees_temp表之间建立外键约束，并指定外键约束的引用类型为ON DELETE CASCADE,最后删除departments_temp与employees_temp表中都存在的外键值，具体操作步骤如下：
（1）在hr模式中，创建一个被引用表(该表的结构复制自departments)，并为其设置主键约束，代码：

SQL> connect hr/hr	-- 在hr模式下
已连接。
SQL> create table departments_temp
	as select * from departments
	where department_id=30;			-- 创建departments_temp表

表已创建。

SQL> alter table departments_temp
	add primary key(department_id);	-- 设置departments_temp表的主键约束

表已更改。

（2）在employees_temp表和departments_temp表之间创建外键约束，并指定外键约束的引用类型为ON DELETE CASDE，代码：

SQL> alter table employees_temp
	add constraint temp_departid_fk2
	foreign key(department_id)
	references departments_temp on delete cascade;

表已更改。

（3）查看外键表employees_temp表中部门编号为30的记录数，代码：

	SQL> select count(*) from employees_temp where department_id=30;

（4）删除外键表departments_temp中的department_id为30的记录，代码：

SQL> delete departments_temp
		where department_id=30;
	已删除1行。

	SQL> select count(*) from employees_temp where department_id=30;

通过上面的查询结果可以看出，由于指定了外键约束的引用类型为ON DELETE CASDE，所以在删除被引用表departments_temp中编号为30的记录时，系统也级联删除了employees_temp表中所有编号为30的记录。

在创建完外键约束之后，如果想要删除外键约束，则可以使用ALTER TABLE...DROP CONSTRAINT语句。

*** 删除employees_temp表和departments_temp表之间的外键约束temp_departid_fk2，代码：

SQL> alter table employees_temp
		drop constraint temp_departid_fk2;
表已更改。

4.5 禁用和激活约束

约束创建之后，如果没有经过特殊处理，就一直起作用。
但也可以根据实际需要，临时禁用某个约束。当某个约束被禁用后，该约束就不再起作用了，但它还存在于数据库中。
那么为什么要禁用约束呢？这是因为约束的存在会降低插入和更改数据的效率，系统必须确认这些数据是否满足定义的约束条件。当执行一些特殊操作时，例如使用SQL*Loader从外部数据源向表中导入大量数据，并且事先知道这些数据是满足约束条件的，此时为提高运行效率，就可以禁用这些约束。
禁用约束操作，不但可以对现有的约束执行，而且还可以在定义约束时执行。

（1）在定义约束时禁用。

在使用CREATE TABLE 或ALTER TABLE语句定义约束时(默认情况下约束是激活的)，如果使用关键字DISABLE，则约束是被禁用的。

**** 创建一个学生信息表(Student),并为年龄列(Age)定义一个DISABLE状态的CHECK约束(要求年龄值在0~120)，代码：

SQL> create table Student
(
	StuCode varchar2(4) not null,
	StuName varchar2(10) not null,
	Age int constraint Age_CK check(age>0 and age<120) disable,
	Province varchar2(20),
	SchoolName varchar2(50)
);

表已创建。

（2）禁用已经存在的约束

对于已经存在的约束，则可以使用ALTER TABLE...DISABLE CONSTRAINT语句禁止该约束。

**** 禁用employees_temp表中的约束temp_departid_fk，代码：

SQL> alter table employees_temp
		disable constraint temp_departid_fk;
表已更改。

说明：在禁用主键约束时，Oracle会默认删除约束对应的唯一索引，而在重新激活约束时，Oracle将会重新建立唯一索引。如果希望在删除约束时保留对应的唯一索引，可以在禁用约束时使用关键字KEEP INDEX(通常放在约束名称的后面)。

技巧：在禁用唯一性约束或主键约束时，如果有外键约束正在引用该列，则无法禁用唯一性约束或主键约束。这时可以先禁用外键约束，然后再禁用唯一性约束或主键约束；或者在禁用唯一性约束或主键约束时使用CASCADE关键字，这样可以级联禁用这些列的外键约束。

禁用约束只是一种暂时的现象，在特殊需求处理完毕之后，还应该及时激活约束。如果希望激活被禁用的约束，则可以在ALTER TABLE语句中使用ENABLE CONSTRAINT子句。激活约束的语法形式：

ALTER TABLE table_name
ENABLE [NOVALIDATE|VALIDATE] CONSTRAINT con_name;

a. table_name: 表示要激活约束表的名称。
b. NOVALIDATE: 该关键字表示在激活约束时不验证表中已经存在的数据是否满足约束，如果没有使用该关键字，或者使用VALIDATE关键字，则在激活约束时系统将验证表中的数据是否满足约束的定义。

**** 首先禁用Books_1表中的主键BOOK_PK,然后再重新激活该约束，具体步骤如下：
（1）使用ALTER TABLE语句禁用BOOK_PK主键约束，代码：

SQL> alter table books_1
	disable constraint BOOK_PK;

表已更改。

（2）在Books_1表中插入两行数据，并且这两行数据的bookno列的值相同(如8888)，代码：
SQL> insert into books_1(bookno,publisherno,isbn)
		values(8888,'东方','12345678');
已创建1行。

SQL> insert into books_1(bookno,publisherno,isbn)
		values(8888,'东方','7890122');
已创建1行。

通过上面运行结果可以看出，由于在禁用BOOK_PK主键之后，不受主键约束条件的限制，可以给bookno列添加重复值。

（3）使用ALTER TABLE语句激活BOOK_PK主键约束，代码：

SQL> alter table books_1
		enable constraint BOOK_PK;
after table books_1
*
第1行出现错误：
ORA-02437: 无法验证(SYSTEM.BOOK_PK)-违反主键

由于bookno列的现有值中存在重复的情况，这与主键约束的作用存在冲突，因此激活约束的操作一定是失败的。对于这种情况的解决办法，通常是更正表中不满足约束条件的数据。

4.6 删除约束

如果不再需要某个约束时，则可以将其删除，可以使用带DROP CONSTAINT子句的ALTER TABLE语句删除约束。删除约束与禁用约束不同，禁用的约束是可以激活的，但是删除的约束在表中就完全消失了。使用ALTER TABLE语句删除约束的语法格式：

ALTER TABLE table_name DROP CONSTRAINT con_name;
a. table_name: 表示要删除约束的表名称。
b. con_name: 要删除的约束名称。

**** 通过下面的语句删除Student表中所创建的CHECK约束Age_CK，代码：

SQL> alter table Student
		drop constraint Age_CK;
表已更改。