oracle模式对象的管理

实验二 模式对象的管理

【开发语言及实现平台或实验环境】

Oracle10g 【实验目的】

（1）学习基本数据库表的创建，表结构的修改与删除等命令； （2）学习索引的管理命令； （3）学习索引化表的管理命令 （4）学习分区表的管理命令

（5）学习簇、视图、序列、同义词等的管理命令

【实验原理】 1．基本表的管理 （1） 表的创建： Create命令：

CREATE TABLE table_name( column_name type(size), column_name type(size), …);或 CREATE TABLE table_name [(column_name,…)] AS SELECT statement； 表名命名规则

所用的表名必须满足下面的条件： a) 名字必须以A-Z 或a-z的字母开始； b) 名字可以包括字母、数字和特殊字母（_）。字符$和#也是合法的，但是这种用法不提倡； c) 名字大小写是一样的；例如EMP、emp和eMp是表示同一个表； d) 名字最长不超过30个字符； e) 表名不能和其它的对象重名； f) 表名不能是SQL保留字。 字段类型：

完整性约束：

Oracle允许用户为表和列定义完整性约束来增强一定的规则。 可分为：表约束和字段约束 约束类型如下：

1）NOT NULL约束

NOT NULL约束保证字段值不能为NULL。没有NOT NULL约束的字段，值可以为NULL。

2）UNIQUE约束

指定一个字段或者字段的集合为唯一键。在表中没有两行具有相同的值。如果唯一键是基于单条记录的，NULL是允许的。

表约束命令格式：

，[CONSTRAINT constraint_name] UNIQUE (Column, Column, …) 字段约束命令格式：

[CONSTRAINT constraint_name] UNIQUE 例如：

CREATE TABLE DEPT

（DEPTNO NUMBER， DNAME VARCHAR2（9）， LOC VARCHAR2（10），CONSTRAINT UNQ_DEPT_LOC UNIQUE（DNAME，LOC））； UNQ_DEPT_LOC是一个表约束。 3）主键约束（Primary Key Constraint）

主键约束强制字段和字段集合的唯一性，并且用一个唯一索引来管理它。每个表中只能用一个主键，这样可以通过主键来标识表中的每条记录。NULL值不允许在主键字段出现。

表约束命令格式：

，[CONSTRAINT constraint_name] PRIMARY KEY (Column, Column, …) 字段约束命令格式：

[CONSTRAINT constraint_name] PRIMARY KEY 例如：用字段约束定义DEPTNO为主键 CREATE TABLE DEPT

（DEPTNO NUMBER（2） CONSTRAINT DEPT_PRIM PRIMARY KEY, …）; 4）外键约束

外键提供表内或表间的完整性规则。外键必须依赖于一个primary或unique key。 表约束命令格式：

，[CONSTRAINT constraint_name] FOREIGN KEY (Column, Column, …) REFERENCE table (column, column, …)

字段约束命令格式：

[CONSTRAINT constraint_name] FOREIGN KEY table (column) 例如：

CREATE TABLE EMP

（…CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCE DEPT(DEPTNO)); 5）Check约束

CHECK约束定义了每条记录必须满足的条件 语法：

[CONSTRAINT constraint_name] CHECK (condition) （2）修改表

ALTER TABLE命令可用来修改数据表的定义。 命令格式：

ALTER TABLE tablename

[ADD或MODIFY或DROP options ](column_spec [column_constraint]) [ENABLE clause 或DISABLE clause]

ADD关键字可以用来给已存在的数据表增加一个字段或约束。 如：给EMP增加一个字段

ALTER TABLE EMP ADD （SPOUSES_NAME CHAR(10)）； MODIFY关键字可以用来修改已存在的数据表定义。 如：把EMP中ENAME长度改为25个字符

ALTER TABLE EMP MODIFY （ENAM CHAR（25））； （3）删除表

用DROP TABLE命令删除Oracle数据表的定义。 命令格式：

DROP TABLE table_name [CASCADE CONSTRAINT] 例如：

DROP TABLE EMP；

CASCADE CONSTRAINT选项说明了也把完整性约束一起删除。 注意：

DROP TABLE也把数据表中的数据删除。

数据表的VIEWS和SYNOMNYMS保留下来，但它们变成了不合法的。 任何悬而未决的事务将被提交。

只有数据表的生成者或DBA才有权删除它。 2.索引的管理 （1）创建索引 语法

CREATE [UNIQUE]|[BITMAP] INDEX index_name ON table_name([column_name[ASC|DESC],…]| [expression]) [REVERSE]

[parameter_list]； 创建非惟一性索引

CREATE INDEX employee_ename ON employee(ename)TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75); 创建惟一性索引

CREATE UNIQUE INDEX deptartment_index ON department(dname); 创建位图索引

CREATE BITMAP INDEX student_sex ON student(sex); 创建反序索引

CREATE INDEX player_sage ON player(sage) REVERSE; 创建函数索引

CREATE INDEX idx ON employee(UPPER(ename)); （2）删除索引 语法

DROP INDEX index_name; 3.索引化表的管理

在CREATE TABLE语句中指定ORGANIZATION INDEX关键字。

CREATE TABLE new_student(

sno NUMBER PRIMARY KEY , sname CHAR(11), sage NUMBER, sex CHAR(2))

ORGANIZATION INDEX TABLESPACE USERS ; 可以通过子查询创建索引化表 CREATE TABLE new_emp(

empno PRIMARY KEY,ename) ORGANIZATION INDEX

AS SELECT empno,ename FROM emp; 4. 分区表的管理 创建分区表 （1）范围分区

范围分区是按照分区列值的范围来对表进行分区的。

通过PARTITION BY RANGE子句说明根据范围进行分区，其后括号中列出分区列。 每个分区以PARTITION关键字开头，其后是分区名。VALUES LESS THAN子句用于设置分区中分区列值的范围。 示例:

创建一个分区表，将学生信息根据其出生日期进行分区，将1980年1月1日前出生的学生信息保存在ORCLTBS1表空间中，将1980年1月1日到1990年1月1日出生的学生信息保存在ORCLTBS2表空间中，将其他学生信息保存在ORCLTBS3表空间中。 CREATE TABLE student_range(

sno NUMBER(6) PRIMARY KEY, sname VARCHAR2(10), sage int, birthday DATE )

PARTITION BY RANGE(birthday) ( PARTITION p1 VALUES LESS THAN

(TO_DATE('1980-1-1', 'YYYY-MM-DD')) TABLESPACE ORCLTBS1, PARTITION p2 VALUES LESS THAN

(TO_DATE('1990-1-1', 'YYYY-MM-DD')) TABLESPACE ORCLTBS2,

PARTITION p3 VALUES LESS THAN(MAXVALUE) TABLESPACE ORCLTBS3 STORAGE(INITIAL 10M NEXT 20M))

STORAGE(INITIAL 20M NEXT 10M MAXEXTENTS 10 );

（2）列表分区 概述

如果分区列的值并不能划分范围（非数值类型或日期类型），同时分区列的取值范围只是一个包含少数值的集合，则可以对表进行列表分区（LIST），如按地区、性别等分区。

通过PARTITION BY LIST子句说明根据列表进行分区，其后括号中列出分区列。每个分区以PARTITION 关键字开头，其后是分区名。VALUES子句用于设置分区所对应的分区列取值。 示例

创建一个分区表，将学生信息按性别不同进行分区，男学生信息保存在表空间ORCLTBS1中，而女学生信息保存在ORCLTBS2中。 CREATE TABLE student_list(

sno NUMBER(6) PRIMARY KEY, sname VARCHAR2(10),

sex CHAR(2) CHECK(sex in ('M', 'F')) )

PARTITION BY LIST(sex)

(PARTITION student_male VALUES('M') TABLESPACE ORCLTBS1, PARTITION student_female VALUES('F') TABLESPACE ORCLTBS2 );

（3）散列分区 概述

散列分区（HASH）是在指定数量的分区中均等地分配数据。

为了创建散列分区，需要指定分区列、分区数量或单独的分区描述。

通过PARTITION BY HASH指定分区方法，其后的括号指定分区列。使用PARTITION子句指定每个分区名称和其存储空间。或者使用PARTITIONS子句指定分区数量，用STORE IN子句指定分区存储空间。 示例

创建一个分区表，根据学号将学生信息均匀分布到ORCLTBS1和ORCLTBS2两个表空间中。 CREATE TABLE student_hash (

sno NUMBER(6) PRIMARY KEY, sname VARCHAR2(10) )

PARTITION BY HASH(sno)

(PARTITION p1 TABLESPACE ORCLTBS1, PARTITION p2 TABLESPACE ORCLTBS2 );

CREATE TABLE student_hash2 (

sno NUMBER(6) PRIMARY KEY, sname VARCHAR2(10) ) PARTITION BY HASH(sno)

PARTITIONS 2 STORE IN(ORCLTBS1,ORCLTBS2); （4）复合分区 复合分区包括： 范围-列表复合分区 范围-散列复合分区。

创建复合分区时需要指定：

分区方法（PARTITION BY RANGE） 分区列

子分区方法（SUBPARTITION BY HASH，SUBPARTITION BY LIST） 子分区列

每个分区中子分区数量或子分区的描述。 范围-列表复合分区

范围-列表复合分区先对表进行范围分区，然后再对每个分区进行列表分区，即在一个范围分区中创建多个列表子分区。 示例：

创建一个范围-列表复合分区表，将1980年1月1日前出生的男、女学生信息分别保存在ORCLTBS1和ORCLTBS2表空间中，1980年1月1日到1990年1月1日出生的男、女学生信息分别保存在ORCLTBS3和ORCLTBS4表空间中，其他学生信息保存在ORCLTBS5表空间中。

CREATE TABLE student_range_list( sno NUMBER(6) PRIMARY KEY,

sname VARCHAR2(10), sex CHAR(2) CHECK(sex IN ('M','F')), sage NUMBER(4), birthday DATE )

PARTITION BY RANGE(birthday) SUBPARTITION BY LIST(sex)

(PARTITION p1 VALUES LESS THAN(TO_DATE('1980-1-1', 'YYYY-MM-DD')) (SUBPARTITION p1_sub1 VALUES('M') TABLESPACE ORCLTBS1, SUBPARTITION p1_sub2 VALUES('F') TABLESPACE ORCLTBS2), PARTITION p2 VALUES LESS THAN(TO_DATE('1990-1-1', 'YYYY-MM-DD')) (SUBPARTITION p2_sub1 VALUES('M') TABLESPACE ORCLTBS3, SUBPARTITION p2_sub2 VALUES('F') TABLESPACE ORCLTBS4), PARTITION p3 VALUES LESS THAN(MAXVALUE) TABLESPACE ORCLTBS5 );

范围-散列复合分区

范围-散列复合分区先对表进行范围分区，然后再对每个分区进行散列分区，即在一个范围分区中创建多个散列子分区。 示例：

创建一个范围-散列复合分区表，将1980年1月1日前出生的学生信息均匀地保存在ORCLTBS1和ORCLTBS2表空间中，1980年1月1日到1990年1月1日出生的学生信息保存在ORCLTBS3和ORCLTBS4表空间中，其他学生信息保存在ORCLTBS5表空间中。 CREATE TABLE student_range_hash( sno NUMBER(6) PRIMARY KEY, sname VARCHAR2(10), sage NUMBER(4), birthday DATE )

PARTITION BY RANGE(birthday) SUBPARTITION BY HASH(sage)

(PARTITION p1 VALUES LESS THAN(TO_DATE('1980-1-1', 'YYYY-MM-DD')) (SUBPARTITION p1_sub1 TABLESPACE ORCLTBS1,

SUBPARTITION p1_sub2 TABLESPACE ORCLTBS2),

PARTITION p2 VALUES LESS THAN(TO_DATE('1990-1-1', 'YYYY-MM-DD'))

(SUBPARTITION p2_sub1 TABLESPACE ORCLTBS3, SUBPARTITION p2_sub2 TABLESPACE ORCLTBS4), PARTITION p3 VALUES LESS THAN(MAXVALUE) TABLESPACE ORCLTBS5 );

5. 簇的管理 （1）创建簇

在数据库中，簇占据实际的存储空间，因此用户必须具有足够的表空间配额。 例：

CREATE CLUSTER student_class ( class_id NUMBER(3)) SIZE 600

TABLESPACE users

STORAGE (INITIAL 200K NEXT 300K MINEXTENTS 2 MAXEXTENTS 20); （2）创建聚簇表

在簇中创建的表称为“聚簇表”。通过将两个或多个聚簇表保存在同一个簇中，可以将两个表中具有相同的聚簇字段值的记录集中存放在同一个数据块（或相邻的多个数据块）中。 在CREATE TABLE语句中通过CLUSTER子句来指定表所使用的簇和聚簇字段。 聚簇表中的聚簇字段必须与创建簇时指定的聚簇字段具有相同的名称和数据类型。 SQL> CREATE TABLE classes(

class_id NUMBER(3) PRIMARY KEY, cname VARCHAR2(10) ) CLUSTER student_class (class_id); SQL> CREATE TABLE students (

sno NUMBER(5) PRIMARY KEY, sname VARCHAR2(15) NOT NULL,

class_id NUMBER(3) REFERENCES classes) CLUSTER student_class(class_id); （3）创建聚簇索引

可以为簇中的聚簇字段创建索引，这种类型的索引称为“聚簇索引”。 例如，为簇student_class创建一个聚簇索引。 CREATE INDEX student_class_index ON CLUSTER student_class TABLESPACE USERS

STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 2 MAXEXTENTS 10) PCTFREE 10; （4）删除簇

删除簇的同时将删除聚簇索引。根据簇中是否包含表，簇删除可以分为下列3种情况。 使用DROP CLUSTER删除不包含聚簇表的簇及簇索引。 DROP CLUSTER student_class;

使用DROP CLUSTER...INCLUDING TABLES语句删除包含聚簇表的簇。 DROP CLUSTER student_class INCLUDING TABLES;

如果聚簇表中包含其他表外键参考的主键约束列或唯一性约束列，则需要使用CASCADE CONSTRAINTS子句删除约束，同时删除簇。

DROP CLUSTER student_class INCLUDING TABLES CASCADE CONSTRAINTS; 6.视图的管理 （1）创建视图 语法

CREATE [OR REPLACE] [FORCE| NOFORCE] VIEW [schema.]view_name[(column1,column2,…)] AS subquery

[WITH READ ONLY]|[WITH CHECK OPTION] [CONSTRIANT constraint]; （2）删除视图

使用DROP VIEW语句删除视图。 7.序列的管理

序列用于产生惟一序号的数据库对象，用于为多个数据库用户依次生成不重复的连续整数。通常使用序列自动生成表中的主键值。 （1）创建序列 语法：

CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n]

[MAXVALUE n | NOMAXVALUE] [MINVALUE n | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE n | NOCACHE]; 参数说明：

INCREMENT BY：设置相邻两个元素之间的差值，即步长，默认值为1； START WITH：设置序列初始值，默认值为1； MAXVALUE：设置序列最大值

NO MAXVALUE：设置默认情况下，递增序列的最大值为1027，递减序列的最大值为-1； MINVALUE：设置序列最小值；

NOMINVALUE：设置默认情况下，递增序列的最小值为1， 递减序列的最小值为-1026； CYCLE|NOCYCLE：指定当序列达到其最大值或最小值后，是否循环生成值，NOCYCLE 是默认选项；

CACHE|NOCACHE：设置是否在缓存中预先分配一定数量的数据值，以提高获取序列值的速度，默认为缓存20个值。 例：

CREATE SEQUENCE stud_sequence INCREMENT BY 1 START WITH 100 MAXVALUE 1000; （2）修改序列 说明

除了不能修改序列起始值外，可以对序列其他任何子句和参数进行修改。 例如，修改序列stud_sequence的设置。 ALTER SEQUENCE stud_sequence INCREMENT BY 10

MAXVALUE 10000 CYCLE CACHE 20; （3）删除序列

可以使用DROP SEQUENCE语句删除序列。 例如，删除序列stud_sequence。 DROP SEQUENCE stud_sequence; 8. 同义词管理

同义词是数据库中表、索引、视图或其他模式对象的一个别名。 （1）创建同义词 语法：

CREATE [PUBLIC] SYNONYM synonym_name FOR object_name; 例如：

CREATE PUBLIC SYNONYM scottemp FOR scott.emp; （2）删除同义词 语法：

DROP [PUBLIC] SYNONYM synonym_name; 例如：

DROP PUBLIC SYNONYM scottemp; 【实验内容】

要求：

1. 创建Class、Student两个表；

2. 为Student表添加一个可延迟的外键约束，其CNO列参照class表的CNO列；

3. 为student表的sex列添加一个检查约束，保证该列取值为“M”或“F”，且默认值为“M”；

4. 在class表的CNAME列上创建一个唯一性索引；

5.创建一个索引化表，其表结构与Student表相同；

6.创建一个Student_range表（列、类型与student表的列、类型相同）

，按学生年龄分为

3个区，低于20岁的学生信息放入part1区，存储在stbs1表空间中；20-30岁的学生信息放在part2区，存储在stbs2表空间中；其他数据放在part2区，存储在stbs3表空间中。

7. 创建一个Student_list表（列、类型与student表的列、类型相同），按学生性别分为两个区。

8.创建一个class_number簇，聚簇字段名为CNO，类型为NUMBER(2)。然后利用该簇，创建students和classes两个聚簇表。

4. 创建一个起始值为10000的序列，步长为2，最大值为1000000，不可循环。

本文来源：https://www.bwwdw.com/article/lelm.html