Oracle11g数据库基础教程课后习题答案

Oracle11g数据库基础教程

参考答案

第1章 Oracle 11g数据库安装与配置

1．简答题

（1）

企业版数据库服务器包含所有的数据库组件，主要针对高端的应用环境，适用于安全性和性能要求较高的联机事务处理（OLTP）、查询密集型的数据仓库和要求较高的Internet应用程序：标准版数据库服务器提供大部分核心的数据库功能和特性，适合于工作组或部门级的应用程序：个人版数据库服务器只提供基本数据库管理功能和特性，适合单用户的开发环境，为用户提供开发测试平台。 （2）

常用数据库类型包括事务处理类、数据仓库类以通用类型。其中事务处理类型主要针对具有大量并发用户连接，并且用户主要执行简单事务处理的应用环境。事务处理数据库的典型应用有银行系统数据库、Internet电子商务数据库、证券交易系统数据库等。对于需要较高的可用性和事务处理性能、存在大量用户并行访问相同数据以及需要较高恢复性能的数据库环境，事务处理类型的配置可以提供最佳性能；数据仓库类型的数据库主要针对有大量的对某个主题进行复杂查询的应用环境。数据仓库的典型应用有客户订单研究、支持呼叫、销售预测、采购模式以及其他战略性业务问题的历史数据研究。对于需要对大量数据进行快速访问，以及复杂查询的数据库环境，数据仓库类型配置是最佳选择；通用类型配置的数据库是事务处理数据库与数据仓库配置的折衷方案。既可以支持大量并发用户的事务处理，又可以快速对大量历史数据进行复杂的数据扫描和处理。 （3）

数据库名可以由字母、数字、下划线（_）、#和美元符号（$）组成，且必须以字母开头，长度不超过30个字符。在单机环境中，可以不设置域名，域名长度不能超过128个字符。 Oracle服务标识符（SID）是一个Oracle实例的唯一名称标识，长度不能超过12个字符。 （4）

? OracleServiceORCL：数据库服务（数据库实例），是Oracle核心服务，是数据库启动的基础，只有该服务启动，Oracle数据库才能正常启动。（必须启动） ? OracleOraDb11g_home1TNSListener：监听器服务，该服务只有在远程访问数据库时才需要（无论远程计算机还是本地计算机，凡是通过Oracle Net网络协议连接数据库都属于远程访问）。（必须启动）

? OracleOraDb11g_home1ConfigurationManager：配置Oracle启动时的参数的服务。（非必须启动）

? OracleOraDb11g_home1ClrAgent：提供对.NET支持的Oracle数据库扩展服务。（非必须启动）

? OracleJobSchedulerORCL：数据库作业调度服务。（非必须启动）

? OracleDBConsoleorcl：Oracle控制台服务，即企业管理器服务。只有该服务启动了，才可以使用Web方式的企业管理器管理数据库。（非必须启动） ? OracleVssWriterORCL：是Oracle对VSS提供支持的服务。（非必须启动）

? OracleMTSRecoveryService：是允许数据库充当一个微软事务服务器、COM/COM+对象和分布式环境下的事务资源管理器的服务。

2．实训题

（1）略 （2）

打开网络管理对话框，分别选中左侧树状结构中的监听器和数据库，修改右侧对话框中的主机名为当前的计算机名。 （3）略

第2章Oracle数据库管理与开发工具

1．简答题

（1）

? 实现对Oracle运行环境的完全管理，包括Oracle数据库、Oracle应用服务器、HTTP服务器等的管理；

? 实现对单个Oracle数据库的本地管理，包括系统监控、性能诊断与优化、系统维护、对象管理、存储管理、安全管理、作业管理、数据备份与恢复、数据移植等； ? 实现对多个Oracle数据库的集中管理； ? 实现对Oracle应用服务器的管理；

? 检查与管理目标计算机系统软硬件配置。 （2）

利用SQL*Plus可以实现以下操作：

? 输入、编辑、存储、提取、运行和调试SQL语句和PL/SQL程序； ? 开发、执行批处理脚本； ? 执行数据库管理；

? 处理数据，生成报表，存储、打印、格式化查询结果； ? 检查表和数据库对象定义。 （3）

建立与数据库的连接后，可以利用SQL Developer实现各种数据库管理与开发操作，包括浏览数据库对象、进行数据的DML操作（插入、删除、修改数据）、进行DDL操作（创建、修改、删除数据库对象）、开发和调试PL/SQL程序、进行数据的导出与导入以及创建与生成报表等操作。 （4）

Oracle提供的ONCA可以实现下列的网络配置任务。

? 监听程序配置：可以添加、重新配置、删除或重命名监听程序。监听程序是数据库服务器响应用户连接请求的进程。

? 命名方法配置：选择命名方法。命名方法是将用户连接时使用的连接标识符解析成连接描述符的方法。

? 本地网络服务名配置：可以添加、重新配置、删除、重命名或测试本地网络服务名，本地网络服务名的解析存放在网络配置文件tnsnames.ora中。 ? 目录使用配置：可以配置符合LDAP协议的目录服务器。 （5）

Oracle网络管理工具ONM是配置和管理Oracle网络环境的一种工具。使用ONM可以对下列的Oracle网络特性和组件进行配置和管理。

? 概要文件：确定客户端如何连接到Oracle网络的参数集合。使用概要文件可以配置命名方法、事件记录、跟踪、外部命令参数以及Oracle Advanced Security的客户端参数。

? 服务命名：创建或修改数据库服务器的网络服务的描述。 ? 监听程序：创建或修改监听程序。

2．实训题

（1）略 （2）略 （3）略 （4）略 （5）略 （6）略

第3章 Oracle数据库系统结构

1．简答题

（1）

Oracle数据库体系结构由物理存储结构、逻辑存储结构和实例组成。其中物理存储结构描述了操作系统层次数据的存储与管理，包括数据文件、日志文件、重做日志文件等组成。逻辑结构描述了数据库内部数据的组织与管理，由表空间、段、区、块组成。实例是数据库运行的软件结构，由内存结构和后台进程组成。数据库运行过程中，用户的操作在内存区中进行，最终通过后台进行转化为对数据库的操作。 （2）

Oracle数据库物理结构包括数据文件、控制文件、重做日志文件、初始化参数文件、归档文件、口令文件等。在控制文件中记录了当前数据库所有的数据文件的名称与位置、重做日志文件的名称与位置，以及数据文件、重做日志文件的状态等。 （3）

Oracle数据库的逻辑存储结构分为Oracle数据块（Oracle Data Block）、区（Extent）、段（Segment）和表空间（Table space）4种。一个或多个连续的Oracle数据块构成区，一个或多个区构成段，一个或多个段构成表空间，所有表空间构成数据库。

（4）

SGA区由数据缓冲区、共享池、重做日志缓冲区、大型池、JAVA池构成；PGA区由排序区、私有SQL区以及堆栈构成。SGA区是由Oracle分配的共享内存结构，包含一个数据库实例共享的数据和控制信息。当多个用户同时连接同一个实例时，SGA区数据供多个用户共享，所以SGA区又称为共享全局区。用户对数据库的各种操作主要在SGA区中进行。该内存区随数据库实例的创建而分配，随实例的终止而释放。

PGA区是在用户进程连接数据库，创建一个会话时，由Oracle为用户分配的内存区域，保存当前用户私有的数据和控制信息，因此该区又称为私有全局区。每个服务器进程只能访问自己的PGA区，所有服务器进程PGA区总和即为实例的PGA区的大小。

（5）

Oracle实例的主要后台进程包括数据库写进程（DBWR）、日志写进程（LGWR）、检查点进程（CKPT）、系统监控进程（SMON）、进程监控进程（PMON）、归档进程（ARCH）、恢复进程（RECO）、锁进程（LCKn）、调度进程（Dnnn）等。

DBWR进程负责把数据高速缓冲区中已经被修改过的数据（“脏”缓存块）成批写入数据文件中永久保存，同时使数据高速缓冲区有更多的空闲缓存块，保证服务器进程将所需要的数据从数据文件读取到数据高速缓冲区中，提高缓存命中率。

LGWR日志写入进程负责把重做日志缓冲区的重做记录写入重做日志文件中永久保存。 CKPT进程的作用就是执行检查点，更新控制文件与数据文件的头部，使其同步；，触发DBWR进程，将脏缓存块写入数据文件。

SMON进程的主要功能包括：在实例启动时负责对数据库进行恢复；回收不再使用的临时空间；将各个表空间的空闲碎片合并。

PMON进程的主要功能包括：负责恢复失败的用户进程或服务器进程，并且释放进程所

占用的资源；清除非正常中断的用户进程留下的孤儿会话，回退未提交的事务，释放会话所占用的锁、SGA、PGA等资源；监控调度进程和服务器进程的状态，如果它们失败，则尝试重新启动它们，并释放它们所占用的各种资源。

ARCH进程用户归档重做日志文件。 RECO进程用户数据库恢复。 （6）

当下列某个条件满足时，DBWR进程将启动，将数据高速缓冲区中的脏数据写入数据文件。

? 服务器进程在数据高速缓存区中搜索一定数量的缓存块后，仍然没有找不到可用的

空闲缓存块，此时DBWR进程将被启动。 ? 检查点发生时，将启动DBWR进程。

? 当数据高速缓冲区中LRU列表长度达到初始化参数DB_BLOCK_WRITE_BATCH指定值

的一半时，DBWR进程将被启动。 ? DBWR进程发生超时（大约3秒），DBWR进程将被启动。 （7）

当下列事件发生时，LGWR进程将重做日志缓冲区中的重做记录写入重做日志文件。 ? 用户通过COMMIT语句提交当前事务。 ? 重做日志缓冲区被写满三分之一。

? DBWR进程开始将脏缓存块写入数据文件。 ? LGWR进程超时（大约3秒），LGWR进程将启动。

3．选择题

（1） D （2） D （3） B （4） B （5） D （6） A （7） D （8） B （9） B （10） A （11） C （12） A （13） C （14） C （15） B （16） A （17） AD （18） A

第4章 案例数据库的创建与客户端的连接

实训题 （1） 略 （2） 略 （3） 略

第5章 数据库存储设置与管理

1．简答题

（1）

数据库表空间分为系统表空间和非系统表空间两类，其中非系统表空间包括撤销表空间、临时表空间和用户表空间等。

SYSTEM表空间主要用于存储数据库的数据字典、PL/SQL程序的源代码和解释代码、数据库对象的定义。撤销表空间专门进行回退信息的自动管理。临时表空间是专门进行临时段管理的表空间。用户表空间用于分离不同应用的数据，而且能够减少读取数据文件时产生的I/O冲突。 （2）

一个数据库由一个或多个表空间构成，不同的表空间用于存放不同应用的数据，表空间的大小决定了数据库的大小。一个表空间对应一个或多个数据文件，数据文件的大小决定了表空间的大小。一个数据文件只能从属于一个表空间。一个数据库对象只能存储在一个表空间中，但可以存储在该表空间所对应的一个或多个数据文件中。 （3）

数据文件中保存了数据库中的所有数据，包括数据字典以及用户数据。 （4）

控制文件保存数据库的物理结构信息，包括数据库名称、数据文件的名称与状态、重做日志文件的名称与状态等。在数据库启动时，数据库实例依赖初始化参数定位控制文件，然后根据控制文件的信息加载数据文件和重做日志文件，最后打开数据文件和重做日志文件。 （5）

重做日志文件是以重做记录的形式记录、保存用户对数据库所进行的修改操作，包括用户执行DDL、DML语句的操作。如果用户只对数据库进行查询操作，那么查询信息是不会记录到重做日志文件中的。 （6）

归档是数据库恢复及热备份的基础。只用当数据库归档模式时，才可以进行热备份和完全恢复。进行归档设置包括归档模式设置（ARCHIVELOG）、归档方式设置以及归档路径的设置等。 （7）

每个数据库至少需要两个重做日志文件，采用循环写的方式进行工作。当一个重做日志文件在进行归档时，还有另一个重做日志文件可用。当一个重做日志文件被写满后，后台进程LGWR开始写入下一个重做日志文件，即日志切换，同时产生一个“日志序列号”，并将这个号码分配给即将开始使用的重做日志文件。当所有的日志文件都写满后，LGWR进程再重新写入第一个日志文件。 （8）

采用多路复用控制文件可以避免由于一个控制文件的损坏而导致数据库无法正常启动。在数据库启动时根据一个控制文件打开数据库，在数据库运行时多路复用控制文件采用镜像的方式进行写操作，保持所有控制文件的同步。

2．实训题

（1）

CREATE TABLESPACE USERTBS1 DATAFILE 'D:\\ORACLE\\ORADATA\\ORCL\\USERTBS1.DBF' SIZE 20M EXTENT MANAGEMENT LOCAL； （2）

CREATE TABLESPACE USERTBS2 DATAFILE 'D:\\ORACLE\\ORADATA\\ORCL\\USERTBS1.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K; （3）

ALTER DATABASE DATAFILE 'D:\\ORACLE\\ORADATA\\ORCL\\USERTBS1.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

（4）

CREATE TEMPORARY TABLESPACE TEMPTBS TEMPFILE 'D:\\ORACLE\\ORADATA\\ORCL\\TEMPTBS.DBF' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16K;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTBS; （5）

ALTER TABLESPACE USERTBS1 OFFLINE; ALTER TABLESPACE USERTBS1 ONLINE; （6）

DROP TABLESPACE USERTBS2 INCLUDING CONTENTS AND DATAFILES; （7）

SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES; （8）

ALTER TABLESPACE USERS

ADD DATAFILE ‘D:\\ORACLE\\ORADATA\\ORCL\%userdata05.dbf’ SIZE 50M’; （9）

ALTER TABLESPACE EXAMPLE

ADD DATAFILE ‘D:\\ORACLE\\ORADATA\\ORCL\\example05.dbf’ SIZE 20M’; （10）

ALTER DATABASE DATAFILE ‘D:\\ORACLE\\ORADATA\\ORCL\%userdata05.dbf’ AUTOEXTEND ON NEXT 5M MAXSIZE 100M; （11）

ALTER DATABASE DATAFILE ‘D:\\ORACLE\\ORADATA\\ORCL\\example05.dbf’ RESIZE 40M; （12）

SHUTDOWN IMMEDIATE;

在操作系统中重命名userdata05.dbf、example05.dbf分别为userdata005.dbf、example005.dbf

STARTUP MOUNT;

ALTER DATABASE RENAME FILE

‘D:\\ORACLE\\ORADATA\\ORCL\%userdata05.dbf’, ‘D:\\ORACLE\\ORADATA\\ORCL\\example05.dbf’ TO

‘D:\\ORACLE\\ORADATA\\ORCL\%userdata005.dbf’, ‘D:\\ORACLE\\ORADATA\\ORCL\\example005.dbf’;

ALTER DATABASE OPEN; （13）

ALTER DATABASE BACKUP CONTROLFILE TO ‘D:\\ORACLE\\CONTROL.BKP’;

（14）

ALTER DATABASE ADD LOGFILE GROUP 5 (‘D:\\ORACLE\\ORADATA\\ORCL\\redo05a.log’,

’D:\\ORACLE\\ORADATA\\ORCL\\redo05b.log’)SIZE 5M; （15）

ALTER DATABASE ADD LOGFILE MEMBER ‘D:\\ORACLE\\ORADATA\\ORCL\\redo05c.log’ TO GROUP 5;

（16）

SHUTDOWN IMMEDIATE STARTUP MOUNT

ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;

ALTER SYSTEM ARCHIVE LOG START

（17）

ALTER SYSTEM SET LOG_ARCHIVE_DEST=’D:\\ORACLE\\BACKUP’

3．选择题

（1） B （2） A （3） B （4） B （5） A （6） D （7） BC （8） A （9） A （10） C （11） B （12） C （13） B （14） B （15） C （16） BC （17） B （18） AC （19） BD （20） C

（21） A （22） AC

第6章 数据库对象的创建与管理

1．简答题

（1）

在Oracle 数据库中，根据表生存周期不同，可以分为永久性表和临时表；根据表中数据组织方式不同，可以分为标准表、索引化表和分区表。索引化表是一种特殊的表，它按B树的结构来组织和存储数据，将表的数据和索引的数据存储在一起。分区表是将一个表分成若干个独立的组成部分进行存储和管理。 （2）

表的约束包括：

? 主键约束：定义了主键约束的列取值不能为空，也不能重复。 ? 唯一值约束：定义了唯一值约束的列取值不能重复。 ? 检查约束：定义了检查约束的列取值要满足检查条件。

? 外键约束：定义了外键约束的列取值要么是主表参照列的值，要么是空值。 ? 空／非空约束：定义了非空约束的列取值不能为空。 （3）

索引用于提高数据的查询效率。根据索引值是否唯一，可以分为唯一性索引和非唯一性索引；根据索引的组织结构不同，可以分为平衡树索引和位图索引；根据索引基于的列数不同可以分为单列索引和复合索引。

（4）

视图是从一个或多个表或视图中提取出来的数据的一种表现形式。在数据库中只有视图的定义，而没有实际对应“表”的存在。通过视图的使用可以提高数据的安全性，隐藏数据的复杂性，简化查询语句，分离应用程序与基础表，保存复杂查询等。 （5）

序列是用于产生唯一序列号的数据库对象，可以为多个数据库用户依次生成不重复的连续整数，通常使用序列自动生成表中的主键值。

序列创建后，可以通过序列NEXTVAL属性产生序列值，通过属性CURRVAL查看当前的序列值。

（6）

分区的必要性：

? 提高数据的安全性，一个分区的损坏不影响其他分区中数据的正常使用； ? 将表的各个分区存储在不同磁盘上，提高数据的并行操作能力；

? 简化数据的管理，可以将某些分区设置为不可用状态，某些分区设置为可用状态，

某些分区设置为只读状态，某些分区设置为读写状态；

? 操作的透明性，对表进行分区并不影响操作数据的SQL语句。

对表进行分区的方法包括：

? 范围分区：按照分区值的范围对表进行分区。

? 列表分区：按照分区值对表进行分区。

? 散列分区：采用HASH算法在指定数量的分区中均等地分配数据。 ? 复合分区：先对表进行范围分区，然后对每个分区进行散列分区。 （7）

本地分区索引是指为分区表中的各个分区单独创建索引分区，各个索引分区之间是相互独立的，索引的分区与表的分区是一一对应的。

全局分区索引：全局分区索引是指先对整个表建立索引，然后再对索引进行分区。索引的分区之间不是相互独立的，索引分区与表分区之间也不是一一对应的

全局非分区索引：建立索引但不分区

2．实训题

（1）略 （2）

Create table exer_class(

CNO number(2) primary key, CNAME varchar2(20), NUM number(3) )

Create table exer_student( SNO number(4) primary key, SNAME varchar2(10) unique, SAGE number, SEX char(2), CNO number(2) ) （3）

Alter table exer_student add constraint ck_sage check (sage>0 and sage<=100); （4）

alter table exer_student add constraint ck_stu check(sex='M' or sex='F')modify sex default 'M' （5）

Create unique index ind_cname on exer_class(cname); （6）

Create view s_c as

Select sno,sname,sage,sex,s.cno,cname,num From exer_class c join exer_student s On c.cno=s.cno; （7）

Create sequence sequ1 start with 100000001;

（8）

create table exer_student_range(

sno number(4) primary key,

sname varchar2(10),sage number, sex char(2),cno number(2)) partition by range(sage)

(partition part1 values less than(20) tablespace example, partition part2 values less than(30) tablespace orcltbs1,

partition part3 values less than(maxvalue) tablespace orcltbs2) （9）

create table exer_student_list( sno number(4) primary key,

sname varchar2(10),sage number, sex char(2),cno number(2)) partition by list(sex)

(partition man values('M') tablespace orcltbs1, partition woman values('F') tablespace orcltbs2) （10）

Create index ind on exer_student_range(sno) local;

3．选择题

（1） C （2） A （3） D （4） B （5） AB （6） B （7） A C （8） B （9） B （10） C （11） BD （12） ACE

第7章 数据操纵与事务处理

1．实训题

（1）略 （2）

INSERT INTO BOOK VALUES('100001','冶金工业出版社','李代平', '冶金工业出版社',TO_DATE('2003-01-01','YYYY-MM-DD'),38);

INSERT INTO BOOK VALUES('100002','Oracle9i中文版入门与提高','赵松涛', '人民邮电出版社',TO_DATE('2002-07-01','YYYY-MM-DD'),35);

INSERT INTO BOOK VALUES('100003','Oracle9i开发指南：PL/SQL程序设计', 'Joan Casteel','电子工业出版社',TO_DATE('2004-04-03','YYYY-MM-DD'),49); INSERT INTO BOOK VALUES('100004','数据库原理辅助与提高','盛定宇', '电子工业出版社',TO_DATE('2004-03-01','YYYY-MM-DD'),34);

INSERT INTO BOOK VALUES('100005','Oracle9i中文版实用培训教程','赵伯山', '电子工业出版社',TO_DATE('2002-01-01','YYYY-MM-DD'),21); INSERT INTO BOOK VALUES('100006','Oracle8实用教程','翁正科等', '电子工业出版社',TO_DATE('2003-07-08','YYYY-MM-DD'),38);

INSERT INTO READER VALUES('200001','张三'); INSERT INTO READER VALUES('200002','李凤'); INSERT INTO READER VALUES('200003','孟欣'); INSERT INTO READER VALUES('200004','谢非'); INSERT INTO READER VALUES('200005','刘英');

INSERT INTO BORROW VALUES('100001','200001', TO_DATE('2004-08-10 10:06:14','YYYY-MM-DD HH:MI:SS')); INSERT INTO BORROW VALUES('100002','200002', TO_DATE('2004-08-10 10:06:27','YYYY-MM-DD HH:MI:SS')); INSERT INTO BORROW VALUES('100003','200003', TO_DATE('2004-08-10 10:06:36','YYYY-MM-DD HH:MI:SS')); INSERT INTO BORROW VALUES('100004','200004', TO_DATE('2004-08-10 10:06:48','YYYY-MM-DD HH:MI:SS')); INSERT INTO BORROW VALUES('100005','200005',

TO_DATE('2004-08-10 10:06:58','YYYY-MM-DD HH:MI:SS')); （3）

INSERT INTO BOOK VALUES('10000007','Java网络编成','李程等', '电子工业出版社',TO_DATE('2000-08-01','YYYY-MM-DD'),35); （4）

UPDATE BOOK SET PRICE=29 WHERE NO='100007'; （5）

DELETE FROM BOOK WHERE NO='10000007'

第8章 数据查询

1．实训题

（1） 查询100号部门的所有员工信息。

Selsect * from employees where department_id = 100

（2） 查询所有职位编号为“SA_MAN”的员工的员工号、员工名和部门号。 Select employee_id,first_name,last_name,department_id from employees where job_id= ‘SA_MAN’

（3） 查询每个员工的员工号、工资、奖金以及工资与奖金的和。 Select employee_id,salary,commission_pct, salary*(1+nvl(commission_pct,0) from employees

（4） 查询40号部门中职位编号为“AD_ASST”和20号部门中职位编号为“SA_REP”的员工的信息。

Select * from employees where department_id=40 and job_id=’ AD_ASST’ OR

department_id=20 and job_id=’ SA_REP’;

（5） 查询所有职位名称不是“Stock Manager”和“Purchasing Manager”，且工资大于或等于2000的员工的详细信息。

Select * from employees where job_id not in(’ Stock Manager’,’ Purchasing Manager’) and salary>=2000

（6） 查询有奖金的员工的不同职位编号和名称。

Select distinct job_id, job_title from jobs where job_id in (select job_id from employees where job_id is not null)

（7） 查询没有奖金或奖金低于100元的员工信息。

Select * from employees where salary*commission_pct<100 or commission is NULL （8） 查询员工名（first_name）中不包含字母“S”的员工。 Select first_name from employees where first_name not like ‘%S%’ （9） 查询员工的姓名和入职日期，并按入职日期从先到后进行排序。 Select first_name,last_name,hire_date from employees order by hire_date; （10） 查询所有员工的姓名及其直接上级的姓名。

Select a.first_name,b.first_name from employees a join employees b on b.employee_id = a.manage_id

（11） 查询入职日期早于其直接上级领导的所有员工信息。

select * from employees a where hire_date<(select hire_date from employees b

b.employee_id=a.manage_id)

（12） 查询各个部门号、部门名称、部门所在地以及部门领导的姓名。

Select d.department_id,d.department_name,d.location,e.first_name from departments d join employees e

on d.manager_id=e.employee_id

（13） 查询所有部门及其员工信息，包括那些没有员工的部门。

Select department_name,first_name from departments d left join employees e on d.deparment_id=e.department_id

（14） 查询所有员工及其部门信息，包括那些还不属于任何部门的员工。 Select e.first_name,d.department_name

From employees left join departments on e.department_id=d.department_id; （15） 查询所有员工的员工号、员工名、部门名称、职位名称、工资和奖金。 Select e.employee_id,e.first_name,d.department_name,j.job_title,e.salary, e.salary*e.commission_pct 奖金

From departments d join employees e on d.department_id=e.department_id Join jobs j on j.job_id=e.job_id;

（16） 查询至少有一个员工的部门信息。

select * from departments d where exists(select 1 from employees where department_id=d.department_id)

（17） 查询工资比100号员工工资高的所有员工信息。

Select * from employees where salary>(select salary from employees where employee_id = 100); （18） 查询工资高于公司平均工资的所有员工信息。

Select * from employees where salary>(select avg(salary) from employees) （19） 查询各个部门中不同职位的最高工资。 Select job_id,max(salary) from employees group by job_id （20） 查询各个部门的人数及平均工资

Select department_id,count(*),avg(salary ) from employees group by department_id;

（21） 统计各个职位的员工人数与平均工资。 Select job_id ,count(employee_id),avg(salary) from employees group by job_id;

（22） 统计每个部门中各职位的人数与平均工资。

Select department_id,job_id,count(*),avg(salary) from employees group by department_id,job_id;

（23） 查询最低工资大于5000元的各种工作。 Select job_id,job_title from jobs where job_id in(

Select job_id from employees group by job_id having min(salary)>5000); （24） 查询平均工资低于6000元的部门及其员工信息。

Select e.*,d.* from employees e join departments d on e.department_id=d.department_id and department_id in(select department_Id from employees group by employee_id having avg(salary)<6000);

（25） 查询在“Sales”部门工作的员工的姓名信息。

Select * from employee where department_id in(select department_d from departments where department_name=’Sales’)

（26） 查询与140号员工从事相同工作的所有员工信息。

Select * from employees where job_id in (select job_id from employees where employee_id = 140);

（27） 查询工资高于30号部门中所有员工的工资的员工姓名和工资。

Select first_name,last_name,salary from employees where salary>(select max(salary) from employees deparment_id=30);

（28） 查询每个部门中的员工数量、平均工资和平均工作年限。

Select count(*),avg(salary),avg(round((sysdate-hire_date)/365)) from employees group by department_id

（29） 查询工资为某个部门平均工资的员工的信息。

Select * from employees where salsry in(select avg(Salary) from employees group by department_id)

（30） 查询工资高于本部门平均工资的员工的信息。

Select * from employees e1 where salary>(select avg(salary) from employees e2 where e2.department_id=e1.department_id )

（31） 查询工资高于本部门平均工资的员工的信息及其部门的平均工资。 Select e.*,avgsal

From employees e join (select department_id,avg(salary) avgsal from employees group by department_id) d

On e.department_id=d.department_id And e.salary>d.avgsal

（32） 查询工资高于50号部门某个员工工资的员工的信息。

Select *from employees where salary>any(select salary from employees where department_id=50):

（33） 查询工资、奖金与10号部门某员工工资、奖金都相同的员工的信息。

create or replace trigger trg_after after update of salary on employees declare

v_maxsal employees.salary%type; v_minsal employees.salary%type; begin

select max(salary),min(salary) into v_maxsal,v_minsal from employees where department_id=pkg_10.v_deptno;

dbms_output.put_Line(v_minsal||' '||v_maxsal||pkg_10.v_newsal); if pkg_10.v_newsalv_maxsal then raise_application_error(-20001,'beyond'); dbms_output.put_Line('beyond'); end if; end;

（10）创建一个存储过程，以一个整数为参数，输出工资最高的前几个员工的信息。 Create or replace procedure pro_emp(p number) As

Cursor c_emp is

Select * from employee where salary is not null and rownum<=p order by salary desc; Begin

For v in v_Emp loop

Dbms_output.put_line(v.employee_id||v.salary||v.department_id); End loop; End;

第11章 PL/SQL高级应用

实训题

（1） 将employees表中所有员工信息存储到一个嵌套表变量中，输出该嵌套表中所

有工资大于10000元的元素。

DECLARE

CURSOR ALL_EMPS IS SELECT * FROM EMPLOYEES;

TYPE T_EMPNESTED IS TABLE OF EMPLOYEES%ROWTYPE; V_EMP T_EMPNESTED :=T_EMPNESTED(); INDEXVALUE BINARY_INTEGER :=1; BEGIN

FOR EMP IN ALL_EMPS LOOP V_EMP.EXTEND;

V_EMP(INDEXVALUE):=EMP; INDEXVALUE:=INDEXVALUE+1; END LOOP;

FOR I IN 1..V_EMP.COUNT LOOP IF V_EMP(I).SALARY>10000 THEN

DBMS_OUTPUT.PUT_LINE(V_EMP(I).EMPLOYEE_ID||''||V_EMP(I).FIRST_NAME ||’'||EMP(I).LAST_NAME||''||V_EMP(I).SALARY||'' ||V_EMP(I).DEPARTMENT_ID); END IF; END LOOP; END;

（2） 将departments表中所有部门信息存储到一个可变数组变量中，输出该可变数

组中元素的个数及所有元素。

DECLARE

CURSOR C_DEPT IS SELECT * FROM DEPARTMENTS;

TYPE T_DEPT IS VARRAY(200) OF DEPARTMENTS%ROWTYPE; V_DEPT T_DEPT :=T_DEPT();

INDEXVALUE BINARY_INTEGER :=1; BEGIN

FOR V IN C_DEPT LOOP V_DEPT.EXTEND;

V_DEPT(INDEXVALUE):=V; INDEXVALUE:=INDEXVALUE+1;

END LOOP;

FOR I IN 1..V_DEPT.LAST LOOP

DBMS_OUTPUT.PUT_LINE(V_DEPT(I).DEPARTMENT_ID||’ ‘||V_DEPT(I).DEPARTMENT_NAME||’ ‘||V_DEPT(I).LOCATION); END LOOP;

DBMS_OUTPUT.PUT_LINE(V_DEPT.COUNT); END;

（3） 创建一个存储过程，以表名和一个列名为参数，创建一个表。

CREATE OR REPLACE PROCEDURE CREATE_TABLE(

P_TABLENAME VARCHAR2, P_COL1 VARCHAR2, P_COL1_TYPE VARCHAR2) AS

V_CREATION VARCHAR2(100); BEGIN

V_CREATION:='CREATE TABLE '||P_TABLENAME||'('|| P_COL1||' '|| P_COL1_TYPE||' PRIMARY KEY'; EXECUTE IMMEDIATE V_CREATION; END;

（4） 创建一个存储过程，将某个表的SELECT权限授予某个用户。

CREATE OR REPLACE PROCEDURE GRANT_PRIV(

P_TABLENAME VARCHAR2,P_USERNAME VARCHAR2) AS

SQL_STR VARCHAR2(100); BEGIN

SQL_STR:='GRANT SELECT ON '||P_TABLENAME ||' TO '||P_USERNAME; EXECUTE IMMEDIATE SQL_STR; END;

（5） 创建一个存储过程，以员工号为参数删除该员工。

（6） 创建一个存储过程，参数为员工号、列名、值，实现对特定员工信息中特定列

信息的修改。

CREATE OR REPLACE PROCEDURE DYN_DML_TEST(

P_COL VARCHAR2, P_VALUE VARCHAR2,P_EMPNO NUMBER) IS

V_STR VARCHAR2(100); BEGIN

V_STR:='UPDATE EMP SET '||P_COL||

'=:PH_VALUE WHERE EMPLOYEE_ID=:PH_ EMPNO';

EXECUTE IMMEDIATE V_STR USING P_VALUE,P_EMPNO;

END;

（7） 创建一个存储过程，参数为列名、值，查询该员工信息。

CREATE OR REPLACE PROCEDURE QUERY_EMP( P_COL VARCHAR2, P_VALUE VARCHAR2) IS

V_FNAME EMPLOYEES.FIRST_NAME%TYPE; V_LNAME EMPLOYEES.LAST_NAME%TYPE; V_EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE; V_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE; V_SAL EMPLOYEES.SALARY%TYPE; V_STR VARCHAR2(200); BEGIN

V_STR:='SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID FROM EMPLOYEES WHERE '||P_COL ||'=:PH_VALUE';

EXECUTE IMMEDIATE V_STR INTO V_EMPNO,V_FNAME,V_LNAME,V_SAL,V_DEPTNO USING P_VALUE;

DBMS_OUTPUT.PUT_LINE(V_EMPNO||' '||V_FNAME||' '||V_LNAME||' '||V_SAL|| ' '||V_DEPTNO); END;

（8） 创建一个存储过程，参数为列名、值，根据部门号或部门名称查询部门信息。

CREATE OR REPLACE PROCEDURE QUERY_EMP( P_COL VARCHAR2, P_VALUE VARCHAR2) IS

V_DEPT DEPARTMTENTS%ROWTYPE; V_STR VARCHAR2(200); BEGIN

V_STR:='SELECT * FROM DEPARTMENTS WHERE '||P_COL ||'=:PH_VALUE'; EXECUTE IMMEDIATE V_STR INTO V_DEPT USING P_VALUE; DBMS_OUTPUT.PUT_LINE(V_DEPT.DEPARTMENT_ID||' '||

V_dept.department_name);

END;

（9） 创建一个存储过程，根据参数查询符合特定条件的员工信息（如根据工资查询、

部门号查询、入职时间等）。

CREATE OR REPLACE PROCEDURE MULTI_QUERY(

P_COL VARCHAR2,P_CONDITION VARCHAR2,P_VALUE VARCHAR2) AS

TYPE T_CURSOR IS REF CURSOR; V_CURSOR T_CURSOR;

LV_QUERY VARCHAR2(200);

V_EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE; V_FNAME EMPLOYEES.FIRST_NAME%TYPE; V_LNAME EMPLOYEES.LAST_NAME%TYPE; V_SAL EMPLOYEES.SALARY%TYPE;

V_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE; BEGIN

LV_QUERY:='SELECT MPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ ID FROM EMPLOYEES WHERE '||P_COL||P_CONDITION||':PH_VALUE'; OPEN V_CURSOR FOR LV_QUERY USING P_VALUE; LOOP

FETCH V_CURSOR INTO V_EMPNO,V_FNAME,V_LNAME,V_SAL,V_DEPTNO ; EXIT WHEN V_CURSOR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(V_EMPNO||' '||V_FNAME||' '||V_LNAME||' '||V_SAL|| ' '||V_DEPTNO); END LOOP; END;

（10）创建一个包，实现对HUMAN_RESOURCE数据库中数据进行分页查询。 略

第12章 安全管理

1．简答题

（1）

Oracle数据库的安全机制包括：用户管理、权限管理、角色管理、表空间设置和配额管理、用户资源限制以及数据库审计等。 （2）

Oracle数据库中权限有两类，系统权限和对象权限。系统权限是指在数据库级别执行某种操作的权限，或针对某一类对象执行某种操作的权限。对象权限是指对某个特定的数据库对象执行某种操作的权限。例如，对特定表的插入、删除、修改、查询的权限。 （3）

在Oracle数据库中，将权限授予用户有2种方法：直接授权，利用GRANT命令直接为用户授权；间接授权，先将权限授予角色，然后再将角色授予用户。 （4）

Oracle数据库中角色分为系统预定义的角色和用户自定义的角色两类。角色是一系列权限的集合，可以先将权限授予角色，然后将角色授予具有相同权限的用户。 （5）

系统权限授予与回收时不需要指定权限的所有者，因为系统权限是数据库级别的权限；而对象权限的授予与回收需要指定权限所依附的数据库对象。 （6）

Oracle数据库概要文件主要用来限制用户的数据库及系统资源的使用以及管理用户口令策略。

（7）数据库概要文件资源控制参数包括：

? CPU_PER_SESSION ：限制用户在一次会话期间可以占用的CPU 时间总量，单位为百

分之一秒。当达到该时间限制后，用户就不能在会话中执行任何操作了，必须断开连接，然后重新建立连接。

? CPU_PER_CALL：限制每个调用可以占用的CPU 时间总量，单位为百分之一秒。当一

个SQL语句执行时间达到该限制后，该语句以错误信息结束。 ? CONNECT_TIME：限制每个会话可持续最大时间值，单位为分钟。当数据库连接持续

时间超出该设置时，连接被断开。 ? IDLE_TIME：限制每个会话处于连续空闲状态最大时间值，单位为分钟。当会话空闲时

间超过该设置时，连接将被断开。 ? SESSIONS_PER_USER：限制一个用户打开数据库会话的最大数量。 ? LOGICAL_READS_PER_SESSION：允许一个会话读取数据块的最大数量，包括从内存中

读取的数据块和从磁盘中读取的数据块的总和。 ? LOGICAL_READS_PER_CALL ：允许一个调用读取的数据块的最大数量，包括从内存中读

取的数据块和从磁盘中读取的数据块的总和。

? PRIVATE_SGA：在共享服务器操作模式中，执行SQL语句或PL/SQL程序时，Oracle将在SGA中创建私有SQL区。该参数限制在SGA中一个会话可分配私有SQL区的最大值。

? COMPOSITE_LIMIT ：称为“综合资源限制”，是一个用户会话可以消耗的资源总限额。

该参数由CPU_PER_SESSION、 LOGICAL_READS_PER_SESSION、 PRIVATE_SGA、 CONNECT_TIME几个参数综合决定。

（8）概要文件中的口令管理参数包括：

? FAILED_LOGIN_ATTEMPTS：该参数限制用户在登录Oracle数据库时允许失败的次数。

一个用户尝试登录数据库的次数达到该值时，该用户的账户将被锁定，只有解锁后才可以使用。

? PASSWORD_LOCK_TIME：该参数用于设定当用户登录失败后，用户账户被锁定的时

间长度。

? PASSWORD_LIFE_TIME：该参数用于设置用户口令有效天数。达到限制的天数后，

该口令将过期，需要设置口令。

? PASSWORD_GRACE_TIME：用于设定在PASSWORD_LIFE_TIME过期后一个天数。在

这几天中，用户将接收到一个关于口令过期需要修改口令的警告。当达到规定的天数后，原口令过期。 ? PASSWORD_REUSE_MAX：该参数用于指定一个用户口令被修改后，必须经过多少天

后才可以重新使用该口令。

? PASSWORD_REUSE_TIME：该参数用于指定一个口令被重新使用前，必须经过多少

次修改。

? PASSWORD_VERIFY_FUNCTION：设置口令复杂性校验函数。这个函数会对口令进行校

验，以判断口令是否符合最低复杂程度或其他校验规则。

（9）

审计是监视和记录用户对数据库所进行的操作，以供DBA进行统计和分析。利用审计可以调查数据库中的可疑活动、监视和收集特定数据库活动的数据。 （10）

? 语句审计（Statement Auditing）：对特定的SQL语句进行审计，不指定具体对象。 ? 权限审计(Privilege Auditing)：对特定的系统权限使用情况进行审计。

? 对象审计(Object Auditing)：对特定的模式对象上执行的特定语句进行审计。 ? 网络审计（Network Auditing）：对网络协议错误与网络层内部错误进行审计。

2．实训题

（1）

CREATE USER usera_exer IDENTIFIED BY usera DEFAULT TABLESPACE USERS QUOTA 10M ON USERS ACCOUNT LOCK;

（2）

CREATE USER userb_exer IDENTIFIED BY userb; （3）

GRANT CREATE SESSION TO usera_exer WITH ADMIN OPTION;

GRANT SELECT ,UPDATE ON ehr.employees TO usera_exer WITH GRANT OPTION; （4）

ALTER USER usera_exer ACCOUNT UNLOCK; （5）

CONNECT usera_erer/usera SELECT * FROM ehr.employees;

UPDATE ehr.employees SET salary=salary+100 ;

GRANT SELECT ,UPDATE ON ehr.employees TO userb_exer; （6）

REVOKE CREATE SESSION FROM usera_exer; GRANT CREATE SESSION TO usera_exer; （7）

REVOKE SELECT,UPDATE ON ehr.employees FROM usera_exer; GRANT SELECT ,UPDATE ON ehr.employees TO usera_exer; （8）

CREATE ROLE rolea; CREATE ROLE roleb;

GRANT CREATE TABLE TO rolea;

GRANT INSERT,UPDATE ON ehr.employees TO rolea; GRANT CONNECT ,RESOURCE TO roleb; （9）

GRANT rolea,roleb TO usera_exer; （10）

CREATE PROFILE pwdfile

LIMIT CONNECT_TIME 30 IDLE_TIME 10 FAILED_LOGIN_ATTEMPTS PASSWORD_LIFE_TIME 20 PASSWORD_LOCK_TIME 10

ALTER USER usera_exer PROFILE pwdfile;

3．选择题

（1） A （2） B （3） A

4

（4） A （5） B （6） A （7） D （8） D （9） D （10） D

第13章 备份与恢复

1．简述题

（1）

在以数据库为数据管理中心的信息系统中，由于数据库故障而导致业务数据部分或全部丢失、系统运行失败的情况时有发生。因此，如何有效地预防数据库故障的发生以及在数据库发生故障后如何快速、有效地恢复数据库系统是数据库管理员的重要任务，其解决方法就是合理制定数据库的备份与恢复策略，执行有效的数据库备份与恢复操作。 （2）

备份：

? 物理备份与逻辑备份 ? 热备份与冷备份 ? 完全备份与部分备份 ? 归档备份与非归档备份

恢复：

? 物理恢复与逻辑恢复 ? 完全恢复与部分恢复

（3）

在SQL Plus环境中进行数据库完全热备份的步骤为： ? 启动SQL Plus，以SYSDBA身份登录数据库。 ? 将数据库设置为归档模式。

? 以表空间为单位，进行数据文件备份 ? 备份控制文件。

? 归档当前的联机重做日志文件。

? 备份归档日志文件，将所有的归档日志文件复制到备份磁盘中。

? 备份初始化参数文件，将初始化参数文件复制到备份磁盘中 （4）

可以进行数据库的完全恢复与不完全恢复。 完全恢复：

? 基于数据文件的完全恢复：某个数据文件损坏。

? 基于表空间的完全恢复：一个表空间中的多个数据文件损坏。

? 基于数据库的完全恢复：数据库多个表空间中的多个数据文件损坏。 不完全恢复：

? 基于时间的不完全恢复 ? 基于SCN的不完全恢复 ? 基于CANCEL的不完全恢复 （5）

? 配置恢复目录 ? 配置通道 （6）

与物理备份与恢复不同，逻辑备份与恢复必须在数据库运行的状态下进行，当数据库发生介质损坏而无法启动时，不能利用逻辑备份恢复数据库。因此，数据库备份与恢复是以物理备份与恢复为主，逻辑备份与恢复为辅的。

2．实训题

略

3．选择题

（1） （2） （3） （4） （5） （6） （7） （8） （9） （10） （11）

E AD A D AD CD A BC C ACF B

第14章 闪回技术

1．简答题

（1）

传统的数据库恢复技术的解决的方法是利用预先做好的数据逻辑备份或物理备份进行恢复，而且恢复的程度取决于备份与恢复的策略。

闪回技术是利用数据库内部的设置，利用撤销表空间中的回退信息、回收站、闪回恢复区等信息，在数据库正常运行的状态下即可进行相应的恢复操作。 （2）

闪回查询是查询过去某一个SCN值或时间点的表中数据的状态。

闪回版本查询是查询过去某个SCN值端或时间段表中数据的变幻状态，即不同的版本。 （3）

通常，将闪回事务查询与闪回版本查询相结合，先利用闪回版本查询获取事务ID及事务操作结果，然后利用事务ID查询事务的详细操作信息。最后，进行是操作的逆向操作恢复数据。 （4）

为了使用闪回表功能，必须满足下列条件： ? 用户具有FLASHBACK ANY TABLE系统权限，或者具有所操作表的FLASHBACK对象权

限；

? 用户具有所操作表的SELECT、INSERT、DELETE、ALTER对象权限；

? 数据库采用撤销表空间进行回退信息的自动管理，合理设置UNDO_RETENTION参

数值，保证指定的时间点或SCN对应信息保留在撤销表空间中； ? 启动被操作表的ROW MOVEMENT特性。 （5）

在Oracle 11g数据库中，要使用闪回数据库技术，需要满足下列4个条件： ? 配置了数据库的快速恢复区； ? 数据库必须运行在归档模式下；

? 在数据库加载状态下启用数据库的FLASHBACK特性； ? 设置数据库参数DB_FLASHBACK_RETENTION_TARGET。 （6）

默认情况下，数据库中所有表的闪回数据归档特性都没有启用。如果要为一个表启用闪

回数据归档特性，必须满足下列的要求：

? 用户在表使用的闪回数据归档区上具有FLASHBACK ARCHIVE的对象权限； ? 表不能是嵌套表、聚簇表、临时表、远程表或外部表； ? 表不能包含LONG类型列和嵌套表类型列。

2．实训题 （1）

SQL>SHOW PARAMETER UNDO

查看 UNDO_MANAGEMENT、UNDO_TABLESPACE、UNDO_RETENTION参数设置，进行合理配置。 （2）

SQL>SHOW PARAMETER RECYCLEBIN

如果参数RECYCLEBIN值为OFF，需要修改为ON （3）

? 检查归档配置 SQL>archivelog list

? 启用数据库的FLASHBACK特性 SQL>ALTER DATABASE FLASHBACK ON; ? 检查是否设置闪回恢复区

SQL>SHOW PARAMETER DB_RECOVERY_FILE （5）

Select * from exercise as of to_timestamp(‘2013-3-12 15:40:10’,’yyyy-mm-dd hh24:mi:ss); （6）

SELECT * FROM exercise

VERSIONS BETWEEN TIMESTAMP to_timestamp(‘2013-3-12 15:35:10’,’yyyy-mm-dd hh24:mi:ss) AND TIMESTAMP to_timestamp(‘2013-3-12 15:42:10’,’yyyy-mm-dd hh24:mi:ss) WHERE sno=100; （7）

FLASHBACK TABLE exericse TO TIMESTAMP

TO_TIMESTAMP('2013-3-12 15:40:10','YYYY-MM-DD HH24:MI:SS'); （8）

FLASHBACK TABLE exercise TO BEFORE DROP; （9）

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 （10）

SHUTDOWN IMMEDIATE STARTUP MOUNT EXCLUSIVE

FLASHBACK DATABASE TO TIMESTAMP(TO_TIMESTAMP( '2013-3-12 15:40:10', 'YYYY-MM-DD HH24:MI:SS')); ALTER DATABASE OPEN RESETLOGS;

2．选择题

（1）ABD （2）CD （3）BE （4）B （5）AB （6）A （7）BCE （8）A （9）DE （10）D （11）A

第15章基于Oracle数据库的应用开发

略

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