OCP认证考试指南全册OracleDatabase11g练习与知识点

OCP/OCA

认证考试指南全册

Oracle Database 11g（1Z0-051,1Z0-052,1Z0-053）

练习与知识点

Author：Deerfer@20140630

1 / 74

第一章 Oracle Database 11g体系结构概述

本章学习内容

 解释内存结构

 描述进程结构

 讲述存储结构

练习1-1 研究所在环境的DRMS

确定自己所在的环境使用的应用程序、应用服务器和数据库服务器。然后集中精力研究数据库，体验一下数据库的规模和忙碌程度。考虑用户数量、数据易失性以及数据量。最后考虑它们对组织的重要程度：就每个应用程序和数据库而言，允许多长的停机时间？允许损失多少数据？可以使用财务数字定量分析吗？

利用研究的结果，可以了解DBA角色的重要性。

应用程序：MDS计量生产调度平台

应用服务器：Oracle WebLogic Server 11g

数据库服务器：Oracle Database 11g

数据库规模：数据量大小、数据库对象多少、用户数、访问并发量、数据库节点格式（RAC环境）

平均无故障时间 MTBF Mean Time Between Failure

平均故障修复时间 MTTR Mean Time To Repair

练习1-2 确定数据库是单实例还是分布式系统的一部分

运行查询来确定数据库是独立系统，还是更大的分布式环境的一部分。

（1） 作为用户SYSTEM连接到数据库。

（2） 确定实例是否为RAC数据库的一部分：

SELECT * FROM v$instance;

SELECT parallel FROM v$instance; -- NO：单实例数据库 YES：RAC数据库

（3） 确定数据库是否通过备用数据库的保护来防止数据丢失：

SELECT * FROM v$database;

SELECT protection_level FROM v$database; --UNPROTECTED数据库未受到保护

（4） 确定是否数据库中配置了流：

SELECT * FROM dba_streams_administrator; --如果尚未配置流，那么将不返回任何行

Tips 1：Oracle Stream

2 / 74

Oracle Stream功能是为提高数据库的高可用性而设计的，在Oracle 9i及之前的版本这个功能被称为Advance Replication。Oracle Stream利用高级队列技术，通过解析归档日志，将归档日志解析成DDL及DML语句，从而实现数据库之间的同步。这种技术可以将整个数据库、数据库中的对象复制到另一数据库中，通过使用Stream的技术，对归档日志的挖掘，可以在对主系统没有任何压力的情况下，实现对数据库对象级甚至整个数据库的同步。

练习1-3 了解实例的内存结构

运行查询来确定构成实例的不同内存结构的当前大小。

（1） 作为用户SYSTEM连接到数据库。

（2） 显示可以动态重设大小的SGA组件的当前、最大和最小的容量：

SELECT component, current_size, min_size, max_size

FROM v$sga_dynamic_components;

查询结果如下：

显示的实例不包括流，因此流池的大小为零。自从启动实例后，大池和Java池都未发生变化，但共享池和数据库缓冲区缓存的大小发生了变化。仅配置了数据库缓冲区缓存的默认池，除进行大量调整的数据库外，通常都是这样的配置。

（3） 确定已经（以及当前为）程序全局区分配了多少内存：

SELECT NAME, VALUE FROM V$PGASTAT

WHERE NAME IN ('maximum PGA allocated', 'total PGA allocated');

练习1-4 了解在实例中运行的进程

将运行查询来查看在实例中运行的后台进程。

（1） 作为用户SYSTEM了解到数据库。

（2） 确定哪些进程正在运行，以及每个进程的数量有多少：

SELECT program FROM v$session ORDER BY program; --有哪些进程

SELECT program FROM v$process ORDER BY program; --每个进程的数量

这些查询将得到相似结果：每个进程必须有会话（即使后台进程，也同样如此），而每个会话必须有进程。可多次出现的进程将有个数据后缀，但支持用户会话的进程除外：它们都使用同一个名称。

查询结果如下：

(CJQ0)

3 / 74

(CKPT)

(DBW0)

(J000)

(LGWR)

(MMAN)

(MMNL)

(MMON)

(PMON)

(PSP0)

(QMNC)

(RECO)

(SMON)

(q000)

(q001)

Tips 2：Oracle进程简要说明

（1） CJQ：Job queue，oracle的JOB进程，停止后JOB会停止运行

（2） CKPT：checkpoint，检查点进程，CKPT和LGWR这两个进程与DBWn进程互相合作， 提供了既安全又高效的写脏数据块的解决方案。

（3） DBW：DBWR，database writer,就是写数据文件的进程。

（4） J000：ora_jxxx是系统job进程，可以用select * FROM

DBA_JOBS_RUNNING查询。

（5） LGWR：Log Writer，将日志缓冲区中的所有记录项写到日志文件中

（6） MMAN ：Memory manager，内存管理进程，如果设定了SGA自动管理，MMAN用来协调SGA内各组件的大小设置和大小调整

（7） MMNL：Memory Monitor Light，用于填充自动工作负载存储库（Automatic

Workload Repository，AWR）MMNL进程会根据调度从SGA将统计结果刷新输出至数据库表。

（8） MMON：Manageability Monitor，MMON从SGA定期捕获统计数据(默认是每小时一次)，并将它们写入到数据字典中，在数据字典中，可以无限期地存储它们(不过，默认方式是只存储8天)。

（9） PMON：Process Monitor，进程监控器，主要负责连接非正常中断后的清除工作,PMON负责释放相应的资源。

（10） PSP0：启动其它的Oracle进程（10g新增加的后台进程）。

（11） QMNC：监视高级队列,并警告从队列中删除等待消息的出队进程

（12） RECO ：Distributed Database Recovery，负责在分布式数据库环境中自动恢复那些失败的分布式事务,保证分布式事务的一致性,在分布式事务中,要么同时commit,要么同时rollback;

（13） SMON：System Monitor，系统监控进程，负责在实例启动时执行实例恢复，并清理不再使用的临时段。

（3） 通过计算服务器进程数量（Linux或Unix平台上）或Oracle线程数量（Windows上），演示会话生成后启动的服务器进程。

（a） 在Linux上，从操作系统提示符可以运行以下命令：

ps –ef|grep oracle|wc -1

该语句将计算出名称中包含“Oracle”字符串的运行进程的数量，将包括所有会话服务器进程（或许还包括其它进程）。

（b） 在Windows上，启动任务管理器。对其进行配置，以便显示每个进程中的线程数量。

4 / 74

练习1-5 了解数据库的存储结构

在本练习中，将创建一个表段，然后计算出它的物理位置。

（1） 创建一个未确定表空间的表—将在默认表空间中创建它，并包含一个区间：

CREATE TABLE tab24 (c1 VARCHAR2(10));

（2） 确定表所在的表空间、区间大小、区间所在的文件编号、以及作为区间开始位置的文件块：

SELECT TABLESPACE_NAME, EXTENT_ID, BYTES, , BLOCK_ID

FROM DBA_EXTENTS

WHERE OWNER = 'SYS'

AND SEGMENT_NAME = 'TAB24';

查询结果如下：

（3） 根据名称确定文件：在看到提示时，替代前面查询中的：

SELECT NAME FROM v$data file# = &;

查询结果如下：

（4） 精确计算出区间在文件中的位置（按它在文件中的开始字节数）。这要求找到表空间的块大小。在看到提示时输入（2）中查询返回的block_id和tablespace_name.

SELECT BLOCK_SIZE * &BLOCK_ID/1024/1024

FROM DBA_TABLESPACES

WHERE TABLESPACE_NAME = '&tablespace_name';

查询结果如下：

由此可见，表存放于一个64KB的区间中。此区间位于文件“D:”中，从文件的大约478MB处开始。

本章知识点回顾

单实例体系结构

 Oracle服务器是连接到数据库的实例。

 实例包括共享存储块以及一组后台进程。

 数据库是磁盘上的一组文件。

 用户会话是连接到数据库服务器进程的用户进程。

实例内存结构

 实例共享内存是系统全局区（SGA）

5 / 74

 会话的专用区域是程序全局区（PGA）

 SGA由一组子结构构成，其中一些是必须的（数据库缓冲区缓存、日志缓冲区和共享池），而另一些是可选的（大池、Java池和流池）。

 可以动态重调SGA结构的大小，也可对其进行自动化管理，但日志缓冲区除外。

实例进程结构

 当用户连接时，将根据需要启动会话服务器进程。

 后台进程在启动实例时启动，在关闭实例前一直存在。

 服务器进程从数据库读取，后台进程对数据库执行写操作。

 某些后台进行始终存在（特别是SMON、PMON、DBWn、LGWR、CKPT和MMON），而其它进程则根据启动的选项进行。

数据库存储结构

 数据库中有三类必须的文件：控制文件、联机重做日志文件和数据文件。

 控制文件存储完整性信息以及指向数据库其它部分的指针。

 联机重做日志存储应用于数据库的最新变更向量。

 数据文件存储数据。

 外部文件包括：参数文件、口令文件、归档重做日志以及日志和跟踪文件。

 表空间将逻辑数据存储（段）从物理数据存储（数据文件）中抽象出来。

 表空间可有多个数据文件组成。

 一个段包含多个区间、一个区间包含多个Oracle块、一个Oracle块包含多个操作系统块。

 一个段可以包含位于多个数据文件的区间。

6 / 74

第二章 安装和创建数据库

本章学习内容









了解Oracle数据库管理工具

规划Oracle数据库的安装

使用OUI安装Oracle软件

使用DBCA创建数据库

练习2-1 在Windows计算机上安装SQL Developer

在本练习中，将在Windows计算机上安装SQL Developer。

（1） 下载安装SQL Developer

（2） 从命令行提示窗口运行 可执行文件，确定完成安装。

练习2-2 确认可用的硬件资源

在本练习中，将先后针对Windows和Linux检查哪些可用的资源。

Windows：

（1） 右键My Computer图标，打开Properties对话框。查看RAM容量。RAM至少为512MB，首选容量是1GB。

（2） 选择Advanced选项卡，此后，在Performance部分单击Settings按钮。

（3） 在Performance Options对话框中，选择Advanced选型卡。查看虚拟内存设置。这至少应为步骤（1）中报告的内存量的1.5倍。

（4） 打开命令窗口，使用以下命令找到临时数据目录的位置：

C:> echo %TEMP%

这将返回与下面类似的位置：

C: Temp

确认返回的文件系统（在本例中是驱动器C：）至少具有400MB的可用空间。

（5） 确定文件系统具有用于Oracle主目录和数据库的5GB空间。这必须是本地磁盘，不能是文件服务器。如果要复杂媒介安装程序（你很可能这样做吧），还需要另外1.5GB（可以在文件服务器上）。

Linux：

（1） 在操作系统提示符下，运行free命令来显示主存和交换空间（理想情况下，至少为1GB）。这些都是total列的值。

（2） 运行df –h，显示每个已安装文件系统的可用空间。确认具有用于Oracle主目录和数据库的5GB可用空间的文件系统。确认/tmp中有400MB的可用空间（如果它作为独立文件系统存在）

（3） 使用rmp来检查是否已经安装了所有需要的包（正确版本和更新版本）。

rpm –qa|grep sysstat

7 / 74

（4） 使用sysctl来检查是否已经安装了所有需要的内核设置，要执行此操作，必须拥有root用户权限。

sysctl –a|grep ip_loca_port_range

练习2-3 安装Oracle主目录

使用OUI在Linux上安装Oracle主目录。

（1） 以dba成员组的身份登录到Linux。在下面的练习中，用户是db11g。使用id命令确认用户名和组成员，如图所示：

（2） 使用su切换到root用户，并使用mkdir命令为Oracle Base创建一个符合OFA的目录。在本例中，这是/u02/app/db11g。使用chown和chmod命令。更改目录的所有权和访问模式，使Oracle用户对其拥有完全控制权利，然后退回到Oracle用户。

（3） 如果使用的不是控制台计算机，请将DISPLAY变量设置为指向正在使用的计算机上的

（4） …

练习2-4 使用DBCA创建一个数据库

在本练习中，将创建数据库监听器（如果还没有的话），然后在Windows或Linux上使用DBCA创建名为ocp11g的数据库。平台组件没有明显差别。

Tips 3：Oracle几个默认的用户

（1）

（2）

（3）

（4）

SYS用户，拥有数据字典

SYSTEM，用于大多数DBA工作

DBSNMP，用于外部监视

SYSMAN，供Enterprise Manager使用

本章知识点回顾

了解Oracle数据库管理工具

 安装：OUI

 数据库创建和升级：DBCA、DBUA

 发送即席SQL：SQL*Plus、SQL Developer

 备份：RMAN、Oracle Secure Backup

 网络管理：Oracle Net Manager、Oracle Net Configuration Assistant

 加载和卸载数据的实用程序： Data Pump、SQL*Loader

 管理：Oracle Enterprise Manager、Database Control、Grid Control

规划Oracle数据库的安装

硬件要求

 磁盘空间

 主存

✓ 交换空间

8 / 74

✓ 临时空间

✓ 图形终端

操作系统要求

 认证的版本

 必须的包

 内核设置

OFA：适当的Oracle Base目录

使用OUI安装Oracle软件

 使用适当的操作系统用户

 设置必须的环境变量（Linux、Unix）

 提供对root用户账户的访问（Linux、Unix）

 执行交换式或无提示安装

使用Database Configuration Assistant 创建数据库

 可用使用DBCA或SQL*Plus命令行创建数据库

 DBCA可以基于保存的模板创建数据库

 DBCA和SQL*Plus命令可以删除数据库

 在创建数据库之前，必须首先创建实例

 对于在创建时未选中的任何选项，可以再以后予以添加

9 / 74

第三章 实例管理

本章学习内容









设置数据库初始化参数

描述启动和关闭数据库时的多个阶段

使用警报日志和跟踪文件

使用数据字典和动态性能视图

练习3-1 查询和设置初始化参数

本练习将使用SQL*Plus或SQL Developer来管理初始化参数。

（1） 以用户SYS的身份（具有SYSDBA权限）连接到数据库（必须处于打开状态）。使用操作系统身份验证或口令文件身份验证。

（2） 显示所有基础参数，检查它们已被设置了适当的值，还是使用默认值。

SELECT name,value,isdefault FROM v$parameter WHERE isbasic='TRUE'

ORDER BY name;

（3） 读者应该对具有默认值的基本参数研究一番，看默认值是否合适。实际上，应该考虑所有的基本参数。

（4） 将PROCESSES参数更改为200。这是一个静态参数，意味着其值不能在内存中更改并立即生效。必须在静态pfile中设置它，或者如果使用spfile，可以通过指定“scope=spfile”然后重启数据库进行设置。

ALTER SYSTEM SET processes=200; --静态参数 执行报错

ALTER SYSTEM SET processes=200 scope=SPFILE; --需要重启数据库使其生效

（5） 重新运行步骤（3）的查询。注意PROCESSES和SESSIONS的新值。PROCESSES限制允许连接到实例的的操作系统进程数量，SESSION限制会话数量。这些数字是相关的，因为每个会话后需要进程。SESSION的默认值从PROCESSES派生出来，因此，如果SESSIONS采用默认值，那么此时将有一个新值。

（6） 为会话更改NLS_LANGUAGE参数的值。选择需要的主流语言（Oracle支持多种语言：截止到目前，支持67种语言），当必须使用英文单词指定语言（例如，使用German，而非Deutsch）。

ALTER SESSION SET nls_language=German;

（7） 通过查询系统日期确认更改已经生效。

SELECT to_char(SYSDATE,'day') FROM dual;

查询结果如下：

用户可能需要使用另一个ALTER SESSION 命令，将会话语言改回以前的语言，否则在要准备处理的会话正使用的语言的错误信息。

（8） 更改OPTIMIZER_MODE参数，当仅将作用域限制为运行中的实例，不要更新参10 / 74

数文件。本练习启用了被启用的基于规则的优化器（可用于测试一些旧代码）

ALTER SYSTEM SET optimizer_mode = RULE SCOPE=MEMORY;

（9） 确认更改已经生效，但没有写到参数文件中

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'optimizer_mode'

UNION

SELECT VALUE FROM V$SPPARAMETER WHERE NAME = 'optimizer_mode';

查询结果如下：

（10） 在运行的实例中，将OPTIMIZER_MODE恢复为标准值

ALTER SYSTEM SET optimizer_mode = ALL_ROWS SCOPE = MEMORY;

练习3-2 启动和关闭数据库

这个练习使用SQL*Plus启动一个实例并打开一个数据库，然后使用Database Control将其关闭。如果数据库已经处于打开状态，则以其它顺序执行，注意，在windows系统中，需要确认运行了数据库对应的Windows服务。其名称的格式为OracleServiceSID，其中的SID为实例名称。

（1） 以拥有ORACLE_HOME的操作系统组成员的身份登录计算机，并为ORACLE_HOME、PATH和ORACLE_SID合理设置环境变量。

（2） 检查数据库侦听器的状态，如有必要，将其启动。在操作系统命令指示窗口中输入如下命令：

lsnrctl status

lsnrctl start

（3） 检查Database Control控制台的状态。如有必要，将其启动。在操作系统命令提示窗口中输入如下命令：

emctl status dbconsole

emctl start dbconsole

（4） 使用/nolog 开关启动SQL*Plus，已阻止将立即出现的登录提示。

sqlplus /nolog

（5） 使用操作系统身份验证，以SYS的身份连接数据库。

connect / as sysdba

（6） 只启动实例。然后查询V$INSTANCE视图并检查其STATUS列。注意实例的状态是启动“STARTED”

startup nomount;

select status from v$instance;

（7） 加载数据库并查询实例状态。现在数据库已被实例进行了“加载 mount”。

alter database mount;

select status from v$instance;

（8） 打开数据库

alter database open;

（9） 通过查询V$INSTANCE确认已经打开了数据库。数据库现在的状态应该为“OPEN”

select status from v$instance;

（10） 通过浏览器了解到Database Control控制台。主机名和端口将显示在步骤（3）中11 / 74

emctl status dbconsole命令的输出结果中。URL将类似于lhost:port/em，端口号一般为1158.

（11） 以SYS身份，使用创建数据库时选择的口令登录，并从Connect AS下拉框中选择SYSDBA。

（12） 在数据库主页中单击Shutdown按钮。

练习3-3 使用警报日志

在本练习中，将定位警报日志，并找到练习3-1中的参数更改条目和练习3-2中的启动和关闭条目。

（1） 使用SQL*Plus或SQL Developer连接到数据库，找到BACKGROUND_DUMP_DEST参数的值。

SELECT VALUE FROM v$parameter WHERE NAME = 'background_dump_dest';--告警日志存储路径

查询结果如下：

注意，也可以在Database Control 中找到此值。

（2） 定位到上面的目录

（3） 打开警报日志。此文件称为alert_，其中SID是实例名。

（4） 在文件结尾处，看到练习3-1中的ALTER SYSTEM命令以及启动和关闭结果。

练习3-4 查询数据字典和动态性能视图

在本练习中，通过查询视图来了解数据库的物理结构。

（1） 使用SQL*Plus或SQL Developer连接到数据库

（2） 使用动态性能视图来确定哪些数据文件和表空间构成了数据库以及数据文件的大小。

12 / 74

SELECT , , FROM V$TABLESPACE T

JOIN V$DATAFILE D ON # = #

ORDER BY ;

查询结果如下：

（3） 从数据字典视图中获取同一信息。

SELECT tablespace_name, , BYTES FROM dba_data_files ORDER BY

tablespace_name;

查询结果如下：

（4） 确定所有controlfile副本的位置。使用两种技术：

SELECT * FROM v$controlfile;

查询结果：

SELECT VALUE FROM v$parameter WHERE NAME = 'control_files';

查询结果：

（5） 确定联机重做日志文件成员的位置及大小。由于大小是组的特性，并非成员的特性，需要将两个视图连接在一起。

SELECT #, , /1024/1024

FROM v$log g

JOIN v$logfile m

ON # = #

ORDER BY #, ;

查询结果：

13 / 74

本章知识点回顾

描述启动和关闭数据库时的多个阶段

 包括三个阶段：NOMOUNT、MOUNT和OPEN

 NOMOUNT模式需要参数文件

 MOUNT模式需要控制文件

 OPEN模式需要数据文件和联机重做日志文件

设置数据库初始化参数

 不结果关闭/启动过程，就不能更改静态参数文件

 可以针对实例或会话，以动态方式更改其它参数

 可以在动态性能视图V$PARAMETER和V$SPPARAMETER中查看参数

使用警报日志和跟踪文件

 警报日志是有关关键操作的连续消息流

 跟踪文件通常是由于在后台进程中遇到错误生成的

使用数据字典和动态性能视图

 动态性能视图使用实例或控制文件的信息填充

 数据字典视图使用数据字典的信息填充

 动态性能视图汇聚实例整个生命周期的值，在启动时重新初始化

 数据字典视图显示关闭和启动期一直持久保存的信息

 数据字典视图和动态性能视图都通过同义词来发布

14 / 74

第四章 配置Oracle网络环境

本章学习内容

 配置和管理Oracle网络

 使用Oracle共享服务器体系结构

练习4-1 配置Oracle Net

在本练习中，将使用图形化工具和命令行工具来创建一个完整的Oracle Net环境。由此，读者可以看出在Windows和Linux系统中的区别。

（1） 创建Oracle Net配置文件使用的目录，并设置指向此位置的TNS_ADMIN变量。目录的位置无关紧要，只要Oracle用户有权执行创建、读取和写入即可。

在Linux系统中：

mkdir /01/oracle/net --创建文件路径

export TNS_ADMIN=/u01/oracle/net --设置环境变量

确保从现在开始，都从设置了变量的会话中执行所有工作。

在Windows系统中：

mkdir d:oraclenet

创建注册表项TNS_ADMIN，并将其设置为Oracle Home分支的注册表中的字符串变量。这通常是：

HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_OraDb11g_home1

（2） 通过从操作系统提示符中运行TNSPING命令来检查是否读取了变量：

tnsping orcl

15 / 74

（3） 启动Net Manager。

在Linux系统中，从操作系统提示符中运行netmgr。在Windows系统中，从“开始”菜单启动它。Net Manager 窗口最上面的行将显示Oracle Net 文件的位置。如果这不是新目录，则TNS_ADMIN变量设置的不正确。

（4） 创建新侦听器：展开导航树的Local分支，突出显示Listeners项，然后点击“+”16 / 74

图标。

（5） 输入侦听器名“NEWLIST”，然后单击OK按钮。

（6） 单击Add Address按钮。

（7） 对于Address1，选择“TCP/IP”作为协议，并输入“127.0.0.1”作为主机，“1521”作为端口。

（8） 创建新服务名：突出显示导航树的Service Naming项，然后单击“+”图标。

（9） 输入“NEW”作为新服务名，然后单击Next按钮。

（10） 选择“TCP/IP”作为协议。然后单击Next按钮。

（11） 输入“127.0.0.1”作为主机名，“1521”作为端口，然后单击Next按钮

（12） 输入“SERV1”作为服务名，然后单击Next按钮

（13） 单击Finish按钮。如果尝试进行尝试，将显示失败

（14） 通过单击 Network Configuration菜单项保存配置。这将在TNS_ADMIN目录中创建文件盒文件。

（15） 使用编辑器检查这两个文件。

文件如下：

# Network Configuration File:

D:oracleproduct10.2.0db_

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ChuPengfei-PC)(PORT = 1521))

)

文件如下：

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ChuPengfei-PC)(PORT = 1521))

(CONNECT_DATA =

17 / 74

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

（16） 从操作系统提示符中使用lsnrctl start listener 启动监听器。

（17） 在操作系统提示符中使用tnsping orcl尝试连接字符串。

（18） 使用sqlplus / as sysdba，经操作系统省份验证连接到数据库，忽略任何监听器。

（19） 为正在运行的实例设置service_names参数和local_listener参数（仅限于内存，不在参数文件中设置），并向新侦听器注册新服务名：

ALTER SYSTEM SET service_names=sercvl SCOPE=MEMORY;

ALTER SYSTEM SET local_listener=NEW SCOPE=MEMORY;

ALTER SYSTEM REGISTER;

（20） 在操作系统提示符下，使用lsnrctl services newlist，确认已向新侦听器注册了新服务。

（21） 通过使用以下代码登录，确认新网络环境已生效：

sqlplus system/oracle@new

练习4-2 设置共享的服务器环境

本练习是【练习4-1】中步骤（21）的延续，用户需要配置共享服务器。并证实其可以运行。

（1） 设置dispatchers参数和share_servers参数，并注册到侦听器，命令如下：

ALTER SYSTEM SET dispatchers=’(protocol=tcp)(dispatchers=2)’

SCOPE=MEMORY;

ALTER SYSTEM SET shared_servers=4 SCOPE=MEMORY;

ALTER SYSTEM REGISTER;

（2） 通过查询视图V$PROCESS，确认调度程序和共享服务器已经启动、查找名为S000、S001、S003、D000、D001的进程

SELECT program FROM v$process ORDER BY program;

（3） 在操作系统提示窗口，确认调度程序已经注册到侦听器。

lsnrctl services listener

18 / 74

（4） 通过侦听器进行了解，并确认通过共享服务器机制执行了连接。

SELECT ,

FROM v$dispatcher d, v$shared_server s, v$circuit c

WHERE = cher

AND = ;

（5） 还原到最初的配置以清理环境。

ALTER SYSTEM SET local_listener='' SCOPE=MEMORY;

ALTER SYSTEM SET service_names='' SCOPE=MEMORY;

ALTER SYSTEM SET dispatchers='' SCOPE=MEMORY;

ALTER SYSTEM SET shared_servers=0 SCOPE=MEMORY;

ALTER SYSTEM REGISTER;

（6） 在操作系统提示窗口，使用命令停止侦听器

lsnrctl stop listener

（7） 取消设置TNS_ADMIN变量。在linux系统中，采用export TNS_ADMIN=’’；在Windows系统中，删除TNS_ADMIN注册表项。

本章知识点回顾

配置和管理Oracle网络

 服务器段文件是和（可选）

 客户端文件是和（可选）

 Oracle Net文件位于ORACLE_HOME/network/admin，或位于TNS_ADMIN变量指向的目录。

 名称解析可以本地执行（使用文件），也可以在中心位置执行（使用LDAP

目录）

 Easy Connect不需要任何名称解析

 一个侦听器可以侦听多个数据库

19 / 74

 多个侦听器可以连接一个数据库

 可以使用静态（通过在文件详细编写代码）或动态（由PMON进程更新侦听器）方法，向侦听器注册实例

 每个用户进程都存在到专用服务器进程的持久连接

使用Oracle共享服务器体系结构

 用户进程连接到调度程序，这些连接是持久的

 所有调度程序在一个公共队列上放入请求

 共享服务器进程从公共队列中取走请求

 每个调度程序都有自己的响应队列

 共享服务器进程将结果放在适当调度程序的响应队列上

 调度程序取出结果，将结果返回给适当的用户进程

 共享服务器至少配置两个实例参数：dispatchers和shared_servers。

20 / 74

第五章 Oracle存储结构

本章学习内容

 了解表空间和数据文件

 创建和管理表空间

 管理表空间中的空间

练习5-1 了解数据库的数据存储结构

在本练习中，将运行查询来了解数据库的物理结构。可以从SQL*Plus或Database

Control，交互方式运行这些命令。但最好将它们保存为一个脚本（针对显示格式做适当的完善，并执行特定站点的自定义），可针对任何数据库运行此脚本，这将作为普通空间使用报告的一部分。

（1） 以用户SYSTEM的身份连接到数据库

（2） 确定控制文件的名称和大小：

SELECT NAME, block_size* bytes FROM v$controlfile;

（3） 确定联机重做日志文件成员的名称和大小：

SELECT MEMBER,bytes FROM v$log JOIN v$log(group#);

（4） 确定数据文件和临时文件的名称和大小：

SELECT 'v$datafile', NAME, BYTES FROM V$DATA ALL

SELECT 'v$tempfile', NAME, BYTES FROM V$TEMPFILE;

21 / 74

练习5-2 创建、更改和删除表空间

在本练习中，将创建表空间并更改其特性。此后将启用和使用OMF。可以通过Database

Control完成本练习，若如此，请在所有阶段单击Show SQL按钮以观察生成的SQL语句。

（1） 以用户SYSTEM的身份连接到数据库

（2） 在适当目录（Oracle所有者拥有写权限的任何目录）中创建表空间

CREATE TABLESPACE mpac_lc DATAFILE

'D:oracleproduct10.2.0oradataorclmpac_' SIZE 10m

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

SEGMENT SPACE MANAGEMENT AUTO;

此命令指定默认选项。这是一项良好的实践，使语句可以自我记录。

（3） 在新的表空间创建表，并确定第一个区间的大小：

CREATE TABLE lc_test(c1 DATE) TABLESPACE mpac_lc;

SELECT owner, extent_id, bytes FROM dba_extents WHERE segment_name =

'LC_TEST'; --创建表初始为1个段大小64k，8个块

（4） 手动添加区间，并通过重复执行以下命令来观察每个新区间的大小，并重复执行区间大小的查询。注意区间大小的增长点。（手动添加区间，每次增加一个区间）

ALTER TABLE lc_test ALLOCATE EXTENT;

SELECT owner, extent_id, bytes FROM dba_extents WHERE segment_name =

'LC_TEST';

（5） 使表空间脱机，观察效果，再使其重新联机。

ALTER TABLESPACE mpac_lc OFFLINE;

SELECT * FROM lc_test;

ORA-00376: file 7 cannot be read at this time

ORA-01110: data file 7: 'D:ORACLEPRODUCT10.2.0ORADATAORCLMPAC_'

ALTER TABLESPACE mpac_lc ONLINE;

SELECT * FROM lc_test;

（6） 将表空间设置为只读表空间，观察效果，再将其设置为读写。

ALTER TABLESPACE mpac_lc READ ONLY;

SELECT * FROM lc_test;

INSERT INTO lc_test VALUES(SYSDATE);

ORA-00372: file 7 cannot be modified at this time

ORA-01110: data file 7: 'D:ORACLEPRODUCT10.2.0ORADATAORCLMPAC_'

DROP TABLE lc_test;

ALTER TABLESPACE mpac_lc READ WRITE;

CREATE TABLE lc_test (c1 DATE) TABLESPACE mpac_lc;

INSERT INTO lc_test VALUES(SYSDATE);

（7） 启动OMF来创建数据库文件：

ALTER SYSTEM SET db_create_'D:oracleproduct10.2.0oradata';

（8） 使用最少的语法创建表空间

22 / 74

CREATE TABLESPACE mpac_lc_idx;

（9） 确定OMF文件的特性

SELECT ,

BYTES / 1024 / 1024,

AUTOEXTENSIBLE,

MAXBYTES / 1024 / 1024,

INCREMENT_BY / 1024 / 1024

FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME = 'MPAC_LC_IDX';

文件名：D:ORACLEPRODUCT10.2.0ORADATAORCLDATA

数据文件默认的初始化大小为100MB、文件大小自动扩展、没有扩展上限（这里最大为32GB，与32位操作系统有关）、空间大小增长的步长为12.5KB

（10） 调整OMF条件，使特性更趋于合理。使用步骤（9）返回的系统生成的任何文件名：

ALTER DATABASE DATAFILE 'D:ORACLEPRODUCT10.2.0ORADATAORCLDATA'

RESIZE 200m;

ALTER DATABASE DATAFILE 'D:ORACLEPRODUCT10.2.0ORADATAORCLDATA'

AUTOEXTEND ON NEXT 100m MAXSIZE 2g;

（11） 删除表空间，并使用操作系统命令予以确认：

DROP TABLESPACE mpac_lc_idx INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE mpac_lc_idx ;

在表空间中没有对象时，两者效果相同，否则第二条执行将报错。

练习5-3 更改表空间特性

本练习将使用非默认的手动空间管理来创建表空间，以便在升级后模拟到自动段空间管理的转换：

（1） 以用户SYSTEM的身份连接到数据库。

（2） 使用手动段空间管理创建表空间。由于【练习5-2】中启用了OMF，所以不需要任何数据文件子句：

CREATE TABLESPACE manualsegs SEGMENT SPACE MANAGEMENT MANUAL;

（3） 使用手动技术确认新表空间的存在：

SELECT segment_space_management

tablespace_name = 'MANUALSEGS'

FROM dba_tablespaces WHERE

（4） 在表空间中创建表和索引：

CREATE TABLE mantab (c1 NUMBER) TABLESPACE manualsegs ;

CREATE INDEX mantabidx ON mantab(c1) TABLESPACE manualsegs ;

将使用空闲列表（而非位图）来创建这些段。

（5） 新建将使用自动段空间管理（默认方式）的表空间：

CREATE TABLESPACE autosegs;

（6） 将对象移入新表空间：

ALTER TABLE mantab MOVE TABLESPACE autosegs;

ALTER INDEX mantabidx REBUILD ONLINE TABLESPACE autosegs;

23 / 74

（7） 确认对象位于正确的表空间中：

SELECT segment_name,tablespace_name

segment_name LIKE 'MANTAB%'

FROM dba_segments WHERE

（8） 删除原先的表空：

DROP TABLESPACE manualsegs INCLUDING CONTENTS AND DATAFILES;

（9） 将新的表空间重命名为原来的名称。通常这是必需的，因为某些应用程序软件将检查表空间名称：

ALTER TABLESPACE autosegs RENAME TO manualsegs;

（10） 通过删除表空间进行清理，首先使用以下命令：

DROP TABLESPACE manualsegs;

ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

由于表空间非空，将引发错误，应对此进行修正：

DROP TABLESPACE manualsegs INCLUDING CONTENTS AND DATAFILES;

TIPS 自动段管理和手动段管理的区别

TIPS 本地区间管理和xxx 的区别

本章知识点回顾

了解表空间和数据文件

 一个表空间可能是多个数据文件

 一个表空间可包含多个段

 一个段是一个或多个区间

 一个区间是位于一个数据文件的多个连续的块

 Oracle块是数据库的基本I/O单元

创建和管理表空间

 SMALLFILE表空间可以有多个数据文件，但BIGFILE表空间只能有一个数据文件

 默认方式下，表空间采用本地区间管理和自动段空间管理的方式。但统一区间大小并非默认设置。

 OMF数据文件自动命名，开始为100MB，可以自动拓展，没有上限

 除非指定了INCLUDING CONTENTS 子句，否则包含段的表空间无法删除

 可将表空间设置为联机或脱机，设置为读写或只读

 表空间可以存储三类对象：永久对象、临时对象或撤销段

管理表空间中的空间

 本地区间管理使用每个数据文件中的位图来跟踪区间分配

 创建表空间时，UNIFORM SIZE子句强制所有的区间采用相同的大小

 AUTOALLOCATE 子句让Oracle确定下一区间的大小，Oracle将根据分配给段的区间数进行确定。

 自动段空间管理使用位图跟踪区间每个块中的可用空间

 可将表空间从字典区间管理转换为本地区间管理，但不能讲空闲段管理转换为自动管理。

24 / 74

25 / 74

第六章 Oracle安全性

本章学习内容













创建和管理数据库用户账户

授予和撤销权限

创建和管理角色

创建和管理配置文件

实现数据库安全和最小权限原则

使用标准数据库审核

练习6-1 创建用户

在本练习中，将创建一些用户，本章其余练习将使用这些用户。假设有一个名为STOREDATA的永久表空间和一个名为TEMP的临时表空间。如果这些都不存在，请予以创建，或使用其它任何适当的表空间。

（1） 使用SQL*Plus，以具有充足权限（如SYSTEM或SYS）的用户身份连接到数据库。

（2） 创建三个用户：

CREATE USER sales IDENTIFIED BY sales DEFAULT TABLESPACE storedata

PASSWORD EXPIRE; --首次登录需要从新设置密码

CREATE USER webapp IDENTIFIED BY oracle DEFAULT TABLESPACE storedata

QUOTA UNLIMITED ON storedata; --限制表空间使用大小

CREATE USER accounts IDENTIFIED BY oracle;

练习6-2 授予直接权限

在本练习中，将为【练习6-1】中创建的用户赋予一些权限，并证明这些权限已经生效。

（1） 使用SQL*Plus，以用户SYSTEM的身份连接到数据库。

（2） 为用户SALES授予CREATE SESSION权限。

GRANT CREATE SESSION TO sales;

（3） 打开另一个SQL*Plus会话，以SALES的身份连接。此次登陆将会成功

SQL> connect sales/oracle

（4） 以SALES的身份，尝试创建表：

CREATE TABLE t1(c1 DATE);

ORA-01031: insufficient privileges

这将失败，并弹出消息“ORA-01031: insufficient privileges”。

（5） 在SYSTEM会话中，向SALES授予CREATE TABLE权限：

SQL> grant create table to sales;

（6） 在SALES会话中，再尝试一下：

CREATE TABLE t1(c1 DATE);

ORA-01950: no privileges on tablespace 'STOREDATA'

26 / 74

这将失败，并弹出消息“ORA-01950: no privileges on tablespace 'STOREDATA'”

（7） 在SYSTEM会话中，为SALES分配STOREDATA表空间的配额：

ALTER USER sales QUOTA 1m ON storedata;

（8） 在SALES会话中再次尝试。此次创建将会成功。

（9） 以SALES的身份，在新表上授予对象权限。

GRANT ALL ON t1 TO webapp;

GRANT SELECT ON t1 TO accounts;

（10） 以用户SYSTEM的身份连接到Database Control。

（11） 确认已经授予了对象权限。在数据库主页中，导航到Schema选项卡，然后单击Database Objects部分中的Tables链接。输入“SALES”作为Schema，输入“T1”作为Table，并单击Go按钮。在Action下拉框中，选择Object Privileges。ACCOUNTS只有SELECT权限，WEBAPP拥有全部权限。注意，此窗口还显示是谁授予权限，而且它们都未被授予 WITH GRANT OPTION。

（12） 在Database Control中，确认为SALES授予了哪些权限。在数据库主页上，导航大Server选项卡，然后单击Security部分中的User链接。选中SALES的单选按钮，并单击View按钮。您将看到，它有两个系统权限（CREATE SSESSION 和CREATE TABLE），而无ADMIN OPTION，在STOREDATA上有1MB的配额，除此无它。

（13） 使用SQL*Plus检索步骤（11）和步骤（12）中的相同信息。以SYSTEM的身份，运行这些查询：

SELECT grantee, PRIVILEGE, grantor, grantable

FROM dba_tab_privs

WHERE owner = 'SALES'

AND table_name = 'T1'

SELECT * FROM dba_sys_privs WHERE grantee='SALES'

（14） 撤销授予WEBAPP和ACOUNTS的权限：

REVOKE ALL ON sales.t1 FROM webapp;

REVOKE ALL ON sales.t1 FROM accounts;

重新运行步骤（13）中的第一个查询确认撤销。

27 / 74

练习6-3 创建和授予角色

在本练习中，将创建一些角色，并把这些角色授予用户，演示其有效性。

（1） 使用SQL*Plus，以SYSTEM用户身份连接到数据库。

（2） 按一下方式创建两个角色

CREATE ROLE usr_role;

CREATE ROLE mgr_role;

（3） 为这些角色授予一些权限，并将USR_ROLE授予MGR_ROLE；

GRANT CREATE SESSION TO usr_role;

GRANT SELECT ON sales.t1 TO usr_role;

GRANT usr_role TO mgr_role WITH ADMIN OPTION;

GRANT ALL ON sales.t1 TO mgr_role;

（4） 以用户SYSTEM的身份，将MGR_ROLE角色授予WEBAPP用户：

GRANT mgr_role TO webapp;

（5） 以用户WEBAPP的身份连接到数据库：

conn webapp/oracle

（6） 将角色USR_ROLE授予用户ACCOUNTS，并在SALES.T1中插入一行：

GRANT usr_role TO accounts;

INSERT INTO sales.t1 VALUES (SYSDATE);

COMMIT;

（7） 确认ACCOUNTS可以连接并查询SALES.T1，但无其它权限。紧跟其后的INSERT语句会失败，并弹出错误消息“ORA-01031：insufficient privileges”。

SELECT * FROM sales.t1;

INSERT INTO sales.t1 VALUES(SYSDATE);

ORA-01031: insufficient privileges

（8） 以用户SYSTEM的身份，对ACCOUNTS进行调整，使其可以在默认情况下登录，但无其它权限：

CONNECT accounts/oracle

GRANT CONNECT TO accounts;

ALTER USER accounts DEFAULT ROLE CONNECT;

（9） 演示角色的启动和禁用。SALES第一次尝试查询SALES.T1表时，将弹出“ORA-00942：table or view does not exist”错误。在启动USER_ROLE后，同样的查询将成功：

CONNECT accounts/oracle;

SELECT * FROM sales.t1;

ORA-00942: table or view does not exist

SET ROLE usr_role;

SELECT * FROM sales.t1;

（10） 使用Database Control来检查这些角色。在数据库主页上的导航路径是：在Server选项卡上单击Security部分的Roles链接。单击两个新角色对应的链接来查看其权限。

（11） 要查看为谁授予了角色，在Actions下拉框中，选择Show Grantees选项，并单击Go按钮。

（12） 使用下面的查询获取在步骤（10）和步骤（11）检索的同一个信息：

SELECT *

FROM DBA_ROLE_PRIVS

WHERE GRANTED_ROLE IN ('USR_ROLE', 'MGR_ROLE');

28 / 74

SELECT grantee, owner, table_name, PRIVILEGE, grantable

FROM dba_tab_privs

WHERE grantee IN ('USR_ROLE', 'MGR_ROLE')

UNION ALL

SELECT grantee, to_char(NULL), to_char(NULL), PRIVILEGE, admin_option

FROM dba_sys_privs

WHERE grantee IN ('USR_ROLE', 'MGR_ROLE')

ORDER BY grantee;

练习6-4 创建和使用配置文件

在本练习中，将创建、分配和测试一个配置文件，此文件将实施一些口令控制。

（1） 通过SQL*Plus，以system用户的身份连接到数据库

（2） 创建一个配置文件，如果出现两次口令输入错误，那么将锁定账户：

CREATE PRO LIMIT FAILED_LOGIN_ATTEMPTS 2;

（3） 将新配置文件分配给SALES：

ALTER USER sales PRO;

（4） 使SALES故意多次输入错误口令。在第三次尝试失败后，将得到“ORA-28000：the account is locked”消息：

conn sales/xxxxxxx

（5） 以SYSTEM用户的身份，解除SALES账户的锁定：

ALTER USER sales ACCOUNT UNLOCK;

（6） 确定SALES此时可以连接：

conn sales/oracle

（7） 通过删除配置文件、角色和用户进行清理。在删除配置文件以便从SALES将其删除时，以及在DROP USER命令上删除其表时使用了CASCADE。即使已将角色分配给用户，也可以删除角色。在删除表时，也将撤销在此表上授予的权限。

CONNECT SYSTEM/oracle;

DROP PRO CASCADE;

DROP ROLE usr_role;

DROP ROLE mgr_role;

29 / 74

DROP USER sales CASCADE;

DROP USER accounts;

DROP USER webapp;

练习6-5 删除一些有潜在危险的权限

本练习将生成一个脚本（可能需要做一些编辑，具体取决于本地要求），以便从PUBLIC删除一些更具危险性的权限。此次使用SQL*Plus

（1） 以用户SYSTEM的身份连接到数据库

（2） 编辑SQL*Plus，从其输出中删除无关的字符：

set headers off;

set pagesize 0;

set feedback off;

（3） 将输出打印到适当目录的文件中。下面是Unix和Windows系统中的示例：

Spool $HOME/oracle/scripts/clear_public_

Spool c:oraclescriptsclear_public_

（4） 通过运行以下语句生成SQL命令文件

SQL> set pagesize 0

SQL> set feedback off

SQL> set feedback off

SQL> spool clear_public_

Started spooling to d:Program FilesPLSQL

Developerclear_public_

select 'revoke execute on '||table_name||' from public;' from

dba_tab_privs where table_name like 'UTL_%';

revoke execute on UTL_ALL_IND_COMPS from public;

revoke execute on UTL_URL from public;

revoke execute on UTL_TCP from public;

revoke execute on UTL_SMTP from public;

revoke execute on UTL_REF from public;

revoke execute on UTL_RAW from public;

revoke execute on UTL_NLA_ARRAY_INT from public;

revoke execute on UTL_NLA_ARRAY_FLT from public;

revoke execute on UTL_NLA_ARRAY_DBL from public;

revoke execute on UTL_NLA from public;

revoke execute on UTL_MATCH from public;

revoke execute on UTL_LMS from public;

revoke execute on UTL_INADDR from public;

revoke execute on UTL_I18N from public;

revoke execute on UTL_HTTP from public;

revoke execute on UTL_GDK from public;

revoke execute on UTL_ public;

revoke execute on UTL_ENCODE from public;

revoke execute on UTL_DBWS from public;

revoke execute on UTL_COMPRESS from public;

revoke execute on UTL_COLL from public;

SQL> spool off

Stopped spooling to d:Program FilesPLSQL

Developerclear_public_

30 / 74

练习6-6 使用标准数据库审核

本练习将使用Database Control 或SQL*Plus启动标准数据库审核，并查看结果。如果使用Database Control，那么确保在适当的时候单击Show SQL按钮来查看生成的SQL语句。

（1） 以用户SYSTEM的身份连接到数据库，并创建本练习使用的用户和表：

CREATE USER auditor IDENTIFIED BY oracle;

CREATE TABLE AS SELECT * FROM all_users;

GRANT CREATE SESSION, SELECT ANY TABLE TO auditor;

GRANT SELECT ON TO auditor;

（2） 使用SQL*Plus，启动对AUDITOR使用SELECT ANY PRIVILEGE的审核，以及对表AUDI的所有访问的审核：

AUDIT SELECT ANY TABLE BY ACCESS;

AUDIT ALL ON BY ACCESS;

（3） 以用户SYS的身份连接到数据库。这是必须的，因为此步骤需要重新启动实例。将审核跟踪目标设置为DB，启用权限用户的审核，关闭并重启实例，按如下所示使用SQL*Plus：

ALTER SYSTEM SET audit_trail='DB_EXTENDED' SCOPE = SPFILE;

ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE;

startup FORCE;

使用Database Control时，数据库主页可能的导航路径为：选择Server选项卡，然后单击Security部分中的Audit Setting链接。如果单击Configuration部分中的Audit Trail链接，将弹出一个窗口，允许修改spfile中的参数设置。另外，在Server选项卡上。单击Database

Configuratuon 部分Initialization Parameters链接，可以直接进入Initialization Parameters窗口。

在spfile中设置两个参数，此后，在数据库主页中关闭并重新启动数据库。

（4） 以SYS身份连接时，将审核所有的语句。运行以下语句：

SELECT COUNT(*) FROM ;

（5） 如果使用Linux或Unix，则可以通过查询AUDIT_参数来确定系统审核跟踪的位置。这将用于审核SYS操作，而不考虑AUDIT_DEST设置。使用SQL*Plus运行以下语句：

SELECT VALUE FROM v$parameter WHERE NAME ='audit_';

使用操作系统导航到此目录，并打开最新创建的文件。

如果使用windows，请在Event Viewer中打开Application log。无论如何，都将看到以SYS身份执行的SELECT语句，以及操作系统用户和主机名的详细信息。

（6） 以AUDITOR的身份连接到数据库，并运行以下的查询：

SELECT COUNT(*) FROM ;

SELECT COUNT(1) FROM t_user_profile;

（7） 以用户SYSTEM的身份，运行查询来查看审核事件：

SELECT SQL_TEXT, PRIV_USED, ACTION_NAME

FROM DBA_AUDIT_TRAIL

WHERE USERNAME = 'AUDITOR';

注意，使用了最低权限：通过SELECT对象权限来访问AUDI表，而非功能更强大的系统权限SELECT ANY TABLE（获取PRODUCT_USER_PROFILE需要此权限）。

（8） 进行整理：

DROP USER auditor;

31 / 74

DROP table ;

本章知识点回顾

创建和管理数据库用户账户

 用户和用户账户连接，而用户账户与模式连接。

 所有用户都需要在连接前进行身份验证

 用户想要创建任何对象，必须拥有表空间上的配额。

 如果不使用CASCADE关键字，将不能删除拥有对象的用户。

授予和撤销权限

 默认方式下，用户什么都不能做，包括不能登录

 直接权限会始终被启动

 系统权限的撤销不级联，而对象权限的撤销级联

创建和管理角色

 角色不是模式对象

 角色包括系统权限和对象权限以及其它角色

 可以为会话启动或禁用角色

创建和管理配置文件

 配置文件可以管理口令和资源限制

 口令限制要强制实施，而资源限制取决于实例参数

 每个用户始终都有配置文件，默认是DEFAULT配置文件

数据库安全和最小权限原则

 未特地允许的所有权限，都要予以拒绝

 数据库管理员和系统管理员不应由同一个人担任

 必须监视授予PUBLIC角色的权限

 必须监视关键的安全实例参数，只有重新启动实例，才能更改它们

使用标准数据库审核

 数据库审核可以针对权限、命令或对象

 审核记录可以指向数据库表或操作系统文件

 数据库审核记录存储在$数据字典表中

 细粒度审核可以指定特定的行和列

 可用使用数据库触发器实现审核。

32 / 74

第七章 DDL和模式对象

本章学习内容































分类主要的数据库对象

回顾表结构

列举列可用的数据类型

创建简单的表

讨论在创建表时如何创建约束

描述模式对象的工作方式

创建和修改表

管理约束

创建索引

创建和使用临时表

创建简单视图和复杂视图

从视图中检索数据

创建、维护和使用序列

创建和维护索引

创建私有同义词和公有同义词

练习7-1 确定会话可以访问哪些对象

在这个练习中，将以用户HR身份来查询各种数据字典视图，确定HR模式中有哪些对象以及HR可以访问其它模式中的哪些对象。

（1） 使用SQL*Plus或SQL Developer，以用户HR身份连接到数据库

（2） 确定HR模式中各种类型的对象数量：

SELECT object_type,COUNT(*) FROM user_objects GROUP BY object_type;

USER_OBJECTS视图将列出当前会话连接的模式（这里是HR）拥有的所有对象。

（3） 确定HR有权访问的对象的总数

SELECT object_type,COUNT(*) FROM all_objects GROUP BY object_type;

33 / 74

ALL_OBJECTS视图将列出用户能够访问的所有对象。

（4） 确定谁拥有HR能够看到的对象：

SELECT DISTINCT owner FROM all_objects;

练习7-2 研究HR模式中的数据类型

在这个练习中，将通过两种方式找出在HR模式中表使用的数据类型：

（1） 使用SQL*Plus或者SQL Developer，以用户HR连接到数据库

（2） 使用DESCRIBE命令显示某些表的数据类型

34 / 74

SQL> describe employees;

SQL> describe departments;

（3） 像DESCRIBE命令那样，查询数据字典视图显示组成EMPLOYEES表的列：

SELECT column_name,

data_type,

nullable,

data_length,

data_precision,

data_scale

FROM user_tab_columns

WHERE table_name = 'EMPLOYEES';

视图USER_TAB_COLUMNS将显示当前用户模式中每个表的每一列的详细情况。

练习7-3 创建表

这个练习表明案例研究正式开始。现在，您的计算机上应已安装了一个数据库，如果已完成了第5章中的练习。那么应有一个STOREDATA表空间，否则应创建一个。

在本练习中，使用SQL Developer来创建一个堆表。使用子查询来插入一些行，并修改表。使用SQL*Plus进行更多修改，然后删除表。

（1） 以SYSTEM用户身份连接数据库，创建WEBSTORE用户并指定默认的表空间STOREDATA和临时表空间TEMP。授予WEBSTORE用户在STOREDATA表空间35 / 74

上无限的配额，以及创建会话和创建表的权限。WEBSTORE模式将用于后续练习中。

CREATE USER webstore IDENTIFIED BY webstore DEFAULT TABLESPACE

storedata;

ALTER USER webstore QUOTA UNLIMITED ON storedata;

GRANT CREATE SESSION,CREATE TABLE TO webstore;

（2） 使用SQL Developer，以用户WEBSTORE身份连接。右击导航树的Tables分支，单击New Table选项。

（3） 将新表命名为CUSTOMERS，使用Add Column按钮。

（4） 单击DDL标签，看看是不是已经构造了语句。它应该如下所示

CREATE TABLE customers

(

customer_id NUMBER(8,0) NOT NULL,

join_date DATE NOT NULL,

Customer_staus VARCHAR2(8) NOT NULL,

customer_name VARCHAR2(20) NOT NULL,

creditrating VARCHAR2(10)

);

返回Table选项卡，单击OK按钮创建表

（5） 运行以下语句：

INSERT INTO CUSTOMERS

(CUSTOMER_ID, JOIN_DATE, CUSTOMER_STAUS, CUSTOMER_NAME,

CREDITRATING)

VALUES

(1, SYSDATE, 'NEW', 'Ameetha', 'Platinum');

INSERT INTO CUSTOMERS

(CUSTOMER_ID, JOIN_DATE, CUSTOMER_STAUS, CUSTOMER_NAME,

CREDITRATING)

VALUES

(2, SYSDATE, 'NEW', 'Coda', 'Bronze');

COMMIT;

（6） 右击SQL Developer导航中的CUSTOMERS表，单击Column和Add按钮。

（7） 定义一个新的列EMALI，数据类型为VARCHAR2(50)，单击Apply按钮创建新列。

ALTER TABLE customers ADD email VARCHAR2(50);

（8） 使用SQL*Plus作为WEBSTORE用户连接到数据库

（9） 为CUSTOMERS表中的JOIN_DATE列定义一个默认值：

ALTER TABLE customers MODIFY(join_date DEFAULT SYSDATE);

（10） 在没有指定JOIN_DATE值的情况下插入一行，检查新的行有JOIN_DATE日期，而其它行没有：

INSERT INTO CUSTOMERS

(CUSTOMER_ID, CUSTOMER_STAUS, CUSTOMER_NAME, CREDITRATING)

VALUES

(3, 'NEW', 'Sid', 'Gold');

SELECT join_date,COUNT(1) FROM customers GROUP BY join_date;

（11） 创建三个额外的表

CREATE TABLE orders

(

order_id NUMBER(8) NOT NULL,

order_date DATE NOT NULL,

order_status VARCHAR2(8) NOT NULL,

order_amount NUMBER(10,2),

customer_id NUMBER(8) NOT NULL

);

36 / 74

CREATE TABLE order_items

(

order_item_id NUMBER(8) NOT NULL,

order_id NUMBER(8) NOT NULL,

product_id NUMBER(8) NOT NULL

);

CREATE TABLE products

(

product_id NUMBER(8) NOT NULL,

product_description VARCHAR2(20) NOT NULL,

product_status VARCHAR2(8) NOT NULL,

price NUMBER(10,2) NOT NULL,

price_date DATE NOT NULL,

stock_count NUMBER(8) NOT NULL

);

（12） 向ORDER_ITEMS表中添加QUANTITY列，其数据类型为NUMBER

ALTER TABLE order_items ADD (quantity NUMBER);

练习7-4 创建和使用临时表

本练习创建用于报告当前员工的临时表。通过使用两个SQL*Plus会话演示数据是每个会话专用的。

（1） 使用SQL*Plus，以用户HR的身份连接到数据库

（2） 按如下方式创建临时表

CREATE GLOBAL TEMPORARY TABLE tmp_emps ON COMMIT PRESERVE ROWS

AS SELECT * FROM ees WHERE 1=2;

（3） 插入一些行，并将其提交

INSERT INTO tmp_emps SELECT * FROM employees WHERE department_id =

30;

COMMIT;

（4） 以HR身份登陆到第二个SQL*Plus会话

（5） 在第二个会话中，确认第一个会话中的第一个插入虽已提交但不可以见，并插入一些不同的行：

SELECT COUNT(*) FROM tmp_emps;

INSERT INTO tmp_emps SELECT * FROM employees WHERE department_id=50;

COMMIT;

（6） 在第一个会话中，将表截断

TRUNCATE TABLE tmp_emps;

（7） 在第二个会话中，确认表的会话副本仍然包含行

SELECT COUNT(*) FROM tmp_emps;

（8） 在第二个会话中，演示会话的终止并不会清除行。这需要断开连接并再次连接

SQL> disconnect;

Not logged on

SQL> connect hr/hr

Connected to Oracle Database 10g Enterprise Edition Release

10.2.0.3.0

Connected as hr

SQL> SELECT COUNT(*) FROM tmp_emps;

37 / 74

COUNT(*)

----------

0

（9） 在两个会话中，通过删除表来清理环境。

练习7-5 创建索引

本练习将为CUSTOMERS表添加一些索引。

（1） 使用SQL*Plus，以用户WEBSTORE的身份连接到数据库

（2） 在客户名称和客户状态上创建复合B*树索引：

CREATE INDEX cust_name_i ON customers(customer_name, customer_staus);

（3） 在一些低基数列上创建位图索引

CREATE BITMAP INDEX creditrating_i ON customers(creditrating);

（4） 运行以下查询确定刚创建的索引的名称及其它一些特性

SELECT index_name, column_name, index_type, uniqueness

FROM user_indexes NATURAL

JOIN user_ind_columns

WHERE table_name = 'CUSTOMERS'

练习7-6 管理约束

在本练习中，使用SQL Developer和SQL*Plus来定义和调整在【练习7-3】中创建的表上的一些约束。

（1） 在SQL Developer中，导航到WEBSTORE上的一系列表并单击CUSTOMERS表。

（2） 通过Constraints选项卡，查看为该表创建的4个NOT NULL约束。注意，它们的名称起不到什么步骤作用，并在步骤（8）中确定名称。

（3） 单击Actions按钮，选择Constraints：Add Primary Key

（4） 在Add Primary Constraint窗口中，命名约束为PK_CUSTOMER_ID，选择CUSTOMER_ID列，然后单击Apply按钮

（5） 选择Show SQL选项卡来查看创建约束的语句，然后单击Apply按钮运行该语句。

ALTER TABLE customers ADD CONSTRAINT pk_customer_id PRIMARY KEY

(customer_id);

（6） 使用SQL*Plus，以用户WEBSTORE的身份连接到数据库。

（7） 运行此查询来查找约束的名称：

SELECT constraint_name, constraint_type, column_name

FROM user_constraints NATURAL

JOIN user_cons_columns

WHERE table_name = 'CUSTOMERS';

38 / 74

（8） 使用ALTER TABLE命令，将基于步骤（7）检索到的最初约束名，将约束重命名为更有意义的名称

ALTER TABLE customers RENAME CONSTRAINT sys_c005565 TO

customer_id_not_null;

ALTER TABLE customers RENAME CONSTRAINT sys_c005566 TO

join_date_not_null;

ALTER TABLE customers RENAME CONSTRAINT sys_c005567 TO

customer_staus_not_null;

ALTER TABLE customers RENAME CONSTRAINT sys_c005568 TO

customer_name_not_null;

（9） 向WEBSTORE模式下添加下列约束：

ALTER TABLE orders ADD CONSTRAINT pk_order_id PRIMARY KEY (order_id);

ALTER TABLE products ADD CONSTRAINT pk_product_id PRIMARY KEY

(product_id);

ALTER TABLE order_items ADD CONSTRAINT fk_product_id FOREIGN KEY

(product_id) REFERENCES products(product_id);

ALTER TABLE order_items ADD CONSTRAINT fk_order_id FOREIGN KEY

(order_id) REFERENCES orders(order_id);

ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY

(customer_id) REFERENCES customers(customer_id)

练习7-7 创建视图

在此练习中，使用处于HR模式的数据创建一些简单视图和复杂视图。可以使用SQL*Plus，也可以使用SQL Developer。

（1） 作为用户HR连接到数据库

（2） 对EMPLOPEES和DEPSRTMENT表创建视图。删除其中的所有个人信息：

CREATE VIEW emp_anon_v AS

SELECT hire_date,job_id,salary,commission_pct,department_id FROM

ees;

CREATE VIEW dept_anon_v AS

SELECT department_id,department_name,location_id FROM departments;

（3） 创建联接与聚合着两个简单视图的复杂视图。注意。可以在视图的基础上再创建视图。

CREATE VIEW dep_sum_v AS

SELECT MENT_ID,

COUNT(1) STAFF,

SUM() AS SALARIES,

MENT_NAME

FROM EMP_ANON_V E

JOIN DEPT_ANON_V D

ON MENT_ID = MENT_ID

GROUP BY MENT_ID, MENT_NAME;

（4） 通过查询确认视图有效

39 / 74

练习7-8 创建和使用同义词

在本练习中，将用HR模式中的对象创建和使用私有同义词。可以使用SQL*Plus或SQL

Developer。

（1） 作为用户HR连接到数据库

（2） 为【练习7-7】中创建的三个视图创建同义词：

CREATE SYNONYM emp_s FOR emp_anon_v;

CREATE SYNONYM dept_s FOR dept_anon_v;

CREATE SYNONYM dsum_s FOR dep_sum_v;

（3） 确定同义词等同于底层对象：

SQL> describe emp_s;

Name Type Nullable Default Comments

-------------- ------------ -------- ------- --------

HIRE_DATE DATE

JOB_ID VARCHAR2(10)

SALARY NUMBER(8,2) Y

COMMISSION_PCT NUMBER(2,2) Y

DEPARTMENT_ID NUMBER(4) Y

SQL> describe emp_anon_v;

Name Type Nullable Default Comments

-------------- ------------ -------- ------- --------

HIRE_DATE DATE

JOB_ID VARCHAR2(10)

SALARY NUMBER(8,2) Y

COMMISSION_PCT NUMBER(2,2) Y

DEPARTMENT_ID NUMBER(4) Y

（4） 通过对同义词而不是视图运行【练习7-7】中的语句来确认同义词有效（甚至到产生相同错误的程度）

SELECT * FROM dsum_s;

INSERT INTO dept_s VALUES(99,'Temp Dept',1800);

INSERT INTO emp_s VALUES(SYSDATE,'AC_MGR',10000,0,99);

UPDATE emp_s SET salary=salary*1.1;

ROLLBACK;

SELECT MAX(salaries/staff) FROM dsum_s;

（5） 删除两个视图

DROP VIEW emp_anon_v;

DROP VIEW dept_anon_v;

（6） 查询基于已删除视图的复杂视图

40 / 74

SELECT * FROM dep_sum_v;

ORA-04063: view "_SUM_V" has errors

可以注意到查询失败。

（7） 尝试重新编译被破坏的视图

ALTER VIEW dep_sum_v COMPILE;

此次编译尝试也会失败

（8） 删除DEP_SUM_V视图

DROP VIEW dep_sum_v;

（9） 查询已删除视图的同义词

SELECT * FROM emp_s;

ORA-00980: synonym translation is no longer valid

该查询会失败。

（10） 重新编译被破坏的同义词

ALTER SYNONYM emp_s COMPILE;

注意，虽然这样做不会抛出错误，而是从第（9）步开始重新运行查询。该同义词无疑仍然处于破坏状态。

（11） 通过删除同义词进行整理：

DROP SYNONYM emp_s;

DROP SYNONYM dept_s;

DROP SYNONYM dsum_s;

练习7-9 创建和使用序列

在本练习中，创建一些序列并使用它们。需要两个并发会话，可用使用SQL Developer或者SQL*Plus。

（1） 在单独的会话中作为WEBSTORE登录到数据库两次。将其中一次登录看着A会话，另一次看做B会话。

（2） 在A会话中，创建如下所示的序列：

CREATE SEQUENCE seql START WITH 10 NOCACHE MAXVALUE 15 CYCLE;

NOCACHE的使用会降低性能。如果指定了MAXVALUE，那么有必要用CYCLE防止达到MAXVALUE时出错。

（3） 在适当的会话中按正确的顺序执行表中的命令，以观察NEXTVAL和CURRVAL的使用以及序列的循环。

步骤 在A会话中 在B会话中

l FROM

第1步

SELECT

dual;--10

SELECT l FROM

第2步

dual;--11

l FROM

第3步

SELECT

dual;--12

SELECT l FROM

第4步

dual;--13

l FROM

第5步

SELECT

dual;--12

SELECT l FROM

第6步

dual;--14

l FROM

第7步

SELECT

dual;--15

SELECT l FROM

第8步

dual;--14

41 / 74

第9步

SELECT l

dual;--1

FROM

SELECT l FROM

dual;--2

第10步

（4） 创建一个带主键的表：

CREATE TABLE seqtest

(

c1 NUMBER,

c2 VARCHAR2(10)

);

ALTER TABLE seqtest ADD CONSTRAINT seqtest_pk PRIMARY KEY (c1);

（5） 创建一个序列来生成主键值：

CREATE SEQUENCE seq_test_pk_s;

=

CREATE SEQUENCE seq_test_pk_s

MINVALUE 1

MAXVALUE 999999999999999999999999999

START WITH 1

INCREMENT BY 1

CACHE 20;

（6） 在A会话中，向新表中插入一行并提交：

INSERT INTO seqtest VALUES(seq_test_pk_l,'first');

COMMIT;

（7） 在B会话中，向新表插入一行并不提交：

INSERT INTO seqtest VALUES (seq_test_pk_l,’second’);

（8） 在A会话中，插入第三行并提交：

INSERT INTO seqtest VALUES(seq_test_pk_l,'third');

COMMIT;

（9） 在B会话中，回滚第二个插入：

ROLLBACK;

（10） 在B会话中查看表的内容：

SELECT * FROM seqtest;

这就演示了在事务控制机制外部递增序列并立即发布下一个值。

（11） 整理表和序列：

DROP TABLE seqtest;

DROP SEQUENCE seq_test_pk_s;

DROP SEQUENCE seql;

（12） 使用SQL Developer或SQL*Plus连接到WEBSTORE模式并创建三个将在后面练习中使用的序列（必须首先以特权用户如SYSTEM的身份连接，将“CREATE

SEQUENCE”权限授予WEBSTORE用户）。

CREATE SEQUENCE prod_seq;

CREATE SEQUENCE cust_seq;

CREATE SEQUENCE order_seq;

本章知识点回顾

分类主要的数据库对象

 有些对象包含数据，主要是表和索引

42 / 74

 编程对象（如存储过程）和函数是可执行的代码

 视图和同义词是能够访问其它对象的对象

 表是保存使用列定义的行的二维结构

 表在模式中。模式名和表名形成唯一标识符。

列举类可用的数据类型

 最常见的字符数据类型有VARCHAR2、NUMBER和DATE

 还有其它许多数据类型

创建简单的表

 可以从头开始或者使用子查询创建表

 创建之后，可以添加、删除或者修改列定义

 表定义可以包含列的默认值

创建和使用临时表

 只有插入行的会话才能够访问临时表中的行

 针对临时表的DML不生成重做数据

 临时表只存在于会话的PGA或临时段

 临时表只在会话期间或事务期间（具体取决于创建方式）保存行

约束

 可以在创建表时定义约束，也可以在随后添加

 可以与列一起定义约束，或者在列之后的表级别定义约束。

 表级别约束可能比内联定义的约束更复杂

 表只能有一个主键。但可以有许多唯一键

 主键的功能等同于UNIQUE加上NOT NULL

 唯一约束不阻止多个空值的插入

 外键约束定义表之间的关系

索引

 要实施唯一约束和主键约束，就必须使用索引

 B*树索引不包含NULL，但位图索引包含NULL

 B*树索引可为唯一索引，也可为非唯一索引，这些将取决于是否接受重复键值。

 B*树索引适用于基数大的列，而位图索引适用于基数小的列。

 位图索引可以采用复合形式，可以基于函数，也可以降序排列。

 B*树索引可以是唯一的、压缩的和反向键。

视图

 简单视图有一个明细表（或基表），既不使用函数也不使用聚合

 复杂视图可以基于任何SELECT语句，不管多么复杂

 视图是模式对象。要在另一个模式中使用视图，必须用模式名限定视图名

 可以像查询表一样查询视图

 可以将视图联接到其它视图或者联接到表，它们可以被聚合，在有些情况下它们可以接受DML语句。

43 / 74

 视图仅作为数据字典结构存在。每当查询视图时，必须运行底层的SELECT语句。

同义词

 同义词是视图或表的另一个名称

 私有同义词是模式对象，公有同义词存在于用户模式之外，不需要指定模式名作为限定符就可以使用。

 同义词与视图和表共享相同的名称空间，因此可以与它们交互使用。

序列

 序列生成唯一值——除非指定了MAXVALUE或MINVALUE和CYCLE

 递增序列不需要提交，不能被回滚

 任何会话都能通过读取它的下一个值来递增序列。可以获得上次发送给会话的值，但不能获得上次发出的值。

44 / 74

第八章 DML与并发性

本章学习内容























描述各种数据操作语言（Data Manipulation Language, DML）语句

在表中插入行

更新表中的行

从表中删除行

控制事务

使用DML管理数据

识别和管理PL/SQL对象

监视和解决锁定冲突

解释撤销的作用

了解事务的撤销生成方式

管理撤销

练习8-1 使用INSERT命令

在这个练习中，我们将使用各种方法在表中插入行

（1） 使用SQL Developer或者SQL*Plus，连接到WEBSTORE模式

（2） 查询PRODUCTS、ORDERS、和ORDER_ITEMS表，确认当前存储了哪些数据

SELECT * FROM products;

SELECT * FROM orders;

SELECT * FROM order_items;

（3） 向PRODUCTS表中插入两行，并依次提供值

INSERT INTO PRODUCTS

VALUES

(PROD_L, '11G SQL EXAM Guide', 'ACTIVE', 60, SYSDATE,

20);

INSERT INTO PRODUCTS

VALUES

(PROD_L, '11G ALL-in-One Guide', 'ACTIVE', 100, SYSDATE,

40);

（4） 向ORDERS表中插入两行，并显示提供列名

INSERT INTO orders

(order_id, order_date, order_status, order_amount, customer_id)

VALUES

(order_l, SYSDATE, 'COMPLETE', 3, 2);

INSERT INTO orders

(order_id, order_date, order_status, order_amount, customer_id)

VALUES

(order_l, SYSDATE, 'PENDING', 5, 3);

（5） 向ORDER_ITEMS表中插入三行，使用替代变量：

INSERT INTO order_items VALUES (&item_id, &order_id, &product_id,

&quantity);

当系统提示时，提供值{1,1,2,5}、{2,1,1,3}、{1,2,2,4}

45 / 74

（6） 向PRODUCTS表中插入一行，计算PRODUCT_ID使它比当前的最大值还高100.这需要使用一个标量子查询：

INSERT INTO PRODUCTS

VALUES

((SELECT MAX(PRODUCT_ID) + 100 FROM PRODUCTS),

'11G DBA2 Exam Guide',

'INACTIVE',

40,

SYSDATE - 365,

0);

（7） 确认插入的行

SELECT * FROM products;

SELECT * FROM orders;

SELECT * FROM order_items;

（8） 提交插入：

COMMIT;

练习8-2 使用UPDATE命令

在这个练习中，我们将使用各种方法更新表中的行。假设TS表与【练习8-1】中图一样。

（1） 使用SQL Developer 或者SQL*Plus，连接到WEBSTORE模式

（2） 更新由主键确定的一行

UPDATE products

SET product_description = 'DBA1 Exam Guide'

WHERE product_id = 102;

这条语句应该返回消息“1 row updated”

（3） 使用不相等谓词并提供值，更新一组行

UPDATE products

SET product_id =

(1 + (SELECT MAX(product_id) FROM products WHERE product_id <>

102))

WHERE product_id = 102;

这条语句返回消息“1 row updated”

（4） 确认行的状态

SELECT * FROM products;

（5） 提交所作的变更：

COMMIT;

46 / 74

练习8-3 使用DELETE命令

在这个练习中，我们将使用各种方法删除表中的行。

（1） 使用SQL Developer 或者SQL*Plus，连接到WEBSTORE模式

（2） 在主键上使用相等谓词来删除一行

DELETE FROM products WHERE product_id = 3;

（3） 尝试通过省略WHERE子句来删除表中所有行

DELETE FROM products;

ORA-02292: integrity constraint (_PRODUCT_ID) violated - child record found

这条语句会失败，因为它违反约束，ORDER_ITEMS表中有子记录通过外键FK_PRODUCT_ID引用PRODUCTS表中的PRODUCT_ID值。

（4） 提交删除

COMMIT;

练习8-4 使用DML管理数据

本练习将示范事务隔离和控制。使用两个SQL*Plus会话（也可以使用SQL

Developer），都以用户SYSTEM的身份连接。按正确顺序运行两个会话在表中的命令。

步骤 第一个会话 第二个会话

CREATE TABLE t1 AS SELECT

1

* FROM all_users;

SELECT COUNT(*) FROM t1;

SELECT COUNT(*) FROM t1;

2

两个会话的结果相同

DELETE FROM t1;

3

SELECT COUNT(*) FROM t1; SELECT COUNT(*) FROM t1;

4

两个会话的结果不同，因为事务隔离隐藏了变更

ROLLBACK;

5

SELECT COUNT(*) FROM t1;

SELECT COUNT(*) FROM t1;

6

两个会话的结果相同

DELETE FROM t1;

7

SELECT COUNT(*) FROM t1; SELECT COUNT(*) FROM t1;

8

CREATE VIEW v1 AS SELECT *

9

FROM t1;

SELECT COUNT(*) FROM t1;

10

ROLLBACK;

11

SELECT COUNT(*) FROM t1;

12

DDL语句提交DELETE，因此它不能回滚

DROP VIEW v1;

13

DROP TABLE t1;

14

SELECT COUNT(*) FROM t1;

SELECT COUNT(*) FROM t1;

练习8-5 创建PL/SQL对象

本练习使用Database Control创建PL/SQL 对象，并且在SQL*Plus中执行这些对象。

（1） 使用SQL*Plus，以用户SYSTEM的身份连接数据库

（2） 创建一个本练习将要使用的表

CREATE TABLE integers(c1 NUMBER, c2 VARCHAR2(5));

（3） 使用Database Control，以用户SYSTEM的身份连接数据库。

47 / 74

（4） 在数据库的主页上选中Schema选项卡，单击Programs部分的Packages链接，然后单击Create按钮。

（5） 在Create Package窗口中，输入程序包的名称NUMBERS以及用于该程序包的源代码。单击OK创建这个程序包。

CREATE OR REPLACE PACKAGE numbers IS

FUNCTION odd_even(v1 NUMBER) RETURN VARCHAR2;

PROCEDURE ins_ints(v1 IN NUMBER);

END numbers;

（6） 在数据库的主页上选中Schema选项卡，单击Programs部分的Packages Bodies链接，然后单击Create按钮。

（7） 在Create Package Body窗口中，输入程序包的名称NUMBERS以及用于该程序包主体的源代码。单击OK按钮创建这个程序包主体。

CREATE OR REPLACE PACKAGE BODY numbers IS

FUNCTION odd_even(v1 NUMBER) RETURN VARCHAR2 AS

BEGIN

IF MOD(v1, 2) = 0 THEN

RETURN 'even';

ELSE

RETURN 'odd';

END IF;

END odd_even;

PROCEDURE ins_ints(v1 IN NUMBER) AS

BEGIN

FOR i IN 1 .. v1 LOOP

INSERT INTO integers VALUES (i, odd_even(i));

END LOOP;

END ins_ints;

END numbers;

（8） 在SQL*Plus会话中描述这个程序包，执行该过程并检查结果

（9） 通过删除程序包和表进行清理

DROP PACKAGE numbers;

DROP TABLE integers;

注意，此DROP语句将对行的插入执行COMMIT操作。

练习8-6 检测和解决锁争用

在本练习中，首先使用SQL*Plus制造问题，然后使用Database Control检测和解决问题。

（1） 使用SQL*Plus，以用户WEBSTORE的身份在两个会话中连接到数据库

（2） 在第一个会话中，锁定PRODUCTS表中所有的行

SELECT * FROM products FOR UPDATE;

（3） 在第二个会话中，尝试更新一行。会话将被挂起

UPDATE products SET stock_count=stock_count-1;

48 / 74

（4） 查看锁信息

SELECT ,#,_text

FROM v$lock l, v$session s, v$sql sq

WHERE =

AND _id = _id

AND = 'ACTIVE';

（5） Kill Session

ALTER SYSTEM KILL SESSION '140,60' IMMEDIATE;

练习8-7 使用撤销数据

本练习将研究数据库中的撤销配置和使用。可以使用SQL*Plus或SQL Developer。

（1） 以用户SYSTEM身份连接到数据库

（2） 使用如下查询，确定数据库正在使用撤销段还是回滚段：

SELECT VALUE FROM v$parameter WHERE NAME='undo_management';

这将返回值AUTO。否则，请发出此命令，然后重新启动实例：

ALTER SYSTEM SET undo_management=AUTO SCOPE = SPFILE;

Start up database force;

（3） 使用下面两个查询，确定已经创建的撤销表空间，以及正在使用哪一个：

SELECT tablespace_name FROM dba_tablespaces WHERE CONTENTS='UNDO';

SELECT VALUE FROM v$parameter WHERE NAME='undo_tablespace';

（4） 确定数据库中使用的撤销段及其大小：

SELECT tablespace_name, segment_name, segment_id, status

FROM dba_rollback_segs;

SELECT usn,rssize FROM v$rollstat;

49 / 74