show和set命令是两条用于维护SQL*Plus系统变量的命令
SQL> show all --查看所有68个系统变量值
SQL> show user --显示当前连接用户
SQL> show error --显示错误
SQL> set heading off --禁止输出列标题，默认值为ON
SQL> set feedback off --禁止显示最后一行的计数反馈信息，默认值为"对6个或更多的记录，回送ON"
SQL> set timing on --默认为OFF，设置查询耗时，可用来估计SQL语句的执行时间，测试性能
SQL> set sqlprompt "SQL> " --设置默认提示符，默认值就是"SQL> "
SQL> set linesize 1000 --设置屏幕显示行宽，默认100
SQL> set autocommit ON --设置是否自动提交，默认为OFF
SQL> set pause on --默认为OFF，设置暂停，会使屏幕显示停止，等待按下ENTER键，再显示下一页
SQL> set arraysize 1 --默认为15
SQL> set long 1000 --默认为80
说明：
long值默认为80，设置1000是为了显示更多的内容，因为很多数据字典视图中用到了long数据类型，如：
SQL> desc user_views
列名 可空值否 类型
------------------------------- -------- ----
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
命令列表：
假设当前执行命令为：select * from tab;
(a)ppend 添加文本到缓冲区当前行尾 a order by tname 结果：select * from tab order by tname;
（注：a后面跟2个空格）
(c)hange/old/new 在当前行用新的文本替换旧的文本 c/*/tname 结果：select tname from tab;
(c)hange/text 从当前行删除文本 c/tab 结果：select tname from ;
del 删除当前行
del n 删除第n行
(i)nput 文本 在当前行之后添加一行
(l)ist 显示缓冲区中所有行
(l)ist n 显示缓冲区中第 n 行
(l)ist m n 显示缓冲区中 m 到 n 行
run 执行当前缓冲区的命令
/ 执行当前缓冲区的命令
r 执行当前缓冲区的命令
@文件名 运行调入内存的sql文件，如：
SQL> edit s<回车>
如果当前目录下不存在s.sql文件，则系统自动生成s.sql文件，
在其中输入“select * from tab;”，存盘退出。
SQL> @s<回车>
系统会自动查询当前用户下的所有表、视图、同义词。
@@文件名 在.sql文件中调用令一个.sql文件时使用
save 文件名 将缓冲区的命令以文件方式存盘，缺省文件扩展名为.sql
get 文件名 调入存盘的sql文件
start 文件名 运行调入内存的sql文件
spool 文件名 把这之后的各种操作及执行结果“假脱机”即存盘到磁盘文件上，默认文件扩展名为.lst
spool 显示当前的“假脱机”状态
spool off 停止输出
例：
SQL> spool a
SQL> spool
正假脱机到 A.LST
SQL> spool off
SQL> spool
当前无假脱机
exit 退出SQL*PLUS
desc 表名 显示表的结构
show user 显示当前连接用户
show error 显示错误
show all 显示所有68个系统变量值
edit 打开默认编辑器，Windows系统中默认是notepad.exe，把缓冲区中最后一条SQL语句调入afiedt.buf文件中进行编辑
edit 文件名 把当前目录中指定的.sql文件调入编辑器进行编辑
clear screen 清空当前屏幕显示
-22-----------------------------------------------------------------------------------------------
第三章附：上机练习
内容：1.创建一数据库，启动实例并装配它。
2.通过访问数据字典了解数据库的结构及实例结构。
步骤：
一．创建数据库
用Netterm或Ptelnet，以Oracle8帐号登录uibm主机(IP:210.34.0.23)。
进入 /oracle/目录，ls查看其结构。
-------- clt1 ---- oradata -- ora
| |--- testdata -- test
|
|------- ctl2 ----- oradata -- ora
| |--- testdata -- test
|
|------- ctl3 … …
以上目录为各数据库中数据文件存放目录。
效仿以上的目录结构，在ctl1、ctl2、clt3目录下再建一个tstdata目录，并在各tstdata
目录下建立一个tst目录
cd /oracle/app/oracle/admin
此目录为Oracle各数据库的管理目录。
cd test
进入test目录了解Oracle目录组织结构(OFA结构)，结合ls命令。
ora ------- bdump 后台存储文件目录(BACKGROUP_DUMP_DEST的值)
|------ udump 用户转储文件目录(USER_DUMP_DEST的值)
|------ cdump 核心文件
|------ pfile init.ora和任何其它数据库初始化参数
|------ create 用于创建初始化数据和数据库对象的脚本
|------ SQL 数据库管理SQL文件
进入create目录阅读crdbtest.sql及crdb2test.sql这两个数据库ORA的创建脚本，进而知
道创建数据库的过程命令。
效防test下的目录结构，创建一名为tst的目录，拷贝test/pfile及test/create下的所有
文件到对应目录。
Cd /oracle/app/oracle/admin
Mkdir tst
Cd tst
Mkdir bdump
Mkdir udump
Mkdir cdump
Mkdir pfile
Mkdir create
Mkdir sql
Cp ../test/pfile/* pfile
Cp ../test/create/* create
进入tst/pfile目录，启动vi编辑器编辑configora.ora文件：
将所有与原来test目录有关的目录全以tst替换test.
如：原来的control_files参数中
把/oracle/ctl1/oradata/test/control01.ctl,改为
/oracle/ctl1/oradata/tst/control01.ctl,
需要改的地方还有：core_dump_dest
user_dump_dest
db_name 参数行
把文件configtest.ora改名为configtst.ora
inittest.ora 改名为inittst.ora
inittest_0.ora 改名为inittst_0.ora
编辑inittst.ora及inittst_0.ora文件，把它们中的前面的ifile指
定文件原来为:
ifile = /oracle/app/oracle/admin/test/pfile/configtest.ora
改为：
ifile = /oracle/app/oracle/admin/tst/pfile/configtst.ora
进入tst/create目录
把crdbtest.sql及crdb2test.sql改名为crdbtst.sql及crdb2tst.sql
编辑crdbtst.sql文件，修改以下行（将test改为tst）:
spool /oracle/app/oracle/admin/test/create/crdbtest.lst
startup nomount pfile= …
create database "test" 改为create database “tst”
将create database语句的用的character set改为ZHS16CGB231280，
原先为US7ASCII。ZHS16CGB231280为Oracle中支持中文国标的字符集名。
编辑crdb2tst.sql文件，将其做类似以上的修改(将创建命令中所用到的有关原来
test目录改为tst目录，并可以适当调节你所要建立数据库的相关数据文件大小。
修改ORACLE_SID环境变量值为新的SID名，此SID告诉oracle欲启动的实例名。
ORACLE_SID=tst;exprot ORACLE_SID
进入tst/create目录，启动服务器管理器(svrmgrl)执行crdbtst.sql脚本：
cd /oracle/app/oracle/admin/tst/create
svrmgrl @crdbtst.sql
执行后在svrmgrl状态下再执行crdb2tst.sql脚本。
Svrmgrl> start crdb2tst.sql
创建过程需要数分钟，请耐心等待 …
等上以脚本执行完毕，新的数据库已建立。此时，可以退出svrmgrl。
拷贝tst/pfile/inittst.ora文件至$ORACLE_HOME/dbs目录，省得每次启动svrmg
rl还要指定init.ora文件位置。在启动svrmgrl时，若没有特别指定init.ora的文件，ORA
CLE将在$ORACLE_HOME/dbs找init<SID>.ora作为其启动的初始化参数文件,SID为ORACLE_S
ID环境变量值。
再次启动svrmgrl(注意：ORACLE_SID值必须已改为新的SID值。)
svrmgrl
svrmgrl>connect internal;
svrmgrl>startup (启动数据库)
svrmgrl>start $ORACLE_HOME\dbs\catproc.sql
catproc.sql脚本安装Procedural Option所必需的脚本或PL/SQL对象及其支持的数据库结
构。
至此，我们已成功创建了一个新的数据库tst，并且我们也用实例tst来装配启动它。
为了能够让用户从远程访问此数据库，我们还必须配置Oracle的TNS（Transparent Netwo
rk Service），配置这一服务只要改变一下其配置文件listener.ora即可，最简单的办法
是拷贝一个副本备份，然后直接编辑它，把原来的实例名更换为新的实例名即可。更名后
，重新启动tnslistener进程即生效。
$lsnrctl stop (在操作系统状态下执行)
$lsnrctl start
更改SYS及SYSTEM用户的默让密码。
grant connect to sys(或system) identified by <new_password>
或 alter user sys identified by <new_password>
以上命令可以在服务器管理器状态下执行，也可以在Sqlplus下执行。
二、考察数据库及实例结构
启动sqlplus用SYS或SYSTEM用户连接。
或启动svrmgrl，connect internal
1. 查询实例启动时间。
select to_char(a.value,’J’)+b.value/86400,
’HH24:MI:SS DD-MON-RR’) start_time
from v$instance a,v$instance b
where a.key=’STARTUP TIME –JULIAN’ AND
b.key=’STARTUP TIME –SECONDS’;
观看实例存储器分配信息
SELECT name,bytes from v$sgastat
Where name in (‘free memory’,’fixed_sga’,’db_block_buffers’,
‘log_buffer’,’dictionary cache’,’library cache’,
‘sql area’);
查看进程实例进程
select spid,name from v$process, v$bgprocess where addr =paddr;
查看数据库用户
select username from dba_users;
查看活动的控制文件
select * from v$controlfile
查看回滚段信息
select a.segment_name,b.bytes,b.extents,a.tablespace_name,
c.shrinks,c.extends,c.hwmsize
from dba_rollback_segs a,dba_segments b,v$rollstat c
where a.segment_id=c.usn and a.segment_name=b.segment_name;
查看重做日志信息
select member,bytes,members,a.status
from v$log, V$logfile b
where a.group# = b.group#
order by member;
查看数据库链接
select spid,mame from v$sysstatprocess,v$sysstatbgprocess
where paddr(+)=addr;
查看多线程服务器进程
select * from v$dispatcher;
select * from v$shared_server;
第四章：SQL
(本次课在机房，结合上机讲授)
本章介绍SQL的基础知识。理解了SQL就理解了关系数据库。Oracle与数据库的所有交互都
使用SQL(Structured Query Language)。SQL*Plus是基于SQL但又具有Oracle特定功能的一
种工具，它可用来生成报表、控制屏幕显示和打印输出格式。
术语
下面介绍本章使用的一引技术术语：
■ DDL(Data Definition Language) 数据定义语言是SQL中定义数据库中数据的结构的
语言。定义数据时，将在Oracle的数据字典中生成数据项。常见的DDL关键字是create、r
evoke、grant和alter
DML(Data Manipulation Language) 数据操纵语言为SQL结构，用来操纵数据库中数据（而
非定义数据，定义数据由DDL完成）。常见的DML关键字为select、insert、update和dele
te。
在Oracle中，我们使用commit(提交)语句表示已经将修改后的数据保存到数据库。每次用
户保存结果时，Oracle将引用用户的提交操作。
约束(constraint) 是一种保证一个Oracle表的数据间关系或两不同表中数据间的一致性的
机制。
Oracle8数据库中一个对象(object)是一个有意义的事物，可在其内部存放信息。我们常谈
的对象类型——表和视图是两种最常见的。
利用如SQL*Plus这样的程序将信息从Oracle数据库中提取出来的操作称为查询(query)。
■ 回滚（Rollback）为当某个对话更改了数据库中的数据后，由于某种原因不想提交些更
改时Oracle所采取的操作。这是一个把信息恢复到用户update前状态的操作。
SQL语句有两大类：DDL和DML。下面我们进一步来看看二者的差异：
二．DDL
DDL数据定义语言是一组SQL命令，用于创建和定义数据库对象，并且将其保存在数据字典
中。
数据定义语言使用户能完成下列任务：
创建(create)数据库对象
删除(drop)数据库对象
更改(alter)数据库对象
为数据库对象授权(grant)
回收已授给数据库对象的权限(revoke)
当发布一条DDL SQL语句时，在每一条DDL语句执行前后，Oracle都将提交当前的事务，理
解这一上点很重要。因此如果用户插入（insert）记录到数据库中并且发布了一条DDL语句
，如create table，此时来自insert命令的数据将提交到数据库。
属于DDL的语句是自动提交的，这意味着当Oracle8通知用户比如“Revoke succeeded”，
此时命令已完成不能回滚了。
DDL语句部分列表：
alter procedure 重编译存贮过程
alter table 增加表列、修改表列、更改存贮分配
analyze 收集数据库对象的性能统计值并送入代价的优化器
alter table add constraint 在已有的表上增加约束
create table 创建表
create index 创建索引
drop index 删除索引
drop table 删除表
grant 将权限或角色授予用户或其它角色
truncate 删除表中所有行
revoke 从用户或数据库角色回收权限
三．DML
DML(数据操纵语言)允许用户对数据库中的数据进行insert、update、delete和select等操
作。正如名字所示，DML处理数据库中的数据内容。最常见的DML语句是insert、update、
delete和select。
Insert
Delete
Update
Select
Commit work 把当前事务所作的更改永久化（写入磁盘）
Rollback 作废上次提交以来的所有更改
在学习了两种主要类型的SQL语句后，下面作进一步的介绍。首先登录进SQL*Plus，
然后试一些最常见的DDL和DML语句。
四.SQL*Plus入门
学习SQL最简单的办法就是使用SQL*Plus。因此先登录到SQL*Plus。Oracle安装后有一用户
名scott,口令为tiger。我们可以用这个帐号登录试用。
有两种方式进入SQL*Plus:
1.使用客户端的的SQL* Plus 8.0。
此程序项在启动菜单栏的Oracle For Windows95组中。启动它，在connect对话框中按提
示输入用户名、口令及主机字符串。如果单机已装了Personal Oracle的，主机字符串可以
不填，否则填上SQL * Net已配置的service name，所连接的数据库实例在service里已定
义，这些定义可以使用Oracle Net8 Easy Config进行配置。
其结果保存在\ORAWIN95\NET80\ADMIN\TNSNAMES.ORA文件中。
使用Unix上的SQL*Plus
登录到UNIX主机上，
打入命令 sqlplus scott/tiger 或sqlplus然后再按提示输入用户名及口令
SQL*Plus: Release 8.0.4.0.0 - Production on Sat Jul 3 0:31:55 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.0.0 - Production
PL/SQL Release 8.0.4.0.0 - Production
SQL>
在进入SQL*Plus后，会看到SQL*Plus提示符SQL>
这时您就可以键入想试验的SQL语句。
下面我们讲一下与SQL缓冲器一起使用的SQL*Plus命令,这些可以帮助我们高效地输入命
令。
命令 缩写 动作
APPEND text A text 在行尾增加text
CHANGE old/new C old/new 在一行中将old文本改为new文本
CLEAR BUFFER CL BUFF 删除所有行
DEL 删除缓冲器中所有行
INPUT I 将一行或多行增加到缓冲器
INPUT text I text 增加一由text组成的行
LIST L 列出SQL*Plus缓冲器内容
LIST n Ln或n 列出行n
LIST * L * 列出当前行
LIST m n L m n 列出行m至行n
LIST LAST L LAST 列出缓冲器中的最后一行
RUN 运行缓冲器中的命令
SQL*Plus中的命令行以分号(;)结束。
下面我们给出一些SQL语句，大家可以在自己的机器上试验一下：
select table_name from user_tables
此命令用数据字典user_tables中列出用登录用户所拥有的表。
若用scott登录，列出的结果如下：
TABLE_NAME
------------------------------
BONUS
DEPT
DUMMY
EMP
HELP
SALGRADE
这些表是在数据库安装时建立的让用户试验学习的表，查看这些表的结构可以用descr
ibe命令，例如：
SQL>describe emp
查看emp表的结构，结果如下：
Name Null? Type
------------------------------- -------- ----
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL>select empno,ename from emp
查看表内容。
２.create语句
在任何数据库总是以DDL语句开始，因为创建数据库对象的工作是由DDL语句来完成的。
首先，我们将创建四个表：Customer、State、X和Y：
SQL>create table customer (
last_name varchar2 (30) not null,
state_cd varchar(2),
sales number)
tablespace users
storage (initial 25k next 25k minextents 1);
Table created.
SQL>create table state (
2 state_cd varchar(2) not null,
3 state_name varchar2(30);
Table created.
SQL>create table x(
2 col varchar2(30);
Table created.
SQL>create table y(
col varchar2(30));
Table created.
▲Null与Not Null
在创建customer表时，last_name表列后跟一个限定符“not null”，这表示数据库不
接受没有表列数据行到customer表中。换句话说，not null表列是强制性字段，在表cust
omer和state中，这意味着要在表中插入一行，last_name和state_cd字段必须含有值。
什么是空值(null value)
空(null)是不包括数据的表列。可以将null理解为长度为0的字符串。很多时候若不知道某
表列的类型可以给它赋一空值。但人们最容易犯的一个错误就是将空值加载到一个数值型
表列中，而问题在于“1+null=null”！因此，如果用户偶然将空值加载至数值域中，那么
产生的统计报表肯定不正确。
３．Insert
现在我们已经建立了一些表，让我们用不用DML语句，在我们建立的表上插入一些数据
，这些数据也将作为我们试验命令的数据。
SQL>insert into customer values (‘Teplow’,’MA’,23445.67);
SQL>insert into customer values (‘Abbev’,’CA’,6969.96);
每次成功地完成一条insert语句后，均返回建立信息，
1 row created.
该信息通知用户建立的行数。
SQL>insert into customer values (‘Porter’,’CA’,6989.99);
SQL>insert into customer values (‘Martin’,’CA’,2345.45);
SQL>insert into customer values (‘Laursen’,’CA’,34.34);
SQL>insert into customer values (‘Bambi’,’CA’,1234.55);
SQL>insert into customer values (‘McGraw’,’NJ’,123.45);
现在我们用稍加变化的insert命令的数据插入state表。我们将指定数据要插入的表列名。
这在处理大表时很有用，因为用户可能没有表中每一列的数据。例如：在一个预算系统中
，只有在月末才有实际的花销数。
SQL>insert into state (state_name,state_cd)
values (‘Massachusetts’,’MA’);
SQL>insert into state (state_name,state_cd)
values (‘California’,’CA’);
最后我们再插入一些数据到表X和表Y中。
SQL>insert into x values (‘1’);
SQL>insert into x values (‘2’);
SQL>insert into x values (‘3’);
SQL>insert into y values (‘3’);
SQL>insert into y values (‘4’);
SQL>insert into y values (‘5’);
４．Select
select命令用于从Oracle数据库中检索数据，select是用户最常用的ＳＱＬ语句，select
命令由四个基本部分构成：
1).select后跟用户要检索的信息（表或视图中的列名），这是select命令不可少的部分，
可用*号代表全部列。
2).from后跟检索对象(如存放数据的一个或多个表或视图的名称)，from部分也是必不可少
的。
3).where后跟检索条件，可选的。
4).order by后跟分类准则，可选的。
现在我们来查看我们刚才插入的数据：
SQL>select * from customer;
SQL>select state_name from state;
SQL>select * from x;
SQL>select * from y;
下面我们来看一下条件及范围检索：
select last_name,state_cd,sales from customer where state_cd=’MA’;
查看state_cd值为MA的所有客户。
select * from customer where state_cd=’CA’ and sales>6000
select * from customer where state_cd=’CA’ or sales>6000
select * from customer where state_cd!=’MA’;
带检索表
select * from customer where state_cd in (‘NJ’,’CA’);
带匹配条件
Select * from customer where last_name like ‘M%’;
Select * from customer where last_name like ‘%tin%;
总结：
=
!= 不等于
^= 不等于
^= 不等于
<> 不等于
<
>
<=
>=
in ( ) 等于括号内任一成员
not in ( ) 不等于括号内任一成员
between A and B 大于等于A与小于等于B
not between A and B 不大于等于A与小于等于B
like ‘%tin%’ 包括给定子串(即‘tin’)
Order by:
Select * from customer order by last_name desc;
Select * from customer order by last_name;
在order by子句中未指定升序或降序时，Oracle按升序排序。
5.Update、Delete和Alter
Update修改表中的数据
SQL>Update customer set sales=23890.66 where state_cd=’MA’;
若没有用where指定修改的条件行，将修改表中全部行。
Delete删除行数据
Delete from customer，将删除customer表的所有记录；delete from customer where
state_cd=’CA’，将删除state_cd为CA的客户记录。
Alter table修改表结构
此语句有如Foxpro中的Modify stru语句。在创建表后，用户可能想要增加表列。这时就
要用到alter table命令了。
Alter table customer add (sale_date date)；
将成功地把表列sale_date加到表customer中。
Alter table x modify(col date)，改变一个已存在表列的数据类型。
6．连接两个表
现实中，用户需要的大量数据往往存放在多个表中。很多情况下需要处理多个表。例如，
customer表中只存放州代码(state_code)，然而用户还想知道州名，这时需要将表custom
er与表state连接。这就要用到表的连接。通过定义，Oracle一类的关系数据库允许用户基
于公共域连接两个或更多表。这些公共域通常称为键域(key field)。
有两种类型的键：主键（primary）和外部键(foreign)。主键使表中的数据行保持唯一。
在表state中，state_cd就是主键。表customer中也包含有state_cd,此时的state_cd就是
外部键。一个表的外部键用于从其他（foreign）表中获取信息。
SQL>select * right.col,left.col from x right,y left
where right.col=left.col;
五．内部函数
数值型函数
函 数 返回值 样 例 显示
Abs(n) N的绝对值 Select abs(-321) from dual; 321
Ceil(n) 大于等于数值n的最大整数 Select ceil(10.6) from dual; 11
Floor(n) 小于等于数值n的最大整数 Select floor(10.6) from dual; 10
Mod(m,n) M除以n的余数，若n=0返回n Select mod(7,5) from dual; 2
Power(m,n) M的n次方 Select power(3,2) from dual; 9
Round(n,m) 将n四舍五入，保留小数点后m位 Select round(1234.5678,2) from dual 12
34.57
Sign(n) N=0,返回0;n>0,返回1;n<0,返回-1 Select sign(12) from dual; 1
Sqrt(n) N的平方根 Select sqrt(25) from dual; 5
Dual表拥有者为SYS，在句法正确，而数据库中没有其他表可用于该语句时，可使用dua
l表。
2.字符串函数
函 数 返回值 样例 显示
initcap(char) 把每个字符串的第一个字符换成大写 Select initcap(‘mr.telpow’)
from dual; Mr.Telplow
Lower(char) 整个字符串换成小写 Select lower(‘Mr.Frank Townson’) from dual; m
r.frank townson
Replace(char,str1,str2) 字符串中所有str1换成str2 Select replace(‘Scott’, ’
S’,’Boy’) from dual; Boycott
Soundex(char) 字符串的语音表示，查找发音相似拼写不同的字符串 Select last_name
from employee where soundex (last_name) = soundex(‘SMYTHE’); SMITH
Substr(char,m,n) 取出从m字符开始的n个字符的子串 Select substr(‘ABCDEF’,2,1)
from dual; B
Length(char) 求字符串的长度 Select length(‘Anderson’) From dual; 8
|| 并置运算符。
Select ‘Dear’||’John’||’:’ from customer
将返回 ‘DearJohn:’
3.日期型函数
函数 返回值 样例 显示
Sysdate 当前日期和时间 Select sysdate from dual;
Last_day 本月最后一天 Select last_day(sysdate) From dual
Add_month(d,n) 当前日期d后推n个月 Select add_months(sysdate,2) from dual;
Months_between (f,s) 日期f和s间相差月数 Select months_between(sysdate,’12-MAR
-99’) from dual;
Next_day(d,day) D后第一周指定day的日期 Select next_day(sysdate,’Monday’) fro
m dual;
Oracle缺省的日期格式为DD-MON-YY。为保证进入21世纪不出问题，请尽可能用四位数字的
年份。Oracle提供了一种特殊的世纪日期格式标记为DD-MON-RR。
常用日期格式
格式 返回值 样例
Y、YY或YYY 年的最后一位，两位或三位 Select to_char(sysdate,’YYY’) from dual;
SYEAR或YEAR 年，SYEAR公元前的年前加一负号 Select to_char(sysdate,’SYEAR’) fr
om dual;
Q 季度，1到3月为第一季度 Select to_char(sysdate,’Q’) from dual;
MM 月份数
Month 用9个字符长度表示月分(英文)
WW 当年第几周
W 本月第几周
D 周内第几天
DD 当月第几天
DY 周内第几天缩写(如：SUN)
HH 12进制小时数
HH24 24进制小时数
MI 分钟数
SS 秒数
类型转换
to_char 将任意类型的数据转换成字符串
to_number
to_date
六．格式化输出
在SQL*Plus中，有许多参数可以控制SQL*Plus的输出显示格式，利用SQL*Plus命令show a
ll用户能知道显示格式的当前设置。
SQL>show all;
appinfo is ON and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autotrace OFF
shiftinout INVISIBLE
blockterminator "." (hex 2e)
btitle OFF and is the 1st few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
echo OFF
editfile "afiedt.buf"
embedded OFF
… ...
1).行和页的大小
set linesize <n>
set pagesize <n>
例：
SQL>set linesize 80;
SQL>set pagesize 25;
2) 页头标、页脚标
ttitle <Title string>
btitle <Foot title string>
例： (connect as scott)
SQL>ttitle ‘Database Technoloies| Customer Report’;
SQL>select empno,ename from emp;
SQL>btitle ‘-----------Sample.sql------------‘;
SQL>select empno,ename from emp;
| 表示换行。
3).SQL*Plus结果到文件
spool <目标文件>
如：spool c:\temp\out.list
这个输出将放在spool命令指定的文件中。为停止假脱机(spooling)，可用命令spool o
ff或spool out。后者关闭输出文件并打印输出。
4).格式化输出列表
column
大多数情况下，用户需要格式化实例的表列数据。Column命令可完成这项工作。下面我们
先执行两条格式化命令，然后再查询customer表。
SQL>column last_name format a8 wrap heading ‘Last |Name’;
SQL>column state_cd format a8 heading ‘State | Code’ ;
8 表示显示宽度，
a 表示每个位置只能是字符
wrap 说明若last_name长度大于8个字符，多余的字符显示下一行的对应
位置。
Heading 部分告诉SQL*plus，last_name的列标。
现在我们查询一下customer表来看不下输出效果：
SQL>select * from customer ;
Sun Jul 04 page 1
Database Technologies
Customer Report
Last State
Name Code SALES
-------- -- ----------
Teplow MA 23445.67
Abbev CA 6969.96
Porter CA 6989.99
Martin CA 2345.45
Laursen CA 34.34
Bambi CA 1234.55
McGraw NJ 123.45
-------------------sample.sql---------------------
本章小结：
本章简要地介绍了SQL及其一些常用的命令，及SQL*Plus的应用。有关SQL的更详细的使用
说明可以参阅本第4章至第8章，课本在从建表到查询等操作都有较为详细的介绍，虽然书
中的章节较长，但其内容简单易懂，由于课时所限无法在此逐一介绍。建议大家可以在自
己的PC上安装个Personal Oracle 8，按课本的例子，进行学习试验。
第五章．PL/SQＬ
前面我们所使用的SQL语言，它不具备过程能力，但Oracle通过PL/SQL语言对SQL进行
了过程语言功能的扩展。PL/SQL是一种比较复杂的的程序设计语言，用于从不同环境中访
问Oracle数据库，。
PL/SQL是Procedural Language/SQL（过程性语言的缩写）。正如其名所表达的，PL/SQL通
过增加了用在其他过程性语言中的结构(construct)来对SQL进行了扩展，例如：
变量和类型（包括预定义的和用户定义的）
控制结构,例如IF-THEN-ELSE语句和循环。
过程和函数
对象类型和方法
过程性结构与Oracle SQL无缝地集成在一起，这样便产生了一种结构化的强有力的语
言。在使用Oracle的存储过程、数据库触发器、包和函数都要用PL/SQL编写代码。因此，
如果不了解PL/SQL就不能深入掌握Oracle。
PL/SQL具有高度的可移植性，在所有Oracle平台上都是标准化的。因为其数据类型基
于数据库服务器，所以语言完全与机器无关。你无需针对UNIX、Windows、Netware等等去
学习各种PL/SQL。PL/SQL程序可以在任何Oracle Server上编译和运行而无需进行任何修改
。
一．PL/SQL基础
下面我们通过实例程序来学习PL/SQL：
首先我们可以运行一下hello.sql这一简单的程序，此程序输出”Hello,world!”。
Hello.sql
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line(‘Hello, world!’);
END;
/
启动SQL*plus并以system帐号连接Oracle.
SQL>start c:\plssql\hello.sql
用以上命令运行些程序。
第1行让SQL*Plus写出服务器返回给它的内容。
第2行和第5 行提供当前块的作用范围。
第3行打开输出机制。
第4行打印簇“Hello, world!”。
第6行执行这个无名PL/SQL块。
服务器响应如下：
Hello, World!
PL/SQL过程已成功完成.
SQL>
在PL/SQL中字符串用单引号围起来，PL/SQL对文字大小写唯一敏感的地方是在字符串
是，对一些变量、命令等大小写不敏感。
数据文字可以任何整数或浮点数值，例如：
整数文字
-12345.0 浮点数文字
1234.567890 浮点文字可以是任意精度
这也是浮点文字，精度为零
1.2345E2 可以使用科学计数
1.2345E-3
0.123 或 .123 开头的0是可选择的
下面我们再来看另一个程序:
Circle.sql
DECLARE
PI CONSTANT REAL:=3.14159265359; -- PI常量值
Circumference REAL; --周长
Area REAL; --面积
Radius REAL:=&Radius; --半径
BEGIN
Circumference:=PI*radius*2.0;
Area:=PI*radius**2;
DBMS_OUTPUT.put_line(‘Radius=’||To_CHAR(radius)||
’,Circumference=’||To_CHAR(circumference)||
‘,Area=’||To_CHAR(area));
END;
/
SQL> start c:\plsql\circle.sql
在运行这个程序时，SQL*Plus首先提示你给&号指定的联编变量指定一个值（第5行）屏幕
显示信息为：
输入radius的值： 5
原值 5： Radius REAL:=&Radius;
新值 5： Radius REAL:=5;
Radius=5,Circumference=31.4159265359,Area=78.53981633975
PL/SQL 过程已成功完成。
SQL>
程序注释
单行注释
单行注释由两个连字符开始，后面一直到行尾都是注释（回车符标识着注释的结束）。
如上边程序中的：
PI CONSTANT REAL:=3.14159265359; -- PI常量值
如果行注释超过一行，必须在每一行的开头上使用双连字符(--)。
多行注释
多行注释由/*开始，由*/结束。这是C语言中使用的注释风格。如circle.sql中的开头部分
所示。
多行注释可以扩展到任意多的行上，但它们不能嵌套。
PL/SQL块结构
PL/SQL块是基本的编程结构，用块结构进行编程适用于自上而下的结构化积木式编程和直
观逻辑组织。
一个无名PL/SQL块有三部分：说明部分、正文（体）部分和异常部分。其中异常部分为可
选项。
DECLARE
---- declarations (说明)
BEGIN
----executable code(执行代码)
EXCEPTION
---- exception handlers(异常处理代码)
END;
实际上说明部分也是可选项，但不声明变量是不能执行实质性的工作。用户定义的全部变
量、常数、数据类型、指示器、函数和过程均在这一部分中说明。若没有定义其中任何一
个，你可以略去这一部分。
变量声明
PL/SQL提供了SQL没有的附加数据类型。除一般的Oracle SQL数据类型外，PL/SQL还可
以让您用用这些数据类型对变量进行说明：
BOOLEAN 布尔类型 可用预定义常量TRUE、FALSE或NULL对一个布尔变量赋值。
BINARY-INTEGER 该类型适用于在-2,147,483,647到2,147,483,643
(二进制整数) 范围内的带符号整数
NATURAL(自然数) 是BINARY-INTEGER的一个子集，这种数据类型是整数集的
一部分，从0到2,147,483,647。
POSITIVE（正整数） 是BINARY-INTEGER的另一个子集, 这种数据类型是整数
集的一部分，从0到2,147,483,647。
%TYPE 这种设计可使您说明一个变量的数据类型与某一指定列的
数据类型相同，其结果产生更易于维护的PL/SQL代码。
%ROWTYPE 用这种数据类型您可以说明一个复合变量，与一特定表中的
一行相同，这种复合变量是由引用表中的列名和数据类型组成的。
除此之外，PL/SQL还提供两种复合数据类型：TABLE类型和RECORD型。我们将在以后介
绍。
变量作用域
变量在仅它所在的块内块内是可见的。
<<l_outer>>
DECLARE
V_AvailableFlag BOOLEAN;
V_SSN NUMBER(9);
BEGIN
fds
DECLARE
V_SSN CHAR(11);
BEGIN
--v_SSN(char 11)是可见的，要引用v_SSN(number 9)可用
-- l_outerV_SSN
END;
END;
变量名风格
变量名的关键是它们是描述性的。声明
x number;
不会告诉您有关x的用途的任何事情。但是
v_StudenID NUMBER(5);
将告诉我们该变量可能要用来存储学生ID号，尽管在声明旁边没有解释的注释。请记住，
PL/SQL标识符的最大长度是30个字符，所有的字符都可以用来传递一些含义的。30个字符
通常足以用来存储一个描述性的名称了。
变量名也可以告诉我们该变量的用途。有的人使用下划线将一个字母代码和变量的其他部
分分隔来以指明这一点。例如：
v_VariableName 程序变量
e_ExceptionName 用户定义异常
t_TypeName 用户定义类型
p_ParameterName 过程或函数参数
c_ConstantValue 常量
下面我们再来看一个程序, loop.sql (参见课本P198)
CREATE TABLE test_table (record_number number(3),current_date date);
DECLARE
max_records CONSTANT int:=100;
I int:=1;
BEGIN
FOR I in 1..max_records LOOP
If (mod(i,10)=0) then
INSERT INTO test_table
(record_number,current_date)
VALUES
(I, SYSDATE);
ELSE
NULL;
END IF;
END LOOP;
COMMIT;
END;
/
COLUMN current_date FORMAT a20
SELECT record_number,to_char(sysdate,'HH24:MI SS') FROM test_table;
DROP TABLE test_table;
循环语句 (P.195)
FOR-LOOP 与 WHILE-LOOP
语法：
FOR loop variable IN [REVERSE] lower-bound..upper-bound LOOP
Statement; … statemnet;
END LOOP;
WHILE condition LOOP
Statement; … statement;
END LOOP;
Condition是一个有效的PL/SQL条件；
Statement是一个有效的PL/SQL语句。
简单的LOOP语句
语法：
LOOP
Statement; … statement;
END LOOP;
无条件循环，为了跳出循环，可在当一个条件被满足时执行EXIT语句。
EXIT语句：
EXIT [lable-name] WHEN condition;
IF语句
IF condition THEN
Statement; … statement;
[ELSIF condition THEN
Statement; … statement;
…
[ELSIF condition THEN
statement; … statement;
[ELSE
statement; … statement;]
END IF;
注意拼写：是ELSIF而不是ELSEIF；END IF而不是ENDIF。
使用过程
过程是执行少量重复工作、严格地通过参数列表传入和传出值的子例行程序。
Table.sql
SET SERVEROUTPUT ON
DECLARE
-- 常量
TB CONSTANT VARCHAR2(1):=CHR(9); -- TAB
-- 变量
status NUMERIC;
table_rec all_tables%ROWTYPE;
-- 例程
PROCEDURE get_table(Powner IN all_tables.owner%TYPE,
Ptable IN all_tables.table_name%TYPE,
Prec OUT all_tables%ROWTYPE,
Pstatus IN OUT NUMBER) IS
-- Local cursors
CURSOR table_cur (Cowner all_tables.owner%TYPE,
Ctable all_tables.table_name%TYPE) IS
SELECT *
FROM all_tables
WHERE owner=Cowner AND table_name = Ctable;
-- 局部变量
Lowner all_tables.owner%TYPE;
Ltable all_tables.table_name%TYPE;
BEGIN
Pstatus:=0; -- OK
Lowner:=UPPER(Powner);
Ltable:=UPPER(Ptable);
OPEN table_cur(Lowner, Ltable);
FETCH table_cur INTO Prec;
IF (table_cur%NOTFOUND) THEN
RAISE NO_DATA_FOUND;
END IF;
CLOSE table_cur;
EXCEPTION
WHEN OTHERS THEN
BEGIN
Pstatus:=SQLCODE; -- 捕获错误代码
IF (table_cur%ISOPEN) THEN
CLOSE table_cur;
END IF;
Prec:=NULL;
DBMS_OUTPUT.put_line('get_table:'||SQLERRM(Pstatus));
DBMS_OUTPUT.put_line('OWNER='||'<'||Lowner||'>');
DBMS_OUTPUT.put_line('TABLE ='||'<'||Ltable||'>');
EXCEPTION
WHEN OTHERS THEN
NULL; --don't care(avoid infinite loop)
END;
END get_table;
BEGIN
DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line('TABLE'||TB||'TABLESPACE'||TB||
'INITIAL'||TB||'NEXT'||TB||'MAX');
DBMS_OUTPUT.put_line(RPAD('-',43,'-')); -- Just an under line
Get_table('scott','dept',table_rec,status);
IF (status=0) THEN
DBMS_OUTPUT.put_line(
Table_rec.table_name ||TB||
Table_rec.tablespace_name ||TB||
Table_rec.initial_extent ||TB||
Table_rec.next_extent ||TB||
Table_rec.max_extents);
END IF;
Get_table('scott','garbage',table_rec,status);
IF (status =0 ) THEN
DBMS_OUTPUT.put_line(
Table_rec.table_name ||TB||
Table_rec.tablespace_name ||TB||
Table_rec.initial_extent ||TB||
Table_rec.next_extent ||TB||
Table_rec.max_extents);
END IF;
END;
/
游标的使用：
参阅课本P.251
说明游标 (declare)
打开（open）游标
用游标提取数据行(fetch)
关闭(close)游标
定义过程：
PROCEDURE procedure-name [(argument1[,argumentN])] IS
[局部变量声明]
BEGIN
Executable-section
[exception-section]
END [procedure-name];
参数说明
IN 参数的值传递给过程或函数，但是没有返回给调用它的PL/SQL子程序。在
一个过程或函数内部，您不能给说明为IN的参数赋值，只能引用这种类型参数的值。
OUT 过程或函数不能使用参数传递的值，但给调用它的PL/SQL子程序一个返回
值。在一个过程或函数内部，您不能用引用说明为OUT的参数值，只能给这种类型参数赋值
。
IN OUT 参数的值传递给过程或函数，并且给调用它的PL/SQL子程序一个返回值。
如果说明一个参数为IN OUT类型，则在过程或函数内部，既可引用这种类型参数的值又可
给其赋值。
函数说明：
一个PL/SQL函数说明与过程说明很相——只是函数返回一个预定义的数据类型的值。
说明一个函数的语法如下：
FUNCTION function-name [(argument1 [,argumentN])
RETURN function-datatype IS
[local-variable-decarations]
BEGIN
Executable-section
[exception-section]
END [function-name];
二．使用存储过程或函数
存储过程或函数是存储在Oracle数据库中的PL/SQL程序，可由用户直接或间接调用。使用
存储过程和函数的主要优越性有以下几点：
.提高了效率 ： 在客户机应用向数据库服务器提出对SQL的需求。随着户数的的增加，SQ
L请求也就不断地增加，使网络很快就成为运行的瓶颈。使用存储过程可使运行性能得到显
著的改进，因为对存储过程的一次调用，即用了在服务器中执行的多个SQL语句，从而减少
了网络的拥护。
.可重用性: 一个PL/SQL程序只需编写一次，即可用在各种地方——SQL脚本、数据库触发
器和客户机应用程序。
.可移植性：您可在任何Oracle数据库中使用存储过程，而不用考虑平台问题，所以您不需
要处理象操作系统或编译版本中出现的兼容性问题。只要平台支持Oracle，不需要做任何
改变就可调用存储过程。当然如果存储过程包含对文件和路径名的引用，您就需要另做一
些改变。
.可维护性：一个存储过程用于完成一个特写的任务，如数据库触发器、SQL*Plus脚本、应
用程序或其他存储过程可能需要调用该过程。从所有这些地方均可调用一个存储过程，这
样可降低软件维护的成本。
建立存储过程或函数
CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument1 [,argumentN])] IS
[local-variable-declarations]
BEGIN
Executable-section
[exception-section]
END procedure-name;
CREATE [OR REPLACE] FUNCTION function-name
[(argument1 [,argumentN])] IS
[local-variable-declarations]
BEGIN
Executable-section
[exception-section]
END function-name;
检索存储过程
建立存储过程后，您也许想看一看PL/SQL程序的源代码，不过，用于建立存储过程的SQL脚
本是不可见的，尽管如此，您还是可以使用查询Oracle数据字典视图的方法检索存储过程
的源代码。
其中一个视图名为：USER_SOURCE
(另一个DBA_SOURCE)
它提供以下四个列：
NAME 包括过程、函数、包或包体的名字。
TYPE 指出源代码是否属于过程、函数包还是包体。
TEXT 包含源代码的一行。
LINE 包含在TEXT中源代码包含的行数。
假设我们已建立了名为DROP_CLASS的存储过程，如果您想看DROP_CLASS的源代码，查询US
ER_SOURCE数据字典视图，那么用下面的SQL命令：
select line,text from user_source
where name=’DROP_CLASS’ order by line;
获得过程、函数、包或包体的目录列表
我们可以查询USER_OBJECTS获得属于当前所连接的Oracle帐号的存储过程、函数、包或
包体的目录列表。如果您想看不管属于谁的所有对象，可以查询DBA_OBJECTS。
Select distinct object_type from user_objects;
三．使用提供的ORACLE数据库程序包
Oracle数据库提供两组程序包：一组是标准程序包，它向Oracle Server提供基本功能，如
你的基本子类型说明和数据类型转换例行程序；另一组程序包供DBA和开发人员使用。这些
程序包由它们的名字区分，名字以DBMS_或UTL_打头，意思是它们与数据库打交道或提供通
用的实用程序。
表4.1 提供的程序包汇总
程序包名 程序包头文件 描述
DBMS_ALERT Dbmsalrt.sql 数据库事件的异常处理
DBMS_APPLICATION_INFO Dbmsutil.sql 记录当前运行的应用程序名（用于性能监控）
DBMS_DDL Dbmsutil.sql 重新编译存储的子程序和程序包，分析数据库对象
DBMS_DESCRIBE Dbmsdesc.sql 描述存储子程序的参数
DBMS_JOB Dbmsjob.sql 在指定时间或时间间隔内运行用户定义的工作
DBMS_LOCK Dbmslock.sql 管理数据库锁定
DBMS_OUTPUT Dbmsotpt.sql 将文本行写入缓冲器供以后检索和显示
DBMS_PIPE Dbmspipe.sql 在会话之间通个存储“管道”存储和发送信息
DBMS_REFRESH Dbmssnap.sql 管理可以一起刷新的快照组
DBMS_SESSION Dbmsutil.sql 有计划地执行alter session语句
DBMS_SHARED_POOL Dbmspool.sql 观察和管理内容
DBMS_SNAPSHOT Dbmssnap.sql 刷新、管理快照和清除快照
DBMS_SPACE Dbmsutil.sql 获取段空间信息
DBMS_SQL Dbmssql.sql 执行动态SQL和PL/SQL
DBMS_SYSTEM Dbmsutil.sql 关闭/打开给定会话的SQL跟踪
DBMS_TRANCACTION Dbmsutil.sql 管理SQL事务
DBMS_UTILITY Dbmsutil.sql 各种实用程序
UTL_RAW Utlraw.sql 用于RAW数据类型的簇函数
UTL_FILE Utlfile.sql 读/写基于ASCII的操作系统文件
UTL_HTTP Utlhttp.sql 从给定的URL获取HTML格式的页面
DBMS_LOB Dbmslob.sql 管理大对象
在您可以使用Oracle提供的程序包内的例行程序时，您应该首先检查它们是否存已经安装
和是否有效。DBA可以运行下列查询：
SELECT object_name,object_type,status
FROM dba_objects
WHERE owner=’SYS’ AND object_type LIKE ‘PACKAGE%’
ORDER BY object_name,object_type;
Oracle提供的程序包存放在￥$ORACLE_HOME\rdbms\admin目录下，你可以对这些程序
包中的每一个检查其程序包头文件看一看有什么例行程序和全局变量可以使用。
在以上所述目录下，我们还可以看到以prvt*.sql 和prvt*.plb形式存在的文件。前者
是提供的程序包包体，为ASCII格式。后者是程序包包体的二进制编译版的程序包包体。这
些代表程序包包体的发布形式。
第六章：DBA
本章将介绍怎样成为数据库管理员（DBA）。DBA工作的贡献在于使所有运行在Oracle数据
库上的系统能够有效地工作。DBA为每一个用户提供技术支持，并且应当熟悉Oracle软件方
面的所有技术出版物。DBA具有以下职责：
Oracle数据库的日常操作
Oracle软件的安装与升级
性能协调
备份与恢复管理策略
个人数据库管理咨询
开发者咨询
一．启动与关闭数据库
在大多数情况下，当启动数据库时，用户只需要简单地启动Server Manager，连接到数据
库，然后输入“Startup”使系统运转。现在我们介绍辅助启动命令选项。当需要时可在S
erver Manager中使用这些选项。
1.启动命令选项
1).正常启动（Startup Normal）
这是默认的启动方式（可省略Normal一词）。在前面的学习中，我们已多次使用这种方
式启动了数据库。
SVRMGR> connect internal
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 16113888 bytes
Fixed Size 45280 bytes
Variable Size 7802880 bytes
Database Buffers 8192000 bytes
Redo Buffers 73728 bytes
Database mounted.
Database opened.
2).安装启动(Startup Mount)
这种方式用于改变数据库的归档或执行恢复状态。此时数据未打开，所以不允许用户访
问数据。启动服务器管理器后，按下述步骤操作：
SVRMGR>connect internal;
SVRMGR>Startup Mount;
3).非安装启动(Startup Nomount)
这种方式用于重建控制文件或重建数据库。由于数据库没有打开，因此不允许用户访问
。启动服务器管理器后，按下述步骤操作：
SVRMGR>connect internal;
SVRMGR>Startup Nomount;
ORACLE instance started.
Total System Global Area 16113888 bytes
Fixed Size 45280 bytes
Variable Size 7802880 bytes
Database Buffers 8192000 bytes
Redo Buffers 73728 bytes
Database mounted.
4).约束启动(Startup Restrict)
使用这种方式能够启动数据库，但只允许具有一定特权的用户访问。该命令的输出信息与
不受限方式的启动命令的信息相同。数据库已打开，如果非特权用户试图登录，则将出现
下列错误信息：
使用alter system disable restricted session可以使数据库处于非约束访问方
式，用户可以再次登录。
5).强制启动(Startup Force)
当不能关闭数据库时，采用强制启动先项可处理这种罕见状态。首先关闭数据库，然后执
行无选项的数据库启动操作。启动操作与执行“Startup”命令相同。启动服务器管理后，
按下述步骤操作：
SVRMGR>connect internal;
SVRMGR>Startup force
完成上述操作后，数据库被打开，用户可访问信息。
6).带初始化参数启动(Startup Pfile)
启动命令使用该选项不影响数据库的运行方式，而只是定义了初始化参数文件（pfile）的
名称及位置。正如前面所讨论，初始化参数文件由Oracle打开数据库时读取。Oracle期望
已定位的缺省的初始化参数文件名。
SVRMGR>connect internal;
SVRMGR>Startup pfile= parameter_file_name
关闭选项
当Oracle数据库不运行时，称其为停止（down）。关闭数据库有多种理由，有些软件做备
份时关闭数据库；当升级或安装一些新的Oracle软件时，在升级或安装对话期间，Oracle
数据库必须关闭。
Showdown immediate 立即关闭，绝大多数情况下使用这种关闭方式。在用户别无选择时使
用shutdown abort选项关闭数据库，这是作为最后一种手段，非不得已的情况下才用。如
果使用中止（abort）选项关闭数据库，应立即启动数据库再做一次正常关闭，经验表明这
是处理中止关闭的最好办法。
二．用户及用户权限管理
建立用户
建立一个用户，实际上是建立一个安全、有用的帐号，它具有适当的权限和正确的缺省设
置。使用create user命令要建立一个新的数据库帐户，当该帐户建立后，在授权前它没有
任何效力，甚至不能注册。
所有用户帐号所需的设置都可由一个create user命令来指定。这些设置可以包括下表所有
的参数值。
参 数 使 用
Username 模式名
Password 帐号口令，也可以是直接与操作系统主机帐号相连
Default Tablespace 缺省表空间用来存储在该模式下建立的对象。这个并不是真正给用户
建立的对象，而仅是设置一个缺省值
Temporary Tablespace 这个表空间只用来存储排序处理的临时段
Quota [on tablespace] 充许用户在限额指定尺寸的表空间存储对象
Profile 给用户指定一个环境文件。如未指定，则使用缺省环境文件。环境文件是用来限
制对系统资源的使用的执行口令管理规则
下面列出一个简单的create user命令。在这个例子中，建立一个叫Rudolf和反用户，
口令为lamb，缺省表空间为USERS，临时表空间为TEMP，没有限额，使用缺省环境文件。
Create user rudolf
Identified by lamb
Default tablespace USERS
Temporary tablespace TEMP;
由于没有指定环境文件，数据库使用缺省环境文件。环境文件名为DEFAULT；其初始值是由
UNLIMITED设置的所有资源消耗的限值。
由于没有指定限额，用户就不能在数据库建立对象。
当授权资源限额时，使用create user或alter user的quota参数，如下面所列出的。这个
例子中，授予rudolf的限额为USERS表空间的10MB。
Alter user rudolf
Quota 10M on users
这时用户Rudolf可以在USERS表空间中建立最多为10M的数据段。用户不需要临时表空间中
空间限额来建立他们查询时的临时段。
除用户名外，create user命令的参数都可以由alter user命令来替换。
2．删除用户
可以用drop user命令来把一个用户从数据库中完全删除。这个命令只有一个参数——c
ascade，它能删除用户模式中的所有对象。如果是用户固有对象，为删除用户，你必须先
指定cascade。下面举例说明drop user命令：
drop user rudolf cascade
任何视图、同义词、过程、函数或数据包等被引用的对象在删除用户都会标上INVALID
。如果不久后另一个用户建立一个同样的名字，那么他在他的前任用户继承不到任何东西
。
3．系统级权限
可以使用系统级角色分配有效的系统级命令，以管理数据库。也可以建立用户系统级角
色或使用自身所带的有效权限，可以通过系统级角色授予，见下表：
权限 授权能力
对象管理
CREATE [ANY] CLUSTER
CREATE [ANY] DIRECTORY
CREATE [ANY] INDEX
CREATE [ANY] LIBRARY
CREATE [ANY] PROCEDURE
CREATE [ANY] SEQUENCE
CREATE [ANY] SNAPSHOT
CREATE ANY SYNONYM
CREATE [PUBLIC] SYNONYM
CREATE [ANY] TABLE
CREATE [ANY] TRIGGER
CREATE [ANY] TYPE
CREATE [ANY] VIEW
ALTER [ANY] CLUSTER
ALTER [ANY] INDEX
ALTER [ANY] PROCEDURE
…
DROP ANY CLUSTER
DROP ANY DIRECTORY
DROP ANY
…
BACKUP ANY TABLE
COMMENT ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
EXECUTE ANY TYPE
EXECUTE ANY PROCEDURE
数据库管理
CREATE [PUBLIC] DATABASE LINK
CREATE PROFILE
CREATE ROLE
CREATE ROLLBACK SEGMENT
CREATE TABLESPACE
CREATE USER
ALTER PROFILE
ALTER RESOURCE COST
ALTER ANY ROLE
ALTER ROLLBACK SEGMENT
ALTER TABLESPACE
ALTER USER
DROP PROFILE
DROP PUBLIC DATABASE LINK
DROP ANY ROLE
DROP ROLLBACK SEGMENT
DROP TABLESPACE
ALTER DATABASE
ALTER SYSTEM
ANALYZE ANY
ANUDITANY AUDIT SYSTEM
BECOME USER
FORCE [ANY] TRANSACTION
GRANT ANY PRIVILEGE
GRANT ANY ROLE
MANAGER TABLESPACE
RESTRICTED SYSTEM
SYSDBA 可以建立数据库一样执行系统管理功能(recover、startup、shutdown)
SYSOPER 可以执行与数据库备份、启动/关闭有关的系统管理功能（装载、恢复、打开和
关闭数据库）
使用grant命令给用户授权，grant命令的子句with grant option，是用于给被授予都
传递授予用户权限的能力。
Oracle提供了8个系统级角色。它们分别是CONNECT，RESOURCE，DBA，IMP_FULL_DATAB
ASE,EXP_FULL_DATABASE,SELECT_CATALOG_ROLE,EXECUTE_CATALOG_ROLE和DELETE_CATALOG
_ROLE。使用这些角色可以用来限制数据库管理角色的系统级权限。
CONNECT角色是授予最终用户的典型。尽管它具有一些建立对象的能力（如CREATE TABL
E权限），但它不能给用户任何表空间的额度。由于用户没有表空间，所以也就不能建表。
RESOURCE角色是授予开发人员的，是开发人员最有效的角色。
DBA角色包括了所有的系统级权限。
IMP_FULL_DATABASE和EXP_FULL_DATABASE是分别用于执行整个数据库输入、输出操作。
这些角色是DBA角色的一部分，可以使用这些角色来授予用户有限的数据库管理权限。
SELECT_CATALOG_ROLE、EXECUTE_CATALOG_ROLE和DELETE_CATALOG_ROLE角色是Oracle8新增
的。
如果用户被授予DELETE_CATALOG_ROLE角色，用户就可以从表SYS.AUD$中删除记录，SYS.A
UD$表是写审计记录的表。
SELECT_CATALOG_ROLE和EXECUTE_CATALOG_ROLE角色是给用户授予选择和执行输出表数据字
典对象的权限。
现在，我们建立一个新的系统级角色，叫做ACCOUNT_CREATOR。它只能建立用户而不能任
何其他DBA命令。建立这个角色的命令如下：
create role ACCOUNT_CREATOR;
grant CREATE_SESSION,CREATE USER,ALTER USER
to ACCOUNT_CREATRO;
一个用户可能被授予多个角色，可以通过alter user命令的子名default role来修改一个
用户的缺省角色。例如，可以修改使用用户不允许有缺省角色：
alter user rudolf default role none;
可以指定角色允许：
alter user rudolf default role CONNECT;
可以指定角色在对话开始时禁止。
Alter user rudolf default all except ACCOUNT_CREATOR;
4.用户环境文件
可以使用环境文件来限制系统和数据库资源向用户开放和管理口令限制。如果数据库
中没有建立环境文件，则用缺省环境文件，它对所有用户开放，资源没有限制。
通过资源环境文件可以限制用户以下资源：
SESSIONS_PER_USER 在一种情况下一个用户可以同时有多个对话
CPU_PER_SESSION 一个对话可用数个百分之一秒
CPU_PER_CAL 语法分析、执行、取数可用数个百分之一秒
CONNECT_TIME 一个数据库可连接数分钟的对话
IDLE_TIME 允许处在非激活状态的分钟数
LOGICAL_READS_PER_SESSION 一个对话可以读数个数据块
LOGICAL_READS_PER_CALL 在一个分析、执行、取数据期间可以读数个数据库块
PRIVATE_SGA 一个对话的私有空间可分配在SGA的shared SQL poos(对于MTS)
COMPOSITE_LIMIT 一个复合式限制，它是基于前面的限制
FAILED_LOGIN_ATTEMPS 连续多次注册失败引起一个帐号死锁
PASSWORD_LIFE_TIME 一个口令在其终止前可用天数
PASSWORD_REUSE_TIME 一个口令必须过几于才能重用
PASSWORD_REUSE_MAX 一个口令在重新使用前必须改变次数
PASSWORD_LOCK_TIME 如超过了FAILED_LOGIN_ATTEMPS设置次数，一个帐号将被锁住天数
PASSWORD_GRACE_TIME 当已达到PASSWORD_LIFE_TIME时之后的缓期天数
PASSWORD_VERIFY_FUNCTION 一个函数名，用于判断口令的组成，由Oracle提供并可编辑
注意：PASSWORD_REUSE_MAX和PASSWORD_REUSE_TIME是不相容的。如果设置了其中一个的
值，则另一个必需为UNLIMITED。
环境文件是通过create profile建立的, 可以通过alter profile对已有的环境文件进行修
改。
例：如果设置用户环境文件的FAILED_LOGIN_ATTEMPS资源为5，该帐号允许被连续注册失败
5次，第6次就会引起帐号死锁。下面列出建立LIMITED_PROFILE环境文件，用户名为JANE:
Create profile LIMITED_PROFILE limit
FAILED_LOGIN_ATTEMPS 5;
Create user JANE identified by HELLO
Profile LIMITED_PROFILE;
Grant CREATE SESSION to JANE;
如果现在JANE帐号连续6次失败，那么失败该帐号就会被Oracle自动锁住。要对帐号解
锁，在DBA帐号下使用如下命令：
alter user JANE account unlock;
注册期间的口令安全
当从一个客户机器与数据库服务器连接或通过数据库链接从一个数据库连接到另一个
时，Oracle将输入的口令以非加密形式传送，除非指定其他形式。对于Oracle8，可以设置
参数来强制Oracle传送前对口令值编码。要允许口令加密，需
设置以下参数：
对客户机，把sqlnet.ora文件的ORA_ENCRYPT_LOGIN参数设为TRUE。
对服务器，把init.ora文件的DBLINK_ENCRYPT_LOGIN参数设置为TRUE。
当设置这些参数后，并且数据库关闭和重启动，口令以加密形式在客户机到服务器和服务
器到服务器之间传送。
三．表空间管理
存储参数意义
一个段所使用的空间由它的存储参数决定。这些参数又是在段生成时，由数据库来确定的
；如果在create table 、create index、create cluster或create rollback segment命
令中，没有指明存储参数，则会使用表空间中存储的缺省参数。存储参数指定了initial(
初始化)扩展块尺寸，next（下一个）扩展块尺寸，以及pctincrease、maxextents、minextents值。在段生成后
，不能改变initial和minextents值。每一个表空间的缺省存储参数一般存储在DBA_TABLE
SPACE和USER_TABLESPACE视图中。
Pctincrease为扩展块数据量增长的百分比值。比如一个initial 为20，next为20,pctinc
rease为50的一个数据段中，扩展块以下列的方式增长：
扩展数 块数 总块数 扩展容量
1 20 20 Initial
2 20 40 Next
3 30 70 Next*1.5
4 45 115 Next*1.5*1.5
表空间在多次分配与回收其空间之后，会出现一个自由空间碎片，后台进程SMON会周期
性地合并表空间内相邻的自由范围，然而，如果表空间缺省的pctincrease 为0，则不会自
动实现空间合并。所以一般我们至少设置pctincrease为1。当然，我们也可以使用SQL命令
强制合并表空间中相邻的自由空间：
alter tablespace data coalesce;
增加空间至已有表空间
我们知道，一个表空间由一个或多个数据文件组成，当一个表空间将被用尽时，除非其
所用的数据文件有自动扩展，否则我们必须为其增加新的数据文件。数据库创建时指定的
那几个数据文件都没有自动扩展选项。
比如我们可以用以下命令给USERS表空间增加空间
ALTER TABLESPACE "USERS"
ADD DATAFILE '/oracle/ctl2/oradata/ora/users_data.dbf'
SIZE 1000K
AUTOEXTEND ON
NEXT 100K
MAXSIZE 10M;
新建表空间
数据库建立时只创建了SYSTEM、RBS、TEMP、TOOLS、USERS这几个表空间，随着应用的增长
，你可能想把自己的应用建立在一个新的表空间上，或是想把不同的应用建立在不同的表
空间之上，这时，我们就必须建立新的表空间。
例如，我们用以下命令创建了一个myapp的表空间：
CREATE TABLESPACE myapp
DATAFILE '/oracle/ctl3/oradata/ora/myapp_data.dbf'
SIZE 5M
AUTOEXTEND ON
NEXT 1M
MAXSIZE 10M
DEFAULT STORAGE
( INITIAL 100K
NEXT 100K
MAXEXTENTS 10M
PCTINCREASE 1 )
MINIMUM EXTENT 100K;
建立表空间时我们同时建立了一个数据文件，因为一个表空间至少要有一个数据文件。
移动数据文件
创建时指定的数据文件存放位置，基于某些原因（比如你在做目录规划调整、或是基于I/
O管理考虑想移动它），你可能想移动数据文件到另的目录，
有两种移动数据库的方法：通过alter database或通过alter tablespace命令。后者仅用
于不包括SYSTEM的表空间、回滚段或临时段的数据文件。前者可用于所有数据文件。
alter database方法：
使用alter database的方法，数据文件在实例关闭后进行移动。下面是具体步骤：
1).关闭数据库
SVRMGR>connect internal
SVRMGR>shutdown
SVRMGR>exit
2).使用操作系统命令移动数据文件到新的位置
mv …
3).装载数据库并用alter database来改变数据中的文件名。
Svrmgrl
SVRMGR>connect internal
SVRMGR>startup mount ora;
SVRMGR>alter database rename file
‘/oracle/ctl1/????.dbf’ to
‘/oracle/ctl3/????.dbf;
执行alter database时，Oracle会检查要改变的文件名是否存在。如果引步骤失败，
则需要检查要改变的文件名是否正确。
4). SVRMGR>alter database open;
Alter tablespace方法
使用此方法，数据文件可在实例运行当中进行移动。
1).置表空间状态为脱机(offline)
SVRMGR>alter tablespace myapp offline
2).使用操作系统命令移动文件
mv /oracle/ctl3/oradata/ora/myapp_data.dbf /oracle/ctl2/oradata/ora/my_ap
p_data.dbf
3).使用alter tablespace命令来更改数据库中的文件名
SVRMGR>alter tablespace myapp rename datafile
‘/oracle/ctl3/oradata/ora/myapp_data.dbf’ to
‘/oracle/ctl2/oradata/ora/myapp_data.dbf’
4).重新置表空间状态为联机(online)
SVRMGR>alter tablespace myapp online
四．管理重演日志组
Oracle向重演日志文件记录事务信息，可将重演日志组想象为活期存款，它就像一个检
查帐号记录对数据库所做的修改。一个重演日志组由一个或多个同样大小的重演日志文件
组成，一个重演日志组中至少两个成员，如果Oracle向其中一个重演成员写记录受阻，则
继续写同组中的另一个成员。
同时，应有多个重演日志组，供Oracle循环使用，Oracle要求至少有两个重演日志组。
增加新的重演日志组
在增加之前，我们先用select * from v$logfile查看一下原有的重演日志组。
SQLWKS> select * from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ---------------------------------------- 1
/oracle/ctl1/oradata/ora/redoora01.log
2 /oracle/ctl2/oradata/ora/redoora02.log
3 /oracle/ctl3/oradata/ora/redoora03.log
然后我们用以下命令新增一组4.
alter database add logfile group 4
('/oracle/ctl1/oradata/ora/redoora04_1.log',
'/oracle/ctl2/oradata/ora/redoora04_2.log') size 500k;
之后可以再查v$logfile验证一下。
删除已有的重演日志组
用户可以用
alter database drop logfile group 4命令删除一个重演日志组。
删除时可能会出现两种情况：
1）.可能出现试图删除一个重演日志组后使Oracle中重演日志组数小于两下组。如果出现
这种情况，必须在删除组2之前，先增加第三个组。
2）. 可能出现试图删除一个已被激活的重演日志组，将被报告ORA-01623错误。这时，必
须等待至该组不再为激活状态时才能删除。
五．控制文件职责
当启动数据库时，Oracle读取初始化参数和文件控制文件。文件中的信息是数据库运行必
不可少的。可将控制文件看作汽车的传动装置，当二者正常运转时，都不需要关注。而一
旦失常，将是很头疼的事。
每个Oracle数据库有一个或多个控制文件。控制文件保存有关数据库创建时间、数据库名
、以及数据库运行时使用的所有文件的位置和名称。用户指示Oracle根据需要以数据库控
制文件的多个（通常为两个或更多）拷贝维护控制文件。Oracle运行时，写控制文件，每
当执行维持操作时，例如，增加数据文件或配置重演日志文件组，控制文件将自动地被修改，以反映变化的情况?br /> Ｓ行┛刂莆募奈ぴ谑菘鈊own（关闭）状态下完成，有的控制文件维护则是在数据库
非安装（nomount）状态下完成。当启动Oracle数据库时，首先打开控制文件，然后检查参
加操作的全部文件在存在以及状态，并确认可访问所有重演日志文件。作为一名DBA，有责任保证在需要时Orac
le可打开控制文件。在备份过程中应包括对控制文件的拷贝操作。
删除、增加控制文件
关闭数据库。Shutdown
修改init.ora文件中的control_files, 删除或增加你想操作的指定文件，
然后重新启动数据库实例。
例如： uibm上的Initora.ora 中 ifile = …. / configora.ora 中有
control_files = (/oracle/ctl1/oradata/ora/control01.ctl,
/oracle/ctl2/oradata/ora/control02.ctl,
/oracle/ctl3/oradata/ora/control03.ctl)
移动功更名控制文件，也用同样的操作步骤。
六．初始化参文件
初始化参数可能是Oracle数据库最神秘的组成部分之一。启动实例时Oracle读取设置
初始化参数文件，也称“init.ora”或pfile。在这个文件中有多个项，说明待访问的数据
以及控制Oracle启动设置的环境参数。这些参数可用于：
.定义数据库使用的文件名和位置
.控制计算机内存分配，以支持数据库配置
.指定一些可以访问数据库的会话
初始化参数文件中一些参数的设置将影响数据库的性能。
在初始化参数文件中有两类参数：显式参数和隐式参数。显式参数在init.ora文件中有一
项；隐式参数不出现在init.ora文件中并假定为默认值，除非将其值放置于init.ora文件
中。
参数格式为
pfile_keyword=keyword_value
参数文件中的项，如果超过一行，则需要用连续符“\”。
在服务器管理器中，我们可以通过下述步骤得到参数（显式和隐式）的完整列表：
SVRMGR>connect internal
SVRMGR>show parameters
NAME TYPE VALUE
----------------------------------- ------- ----------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE
allow_partial_sn_results boolean FALSE
always_anti_join string NESTED_LOOPS
always_semi_join string standard
aq_tm_processes integer 0
arch_io_slaves integer 0
audit_file_dest string ?/rdbms/audit
audit_trail string NONE
b_tree_bitmap_plans boolean FALSE
background_core_dump string full
列出的参数很多，须好几屏才能显示完，如果我们想查看参数的某些字集，比如我们想
查看所有包含“sort”的参数，则启动服务器管理器后，按下述步骤操作：
SVRMGR>show parameters sort
NAME TYPE VALUE
----------------------------------- ------- -------------------
nls_sort string
sort_area_retained_size integer 0
sort_area_size integer 65536
sort_direct_writes string AUTO
sort_read_fac integer 5
sort_spacemap_size integer 512
sort_write_buffer_size integer 32768
sort_write_buffers integer 2
修改参数值
随着我们的Oracle知识的增长，我们可能想修改某些初始化参数项的值。有些参数项可以
修改，有些参数项必须保留。有关修改初始化参数文件值的详细内容在“Oracle8 Refer
ence”（a58224.pdf）中有介绍。该手册给出了关于可修改的参数以及值的范围的忠告和
建议。
修改初始化参数文件中的任何项的值，将在下次启动数据库时产生作用。
在数据字典v$parameter中记录着200多个参数值，这个视图列出了用于数据库操作的值，
用命令show parameters可以显示该视图。下面我们列出几个最常修改的参数项：
参 数 含 义 最常见的修改
Shared_pool_size 分配给共享池的内存字节数 随着用户的需求，可以按时增加此值
Rollback_segments 数据库启动时获取的一个或多个回滚段名 当事务处理增加或减少时，
可以从该表中增加或删除回滚段名
Sessions 可同时访问数据库的最大会话数量 需要增加更多的用户
Processes 可同时访问数据库的最大进程数量 需要增加更多的用户
七．数据字典
数据字典存储在系统（system）表间内，其信息使得Oracle可管理其资源以及跟踪诸如谁
登录到数据库、数据库运行时所需文件等信息。作为DBA，应该熟悉数据字典，大量的DBA
的工作都使用其中的信息。
关于数据字典的类型前面我们已有提及，这里我们要深入再介绍一下：
数据字典视图前缀有all、user、dba、v$分别有以下含义：
数据字典前缀 含 义
All 向用户返回所有对象可访问的信息
User 返回用户拥有的对象的信息
Dba 返回用户数据库列表，类似于用户类型
V$ 由Oracle运行更改的动态特性视图，通过查询这些视图，可得到Oracle特性信息、文件
状态和内存状态等情况
现在我们使用SQL查询数据字典来帮助理解：
SQL>select * from dict where table_name like ‘%_OBJECTS’;
TABLE_NAME COMMENTS
--------------------------------- -----------------------------------------
------------------------
ALL_OBJECTS Objects accessible to the user
DBA_ANALYZE_OBJECTS
DBA_EXP_OBJECTS Objects that have been incrementally exported
DBA_OBJECTS All objects in the database
USER_OBJECTS Objects owned by the user
SQL>select * from dict where table_name like ‘%_QUOTAS’;
TABLE_NAME COMMENTS
--------------------------------- -----------------------------------------
------------------------
DBA_TS_QUOTAS Tablespace quotas for all users
USER_TS_QUOTAS Tablespace quotas for the user
SQL>select * from dict where table_name like ‘%_SYNONYMS’;
TABLE_NAME COMMENTS
--------------------------------- -----------------------------------------
------------------------
ALL_SYNONYMS All synonyms accessible to the user
DBA_SYNONYMS All synonyms in the database
USER_SYNONYMS The user's private synonyms
通过查询dict表，我们可以得到和数据字典表的名称及其注释。
除非在适当的位置访问，否则没有一定特权的用户不能查看属于SYS的dba和v$动态性能
视图，用户必须首先被授予对这些视图的select权限，方可查看这些视图。视图所包含的
信息对初学者及熟练的DBA而言都是非常珍贵的，下面列出的是最有用的dba视图，作为DB
A，必须熟悉这些内容：
表名 注释
DBA_DATA_FILES 关于数据文件的信息
DBA_DB_LINKS 数据库中的所有数据库链接
DBA_EXTENTS 数据库中包括所有段的盘区
DBA_FREE_SPACE 所有表空间中自由盘区
DBA_INDEXES 数据库中所有索引的描述
DBA_IND_COLUMNS 包括在所有TABLEs及CLUSTERs中的COLUMNs
DBA_OBJECTS 数据库中所有对象
DBA_ROLLBACK_SEGS 回滚段描述
DBA_SEGMENTS 为所有数据库段分配的存贮空间
DBA_SYNONYMS 数据库中所有同义词
DBA_TABLES
DBA_TABLESPACES
DBA_TAB_COLUMNS 所有表描述、视图等的列
DBA_TAB_GRANTS 数据库中的对象所授的权限
DBA_TAB_PRIVS 数据库中的对象所授的权限
DBA_TS_QUOTAS 所有表空间限额
DBA_USERS 关于数据库所有用户的信息
DBA_VIEWS 数据库中所有视图的文本
接着我们总结一下一些最有用的v$视图，DBA必须熟悉这些内容。
视 图 内 容
V$datafile 数据库使用的数据文件信息；如控制文件信息
V$librarycache 共享池中SQL语句的管理信息
V$lock 由访问数据库会话放置在对象中的锁的有关信息。当某个用户修改数据库中的数
据时，为了防止另一个用户也修改同样的数据，就要加锁。
V$log 从控制文件中提取有关重演日志组的信息
V$logfile 有关实例重演日志组文件名及其位置的信息
V$parameter 初始化参数文件中所有项的值
V$process 当前进程的信息
V$rollname 回滚段信息
V$rollstat 联机回滚段统计信息
V$rowcache 内存中数据字典激活/性能信息
V$session 有关激活会话的信息
V$sesstat 在v$session中报告的当前会话的统计信息
V$sqlarea 共享池中当前光标的使用情况统计信息。光标（cursor）是一块内存区域，由
Oracle处理SQL语句时打开。
V$statname 在v$sesstat中报告的每份统计的意义
V$sysstat 基于当前操作会话进行的系统统计
V$waitstat 当遇到多个会话访问数据库的数据时详细情况。超过一个以上的会话同时访问
相同信息时，可能有等待的情况。
若本课时有剩余时间，将剩下的时间用于上机，查上列出的各数据字典表的结构及内
容信息。
第七章：性能优化基础
由于某些原因，比如说糟糕的设计、应用增长、应用要求的改变（可能包括重新确定可接
受的性能），数据库在用户定义下的标准下执行效率不能被用户接受。
当系统中的外部组件，甚至整个客户/服务器结构的性能下降时，仅仅进行数据库的优化是
不够的。对于后端数据库来说，主要的外部组件是：后端操作系统，网络和客户操作系统
。这三方任何一方性能不好都有影响应用的执行效率。在网络达到带宽的30%时，10M以太
网就被认为是饱和了。当系统中的某个组件饱和时，就会构成系统瓶颈，例如一个运行的UNIX机器中，当CPU负载
大于等于85%时，CPU就构成系统瓶颈。另外，操作系统可能也会需要重新优化。例如，DS
S（决策支持系统）要求在盘上存储非常大的文件，UNIX系统管理可以优化某些参数，从而
使大文件连续存放。数据库优化与操作系统的优化是紧密结合的，因此，系统管理员（SA）和DBA一起工作是个
好办法。
优化原则
现在，我们来介绍一下一些基本的性能优化原则，在差不多任何一本性能优化参考书中都
有可以找到它们。
主要性能优化的原则是什么呢？它们是：
分而治之。
分而治之原则。本质上讲，你想使用并行技术以弥补瓶颈。
策略：如果性能监视显示数据库系统集中对某块盘进行读写，这块盘就是瓶颈。Oracle的
逻辑结构和物理结构需要分开，以进行并行访问。在这种情况下，常见的情形是正好有多
个用户或进程访问位于该盘的表。
若干必须连接在一起的表格有可能都位于同一盘上，这都会带来性能下降。
预分配，预支取和预编译
就是说要尽可能超前工作。
策略：当你在非增长期内访问数据库时，性能是合理的。然而，在它的增长期，性能就可
能变差。数据库的动态增长使在线系统反应缓慢，当一个表格经常扩展，尤其是有大量的
数据，并导致在线访问时，它是一个主要的问题。对于经常要求扩展或要求极大数据范围
（extent）的数据库系统，预分配最好。我们可以通过Oracle中的存储子句设置INITIAL、NEXT和MINEXTENTS的值
，使分配的容量等于期望的表格最大峰值。
筛余。
筛余原则是解决最重要的问题以获得最大投资回报。
首先必须优化应用，然后优化数据库。不管你的后端优化得如何的好，若不计网络延时和
其它因素，前端（或应用代码）主要占有客户/服务器系统的全部使用时间。因此，多花点
时间优化应用代码是十分值得的。
大量，块和批处理。
在合适的情况下，尽量成组处理，避免资源竞争。
策略：比如说我们做了个DSS，这个DSS通过Internet向外部分析员提供实时、只读信息，
网页是系统的前端。并发用户有时会比较多（大约100个），但一般不会造成问题。我们需
要的是对较大的表格进行选择时，数据的吞吐率。
在从一个表格中选择大多数或全部行时，优化器必须选择全表格扫描取代任何索引作为它
的访问路径。若用户想要这么做，且这种类型的访问是应用要求的一部分，则这本身不能
避免。做什么可以加速这件事呢？在你的UNIX系统上，你最初将你的DB_BLOCK_SIZE设置为
4KB。然后，可以增加到16K或32K。块越大，你的读越有效。为什么这样说呢？因为块大时，每读一个块会获得更
多的行数据。因此，对于大规模的数据检索，你可以增大块的大小使读更为有效。
必要时也可以考虑加大DB_FILE_MULTIBLOCK_READ_COUNT和DB_BLOCK_BUFFERS的值。
5.对应用适当地分段。
合理安排应用的位置，诸如客户机、中间连接器或服务器等。
策略：你可能会拥有客户/服务器结构的数据库进行排序和控制。你会发现，应用的很大部
分是在前端完成的，一般来讲，前端的操作过程如下：
1).登录进入数据块
2).从表中读数据
3).从终端输入数据
4).等待数据库进行操作将数据输入表中
5).退出或重复2)到4).
将一应用分段的标准方法是将它分成三个主要步骤：有事务参与的读（输入数据），没事
事务参与的局部改变（交互式地）以及有事务参与的写回变化（输出数据）。应用分段也
称为事务分割。
优化目标：
确定性能优化的目标的方法多种多种，DBA必须综合各种目录，考虑你的应用类型。衡量
优化的基准主要有：
* 吞吐率：每个时间单位所进行的工作，以每秒事务数为单位进行检测。 （IPS），这个
值越高越好。
* 响应时间：应用响应时间，越小越好。
* 运行时间：程序运行时间，越小越好。
在任何系统中，吞吐率和响应时间一般是两个矛盾的优化目标。若响应时间长（差），则
吞吐率可能很高（好），若吞吐率实低（坏），则响应时间可能很短（好）。
一般来说，OLTP（在线事务处理系统）的目标是低响应时间和高吞吐率，DSS要求低响时间
。在批处理系统中，短时间运行是主要要求。
DBA应该始终记住两个中心优化目标：一、最大投资回报（ROI）：集中精力解决最影响投
资回报的问题；二、最小争论：尽可以消灭瓶颈，减少延迟和等待。
最后，应该考虑一下一般的优化目标：
减少需要访问块的数量。必要时检查和重写代码。
尽量使用高速缓冲存储器，缓冲器和排队以弥补磁盘预支取。
使用快速硬盘，降代数据传输时间。
尽量使用程序避免竞争。调度程序，尽可能无竞争地运行；它们可能并发运行，但大多数
时间仍然没有竞争。
二．应用优化
应用优化意味着优化应用中的SQL语句，即使应用的前端具有图形界面，也是如此。因为在
图形界面中的操作对应着不同复杂程序的若干SQL语句（SELECT、INSERT、UPDATE、DELET
E）。ROI策略告诉我们，我们应该首先优化应用(80%)，然后才是数据库的其它方面(20%)
。
对于DBA来说，系统的性能是与环境密切相关的，相对的，或与特定应用相关的。因此，我
们再强调一下：优化的第一个对象是应用。
1.优化器
优化器是RDBMS的一部分，用于优化给定的SQL语句，以便其提高访问数据的效率。为完成
这一作业，优化器选择一系列可以合Oracle以最快方式得到数据库的访问路径，并基于这
些路径建立运行计划。
所谓访问路径是指到达数据的物理通路，是Oracle确定获取数据的方法。
执行计划则是根据这种路径确定的Oracle执行步骤。
优化器大致分为两种类型：
基于规则(rule-based)——基于规则的优化器按照RDBMS厂商所指定的排列顺序，选择访问
数据的最优路径。由于排序是由厂商指定的，因此不同厂商的优化器性能就很容易比较了
。
基于代价(cost-based)——基于代价的优化器在通常保存于RDBMS字典中的数据分布统计的
基础上进行路径的选择。DBA必须经常维护这种统计信息。
Oracle8的优化器同时提供基于规则的优化和基于代价的优化能力。这些优化器向程序员提
供了暗示工具，在没有语法错误的情况下，优化器会按照暗示去工作，使用最少的资源和
花费最少的时间。
基于规则的方法
Oracle对访问路径按权重1至15划分等级，并选择最低等级的访问路径。访问路径的顺序由
索引和所编写的SQL语句确定，被选择的满足查询的访问路径等级越低，处理效率越高越快
。
顺序 访问路径
1 通过ROWID的单行访问
2 通过簇连接的单行访问
3 通过散列簇或主关键字的单行访问
4 通过主关键字的单行访问
5 簇链接
6 散列簇键
7 索引簇键
8 复合键
9 单列索引
10 在索引列上的有边界搜索
11 在索引列上的无边界搜索
12 排序一合并连接
13 索引列的最大到最小
14 通过索引列排序
15 全表扫描
下面分述几种典型的访问路径等级：
1）基于规则的访问路径等级1
当“where”关键字中行标识符与唯一的值相等，该路径有效。例如：
select *
from fin_mast
where rowid=’008A4.0002.009D’
语句使用权重划分等级。在应用中编写SQL语句时，根本不知道行标识符，所以，这个路径
不常用。
2）基于规则的访问路径等级4
在相等条件下，通过SQL语句引用所有唯一或主键字中的表列时，这个路径有效。
假如有一张表street_master，其列为street_name、house_number、city，其中在street
_name和house_number上建立了主键索引。
下列SQL语句：
select *
from street_master
where street_name=’ROBSON’
and house_number=’2802’；
利用这个访问路径，在“where/and”中提到的唯一或主键字中的所有表列用等式进行
比较。
Select *
From street_master
Where stree_name=’ROBSON’
And house_number>=’2802’;
不能利用这个访问路径，由于“house_number”表列执行的比较条件不是等式（有一个
大于或等于比较条件）。同样SQL语句
select *
from street_master
where street_name=’ROBSON’
不能使用在“where/and”条件中所有关键字列表，并且不能以这个权重划分等级。
3) 基于规则的访问路径等级8
如果所有表列以组合索引和采用等式方式用于SQL语句的“where”子句，则可以使用这个
存取路径。记住：组合索引是建立在表中多个表列的基础上的。
4) 基于规则的访问路径等级9
如果“where/and”部分的SQL语句使用一个或多个单一表列索引，则可以使用这个存取路
径。如果使用多个单一表列的索引，条件必须用“and”相连。假如“fin_mast”表用“f
in_id”表列进行了索引，下列SQL语句就可以使用这个访问路径。
Select max_out
From fin_mast
Where fin_id=’1234M’;
如果“fin_mast”表中还有一个带索引的 “fin_rel”表列，但由于连接条件使用的是“
or”而非“and”，下列语句将不能使用这个路径：
select maxt_out
from fin_mast
where fin_id=’1234M’
or fin_rel is not null;
5) 基于规则的访问路径等级15
任何能满足其他的权重访问路径条件的SQL语句都能使用全表查询。全表查询顺序地读
取表中每一个记录，选择满足选择的条件的所有记录，放弃不满足条件的记录。
基于代价的优化方法
当使用基于代价的方法时，Oracle优化SQL语句使其代价降低至最少。基于代价的优化对影
响SQL语句的执行因素进行加权，以得到代价最小的运行方案。在这里，代价是计算机资源
的占有量（如I/O和CPU消耗）和完成运行时间等因素的度量。
要使CBO发挥效能，需要些信息，例如每个表中数据行数目、表的主键字列表中关键字的
分布，以及分配的和被表的数据行占用的数据块数目等。得到这些信息的窍门是经常去收
集它们，确保收集统计时不出现错误。我们来看三个数据字典视图，这些视图的内容由统
计收集关键字anaylze提供。下面是CBO的重要的表列，由于它们是数据字典的一部分，因此不能用手工修改。
USER_TABLES
NUM_ROWS NUMBER
BLOCKS NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
DEGREE VARCHAR2(21) -- of parallelism
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
USER_INDEXES
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
PARTITIONED VARCHAR2(3)
USER_TAB_COLUMNS
NUM_DISTINCT NUMBER
LOW_VALUE RAW(32)
HIGH_VALUE RAW(32)
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
1).在SQL*Plus中收集统计
使用analyze命令。
建议在表上估算统计（estimating statistics）并为索引计算统计(computing statisti
cs)。下面我们举几个例子来检验一下analyze命令。
建议按以下两点在表上估算统计：
用数据行样本百分率为所有表估算统计
在表中使用20%的数据行作为样本，Oracle随机选择包含在样本中的数据行。
这样，对于一些表来说analyze语句类似于：
analyze table lumberjack estimate statistics sample 20 percent;
analyze table mountie estimate statistics sample 20 percent;
analyze table suspenders estimate statistics sample 20 percent;
我们建议为所有的索引计算统计，下列清单显示了几条索引收集SQL语句：
analyze index lumberjack_pk compute statistics;
analyze index mountie_pk compute statistics;
analyze index suspenders_pk compute statistics;
2)使用PL/SQL过程收集统计
SYS有两个程序包用来分析对象:dbms_utility.analyze_schema和dbms_ddl.analyze_obje
ct。
Analyze_schema过程的输入参数如下：
参 数 缺 省 值
分析对象拥有者 None ----必须提供
收集方法 None ----必须是estimeate或compute
样本行 选项---- 缺省为null
样本行的百分比 选项 ----缺省为null
用20%的样本为SCOTT估算统计，该过程使用以下语法从SQL*Plus中调中：
execute dbms_utility.analyze_schema(‘SCOTT’,’ESTIMATE’,null,20)
有些DBA发现dbms_ddl.analyze_object相当灵活，它提供了比analyze_schema更详细的收
集机制。其参数如下：
参 数 缺 省 值
对象类型 None ---必须是表或索引
分析对象的拥有者 None
收集的方法 None—必须是estimate或compute
样本行 选项 -- 缺省为null
样本行的百分比 选项 – 缺省为null
excute dbms_ddl.analyze_object(‘INDEX’,’SCOTT’,’COMPUTE’,null,null)
为了更好地使用CBO优化，DBA应该周期性，经常性地进行ANALYZE命令。DBA必须保证基于
代价优化器的畅通。
3．指定优化器的方式
下一个明显问题是“如何在Oracle中指定优化器方式？”至此，我们已经学习了基于规则
和基于代价的优化。你可以在实例，会话或语句一级指定你所想要的优化方式。
通过实例（即给Oracle处理的语句的所有语句）- - init.ora的项OPTIMIZER——MODE设置
缺省的方法。其值可以为以下几种：
CHOOSE —— 在统计数据可以使用的情况下，使用基于代价的方式，否则使用基于规则的
优化。
RULE —— 使用基于规则的优化。
FIRST_ROWS ——优化器在统计数据可用的情况下，选择基于代价的优化，使响应时间最短
（即第一行出现在屏幕上的时间最短）。在高度短的使用环境中，诸如OLTP或小的DSS，应
使用这个选项。
ALL_ROWS —— 优化器在统计数据可用的情况下，尽量减少吞吐率，即减少单位时间内通
过系统的总行数。在批处理和大规模DSS中，应选用这个选项。
通过会话 —— 在使用SQL*Plus时，将alter session 命令传给Oracle，则优化程序按指
定方式进行，直到会话终止或遇到另一个alter session optimizer goal语句。
设置语句如下：
alter session set optimizer_goal=CHOOSE;
或 alter session set optimizer_goal=ALL_ROWS;
可设置的方式同1)。
通过语句——开发者使用以下两种约定之一将暗示（hints）嵌入到格式化的SQL语句中：
用以“/*”开头和“*/”结尾的注释块说明。
select /* choose */ name,address from … …
用注释引导线“-”：
select --+choose
name,address
from … …
这两种约定都与使用斜杠“//”等价，在双斜杠后面的同一行的所有文本都被看作是注释
。暗示是简单的注释，因些如果编写有错，Oracle将不给出提示可语法错误。
4．优化SQL语句
我们先来看一下SQL语句的简单步骤：
最理想的是，语句只使用步骤1、2、3和8进行处理。不经过2、3步测被传给Oracle的语句
要使用1、2、3、4、5、6、7、8步进行处理。前者要比后者更有效。
所有的SQL分三个阶段进行处理：句法分析、执行、读取数据。无论工具如何（SQL * Plu
s 、Oracle Forms、SQL worksheet）均将语句传递到Oracle处理。
句法分析在三个阶段中是最费时间且代价最高。
我们可以看出，前者使用了共享池中已准备好的句法分析语句，回避了句法分析阶段。
百分之七十以上的应用协调可完全回避句法分析阶段，通过使用共享池中已经准备好的句
法分析语句，能提高四分之一的效率。所以我们要尽可能地使用共享池中的语句。为此，
我们可以采取以下措施：
相同的功能代码书写完全相同。包含大小写格式等字符完全相同。例如：在编写不同的应
用时若有用到相同的代码段，我们可以通过使用块拷贝的方法保证其完全相同。另外必须
使用相同的书写格式或风格，
在数据库中存贮代码。把常用的代码模块编写成存储过程、函数、触发器和程序包的形式
存储在数据库中。
处理SQL语句的黄金准则是：一旦处理，则执行多次。“Parse once, execute many time
s”。
4．协调交换的工具
我们已经讨论了SQL语句的处理、运行方案、访问路径、优化和索引，现在将讨论如何察看
共享池内容。一旦掌握如何检验共享池中的SQL语句的方法，即可开始编写与共享池中的语
句完全一致的SQL语句。
查看SQL*Plus中共享池的内容
数据字典视图v$sqlarea中保存有共享池中内容信息。用户会对其SQL_TEXT表感兴趣。下列
程序说明共享池的内容：
column sql_text format a80
select sql_text from v$sqlarea where lover(sql_text) like lower(‘&text’||’%
’);
当这段程序运行时，将看到类似下面的内容：
SQL> select sql_text from v$sqltext where lower(sql_text) like lower('&text'|
|'%');
输入text的值： update
原值1：select sql_text from v$sqltext where lower(sql_text) like lower('&text'
||'%')
新值1：select sql_text from v$sqltext where lower(sql_text) like lower('update
'||'%')
SQL_TEXT
------------------------------------------------------------------------------
--
update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,
update ind$ set ts#=:2,file#=:3,block#=:4,intcols=:5,type#=:6,fl
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null
update user$ set name=:2,password=:3,datats#=:4,tempts#=:5,type#
update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,sta
update fet$ set length=:4 where file#=:1 and block#=:2 and ts# =
update sys.aq$_queue_statistics set owner_inst = 0, incarn_num
v$sqlarea视图由Oracle用户SYS拥有，必须授权才能访问v$视图。
explain plan
在选择运行select，insert，update和delete语句期间，该实用程序检查所使用的索引，
其结果以描述语句中每个表的访问方式和Oracle处理期间使用的索引的情况的操作清单的
形式输出。因为常常出现所创建表的索引不被使用的情况，所以explain plan检查索引是
否被使用。
在运行explain plan之前，用户必须拥有或能够访问的“plan_table”表，%RDBMS80%/ad
min目录中的UTLXPLAN.SQL程序创建这个表。
在SYSTEM模式下，我们执行
delete plant_table --先清空plan_table原有的记录
explain plan
set statement_id=’TEST’ --statement_id 用于唯一地标识此代码
for select * from user_tables;
这时，对select * from user_tables这条语句执行情况已记录到plan_table中，我们可以
查询plan_table：
select operation,options,object_name,id,parent_id,position
from plan_table
where statement_id='TEST'
order by id;
OPERATION OPTIONS OBJECT_NAME ID
PARENT_ID POSITION
------------------------ ---------------------- ----------------------- ------
---- ---------- ----------
SELECT STATEMENT
0
NESTED LOOPS
1 0 1
NESTED LOOPS OUTER
2 1 1
NESTED LOOPS OUTER
3 2 1
NESTED LOOPS
4 3 1
TABLE ACCESS BY INDEX ROWID OBJ$
5 4 1
INDEX RANGE SCAN I_OBJ2
6 5 1
TABLE ACCESS CLUSTER TAB$
7 4 2
INDEX UNIQUE SCAN I_OBJ#
8 7 1
TABLE ACCESS BY INDEX ROWID OBJ$
9 3 2
INDEX UNIQUE SCAN I_OBJ1
10 9 1
TABLE ACCESS CLUSTER SEG$
11 2 2
INDEX UNIQUE SCAN I_FILE#_BLOCK#
12 11 1
TABLE ACCESS CLUSTER TS$
13 1 2
INDEX UNIQUE SCAN I_TS#
14 13 1
已选择15行。
我们可以看出，对select * from user_tables这一简单语句，Oracle的内部操作仍然比较
复杂。在plan_table表中：
operation 记录了本操作步骤的内部操作。可能为：
SELECT STATEMENT
DELETE STATEMENT
UPDATE STATEMENT等
Options 操作步骤所采用的选项
Object_name 操作对象
ID 每步骤的标识号
Parent_ID 本步骤之上一步骤的标识号
Position 同上父ID的顺序号
在Plan_table表中，还有optimizer、cost等表列，我们没有列出，这些信息对我们分
析SQL的语句的执行十分有价值。
以上我们直接从PLAN_TABLE表中SELECT出其记录输出，这种输出不够直观，现在我们用
另一种格式输出查询PLAN_TABLE表。
select lpad(' ',2*(level-1))||operation||' '||options
||' '||object_name
||' '||decode(id,0,'Cost='||position) "Query plan"
from plan_table
start with id=0 and statement_id='TEST'
connect by prior id = parent_id and statement_id='TEST';
Query plan
--------------------------------------------------------
SELECT STATEMENT Cost=
NESTED LOOPS
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID OBJ$
INDEX RANGE SCAN I_OBJ2
TABLE ACCESS CLUSTER TAB$
INDEX UNIQUE SCAN I_OBJ#
TABLE ACCESS BY INDEX ROWID OBJ$
INDEX UNIQUE SCAN I_OBJ1
TABLE ACCESS CLUSTER SEG$
INDEX UNIQUE SCAN I_FILE#_BLOCK#
TABLE ACCESS CLUSTER TS$
INDEX UNIQUE SCAN I_TS#
用这种嵌套输出方式要比直接查询直观。
set autotrace的使用
set autotrace提供了在SQL语句中使用explain plan的更自动化的方法。利用autotra
ce功能，说明语句的处理过程已经流水作业化了，下面我们来看一下自动跟踪SQL的set命
令的格式：
SQL> set autotrace
使用：SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
为了使set autotrace令获得成功，用户必须确保访问到了plan_table表。当set auto
trace 命令执行成功时，这时执行每一条语句后，Oracle将产生类似天以下会话的跟踪输
出结果：
SQL> select * from test;
COL
----------------------------------------
汉字测试
English test
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=24)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=1 Card=2 Bytes=24)
统计
----------------------------------------------------------
28 recursive calls
3 db block gets
4 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
457 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
大家可以看到，autotrace给出的统计信息更加丰富，但同时我们也可以感觉出来aut
otrace on之后，语句执行的时间也慢了些，那时因为Oracle还要花时间跟踪其执行。
SQL_TRACE与tkprof
SQL跟踪功能和tkprof格式化输出程序共同提供了创建和观察单一SQL语句性能统计的功能
，用户可以在应用环境和作业级启动SQL跟踪功能。但是，不管在任何情况下，都必须在i
nit.ora中设置以下参数：
TIMED_STATISTICS=TRUE
USER_DUMP_DEST=<directory>
MAX_DUMP_FILE_SIZE=<size in operating system blocks>
这样，所有的作业将产生跟踪文件。跟踪文件以.trc为后缀后，存放在USER_DUMP_DEST目
录下。
在会话级启动SQL跟踪可以使用如下DDL语句：
SQL>ALTER SESSION SET SQL_TRACE=TRUE;
这时，SQL跟踪功能 只在这个作业的生命期内创建统计数据。为了阅读SQL跟踪所创建的信
息，必须在操作系统级（命令行）运行tkprof，一般语法如下：
tkprof <trace_file> <output_file>
tkprof把跟踪文件转成可读的信息，存在的输出文件名默认后缀为.prf。
tkprof输出格式大致如下：
select * from test
call count cpu elapsed disk query current ro
ws
------- ------ -------- ---------- ---------- ---------- ---------- --------
--
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 2 3
2
------- ------ -------- ---------- ---------- ---------- ---------- --------
--
total 4 0.00 0.00 0 2 3
2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5
那么，所有的这些信息意味着什么呢？它如何帮助我们优化应用？虽然利用大多数诊断工
具可以提供信息，但却不能提供解释，至少不能提供自动解释。下面我们来看一下这些统
计信息的意义：
count = parse,execute或fetch调用的执行次数
cpu = 实际使用CPU时间，以秒为单位（如果为0.00，说明这个语句在库缓冲中）
elapsed = 实际操作时间，单位：秒。（应大于或等于CPU时间）
disk = 从数据文件读的Oracle块，也被称做物理读操作
query = 为保持数据一致性，Oracle在撤消缓冲区中进行的缓冲读操作，其结果保存于
缓冲区中，也被称做一致性访问。
current = 当前方式下，Oracle在数据库缓冲区中进行的缓冲读，也被称为数据库块访问
。
rows = 由主驱动语句所访问的行的数量。
逻辑读操作的数量=query+current
作为DBA，作为一名应用程序开发者、程序员或DBA，应努力实现以下目标：
尽量减少逻辑读操作 只访问只需访问的东西，不要多访问任何其它东西，尽量使用捷径。
尽可能将物理读操作和逻辑读操作的比例下降为0 首先，确保尽量减少逻辑读操作目标的
实现；第二，确保你以最有效的方式访问你所需的要访问的东西；第三，增加SGA中的数据
库缓冲区，在必要情况下加大内存。对于大多灵敏语句而言，这个比较的平均值应小于0.
10，也就是说在数据库缓冲中要有>90%的命中率。
尽可能将逻辑读操作和行数的比例降为0 在每次逻辑读操作中，应读取尽可能多的行。这
个比例越小，读操作的效率越高。如果这个比率值太大，就要调整DB_BLOCK_SIZE。
由于我们所做的试验记录数较少，统计出的信息作为教学示例，并不一定具有实际意
义。
三．优化内存
在Oracle中，优化内存经常味意着优化SGA。它包括监视和优化共享区（数据字典和库缓存
）和数据库缓冲区。内存优化和应用优化、I/O优化紧密相关，因为优化的主要目标之一
—— 减少和消除竞争——必须涉及优化过程的各个方面。现在让我们把精力转向实际收集
和诊断内存故障。
UTLBSTAT和UTLESTAT
这一实用工具对是Oracle最常用的诊断工具。DBA在运行他（或她）的应用或仿真之前运行
utlbstat。Utlbstat.sql脚本建立收集和存储性能数据所必须的开始表格。然后，DBA运行
建立结束表格和差异表格的utlestat.sql，计算utlbstat运行和这个utlestat运行之间（
本质上讲是应用持续时间内）的性能差异，格式化输出数据（包括注释和一些解释），并将它们写至缺省文件r
eport.txt。该文件由DBA直接或简接解释（取一些给定的输出值，并用作一些简单公式的
输入）。
这两个程序脚本保存在$ORACLE_HOME/rdbms/admin子目录下。在实际应用前，应在init.o
ra中设TIMED_STATISTICS=TRUE，或在作业级设
ALTER SESSION SET TIMED STATISTICS=TRUE
完成上述步骤后，执行以下步骤：
启动svrmgrl
SVRMGR>connect internal;
SVRMGR>@ $ORACLE_HOME\rdbms\utlbstat.sql
完成上述步骤后，就会在SYS模式中创建初始的表和视图，这些对象的名字中会包括字符簇
BEGIN，最初的统计数据将保存在这里。下一步就可以执行相应的应用了。作为DBA，你的
目标是在它的运行的峰值时，获得有关系统的统计数据。在峰值过后或经过一段合理的时
间，执行utlestat.sql，这个时间不必太长，但必须足够长。
文件report.txt中包含大量的信息，可以帮助用户来优化应用，内存和I/O。其包含有关于
共享区（数据字典和库高速缓冲存储器），数据库缓冲器高速缓存，每一事件/登录数据，
每一表空间/文件/I/O,以及等待事件的统计信息。你可以使用全部或其中部分的信息来帮
助优化。
优化共享区
前面我们在讲述“数据库结构”时，我们已经了解到共享区包括两种结构：
数据库字典缓冲存储器
库高速缓冲存储器
这些存储分析的SQL语句供以后重用的区域称为共享SQL区，私有SQL区是在应用内与指示器
持续时间相关的区域。共享区是缓存结构的，与其它缓存结构一样，它是常驻内存的数据
结构。
优化共享区是内存优化和应用优化的重叠区域。
当Oracle使用过一个SQL语句之后，它在库缓存中为这个语句安排一个区域，并按字符保存
这个语句。当发生重用时，只有完全相同的语句才能重用。例如，以下语句，在Oracle缓
存看来是不同的：
SELECT * FROM EMPLOYEES;
SELECT * FROM EMPLOYESS;
SELECT * FROM employees;
虽然上面三个语句在功能上完全相同，但是由于多个空格的存在和大小写不同，因此，在
字序上，它们是不同的。
提高库高速缓冲存储器指南：
I．尽量减少应用中不必要的分析调用 分析占用大量的CPU时间，同时由于缓冲和缓存
的引入，它也占用了大量内存。运行SQL TRACE/TKPROF，并检查Parse列的值是否很接近E
xecute(或Fetch)的值，如果是这样，那么应用对所有的操作都进行重新分析。
II 最大限度地重用那些被分析的语句 这一点我们在前面已经提过。
把经常用到的程序固定在内存中 在Oracle中，一个指示器（或称光标）、触发器、过程
或程序包，可以通过使用一个特殊的共享区包，DBMS_SHARED_POOL而保留在内存中。为了
创建这个程序包，你可以运行$ORACLE_HOME/rdbms/admin/dbmspool.sql。为了固定一个程
序对象，使用如下语句：
SQL>EXECUTE DBMS_SHARED_POOL.KEEP(‘<Object_name>’);
为了释放它，使用如下语句：
SQL>EXECUTE DBMS_SHARED_POOL.UNKEEP(‘<Object_name>);
判断这个对象是否被钉住，使用如下语句：
SQL>SELECT SUBST(NAME,1,25),KEPT FROM V$DB_OBJECT_CACHE
如果这个对象被固定，则KEPT列的值是YES，否则为NO。
减少库缓存中的碎片 用户的应用可能会出现ORA-04031号错误（没有足够的连续空闲空间
）。解决这个问题的一个方法是固定内存中经常使用的大对象，而为那些不经常使用的对
象保留一定空间。这需要在init.ora中设置SHARED_POOL_RESERVED_SIZE和SHARED_POOL_R
ESERVED_MIN_ALLOC，实际确保那些必须的大对象可以找到空间。将SHARED_POOL_RESERVED_SIZE设为同时装载的
对象字节长度之和。SHARED_POOL_RESERVED_MIN_ALLOC设置为由SHARED_POOL_RESERVED_S
IZE指定的保留区最小的分配单元。为了确定一个你想在保留区中包含的特定对象的大小，
使用以下语句：
SQL>SELECT SUBSTR(NAME,1,25) “Name”,SHARABLE_MEM
2 FROM V$DB_OBJECT_CACHE
3 WHERE NAME=’<Object_name>’;
另外，为了确定SHARED_POOL_RESERVED_SIZE值大小使用如下语句：
SQL>SELECT SELECT SUBSTR(NAME,1,25) “Name”,SHARABLE_MEM
2 FROM V$DB_OBJECT_CACHE
3 WHERE SHARABLE_MEM> <SHARED_POOL_RESERVED_MIN_ALLOC>;
以上查询输出，再增加适当比例，例如10%，设置SHARED_POOL_RESERVED- SIZE。
共享区的数据字典部分保存着Oracle数据的缓存结构。参数SHARED_POOL_SIZE是确定
数据字典本身的唯一途径。对库缓存所做的一切都会影响到数据字典缓存，因为它们是共
享区的，它们不能完全独立地进行配置。
有两种办法可以测量数据字典缓存的性能，其中一种方法是对V$ROWCACHE进行如下查询：
SELECT SUM(GETMISSES)/SUM(GETS) “DC_MISS_RATIO”
FROM V$ROWCACHE;
另一个办法是使用report.txt中的数据字典段，计算所有的GET_MISS和GET_REQS，以得到
一个类似的DC_MISS_RATIO。如果这两种办法的任一种显示: DC_MISS_RATIO>.15，就增加
SHARED_POOL_SIZE（并重新进行测试）。
多线程服务器问题（MTS）
在多线程服务器中，存在着针对客户（用户）内存分配服务器内存问题。正如存在一个SG
A一样，也存在一个User Global Area(UGA)，它包括用户任务信息，排序区域和私有SQL区
。一般情况下，缺省Oracle RDBMS应用会在用户进程与服务器之间采用一种一一对应的方
式。在MTS上，UGA存在于共享区中，其它由各进程使用的内存保留在PGA中。
我们可以使用下面的语句来确定被重新定位到SGA的UGA的大小：
SELECT SUM(VALUE)
FROM V$SESSTAT SE,V$STATNAME SN
WHERE SN.NAME = ‘session uga memory max’
AND SE.STATISTIC# = SN.STATISTIC#;
这个查询自实例启动以来，使用最大数量的UGA会话内存。你应该随时对这个值进行采样，
并跟据这个值增加SHARED_POOL_SIZE的值。
在MTS中，有两个init.ora参数影响到用户内存：
SESSION_CACHED_CURSORS
CLOSE_CACHED_OPEN_CURSORS
若想要的话，将SESSION_CACHED_CURSORS设置为缓存在用户内存区域中的会话指示器的最
大期望值。
CLOSE_CACHED_CURSORS的缺省值为FALSE，它意味着在COMMIT命令出现时，指示器不关闭。
如果语句少重用，将其设为TRUE。
优化数据库缓冲区高速缓冲存储器
在提高Oracle数据库性能方法，正确设置数据库缓冲区高速缓冲存储器的大小，可能是最
重要的一个措施。数据库缓冲区高速缓冲存储器是SGA中保留内存最为经常使用的Oracle数
据块拷贝的存储区域。以下两个参数确定这个区的大小：
DB_BLOCK_SIZE
DB_BLOCK_BUFFER
DB_BLOCK_SIZE是Oracle块的大小，它们取值范围是2的N次方KB。一般来说这个值越大，对
性能越有利。如果数据库已建立，并且已采用相对较小的块，则可以靠在方便的情况下通
过以下过程重建数据库：
关闭实例
将数据库全部输出
在init.ora中增加DB_BLOCK_SIZE值
启动实例
重新输入数据库
这是比较令人头疼的操作，所以在创建数据库时就应当比较慎重地考虑好DB_BLOCK_SIZE的
值。
DB_BLOCK_BUFFERS是在内存中保留的Oracle块的数量，每个缓冲器的大小为一个块的
大小。这个值应足够高以保证有效地击中高速缓冲存储器，但不应过高，从而导致操作系
统页交换。页交换是Oracle的基本工作，你显然不想让操作系统页交换来影响Oracle的工
作。为了保证数据库缓冲区和共享区在实际内存中有一个较大的击中，SGA就应为实际内存的1/2到3/4。
数据库缓冲区高速缓冲存储器的大小为：
DB_BLOCK_BUFFERS * DB_BLOCK_SIZE
它高速缓冲数据而不是程序，因而不同于共享区。
我们来看一下用户I/O请求时缓冲区的管理过程：
用户选择数据块（请求块）
服务器在数据库缓冲区缓存中查找数据。
如果（通过散列函数）在最近最少使用（LRU）列中找到就返回。
如果没找到，它从硬盘的数据文件中读取数据，并（使用散列函数）将其粘在最近最少使
用（LRU）列表的最近经常使用（MRU）或LRU端。
如果用户不修改数据，过程结束。
用户修改数据DBWR将此块写回硬盘。
索引每次只访问一个块，全表扫描则可能一次访问多块。可以通过以下设置来决定多块访
问的数量：
DB_FILE_MULTIPLE_BLOCK_READ_COUNT=<The number of blocks to be read>
缓冲区有几种状态：
free(clean) 从应用启动以来，从未使用过，或曾经被使用过，现在又可作用的缓冲区
。
Dirty 曾经被使用过，但还没有被清除出缓冲区或在检查点上被DBWR写出。
Current 被insert、update或delete使用的缓冲区。
Read-consistent 被SELECT或撤消所使用的缓冲区。
现在为止，我们已前对数据库缓冲区缓冲存储器有较深入的了解，那么怎么来优化它呢
？由于内存I/O比磁盘I/O快几个数据级（ns对ms），所以我们希望经常（90%以上的机会）
从数据库缓冲区高速缓存中访问块，而不是从磁盘读取块。另外，我们还想使LRU锁存锁竞
争最小。
我们至少有两种办法来测量数据库缓冲区缓存击中率：
SELECT 1-（P.VALUE/(D.VALUE+C.VALUE)） “CACHE HIT RATIO”
FROM V$SYSSTAT P,V$SYSSTAT C,V$SYSSTAT D
WHERE P.NAME=’physical reads’
AND D.NAME=’db block gets’
AND C.NAME=’consistent gets’;
以上实际使用的击中率计算公式是：
1-(physical reads/logical reads)
从report.txt的statistics段中收集相应的物理读，db块读取和一致性读取，使用同一公
式计算击中率。
如果击中率小于90%, 增加DB_BLOCK_BUFFERS，并重启，再次检查击中率进行调整。
优化排序
排序消耗大量CPU时间、内存和硬盘空间。
我们的第一个策略是：尽量避免不必要的排序，当无可避免排序时，则应优化排序，使其
优化执行。在内存排序是最理想的，但是对于某些大表，在硬盘上进行排序是不可避免的
，这会消耗大量时间。
我们的第二个策略是：尽可能地在内存中进行排序，只在绝对必要时在磁盘上进行排序。
当然，这意味着分配足够的临时磁盘空间（实质上，是TEMP表空间），并将这一空间与Or
acle数据库文件、回滚段和重做日志文件从物理上分开。
以下SQL命令或操作它们可以触发排序：
CREATE INDEX，ALTER INDEX …REBUILD
ORDER BY,GROUP BY
DISTINCT
UNION, INTERSECT, MINUS
IN, NOT IN
排序参数
在init.ora中，有两个重要的参数影响排序操作：
SORT_AREA_RETAINED：用于内存排序的最大内存数。
SORT_AREA_SIZE：用于外部磁盘排序的最大内存数，涉及到临时段的分配。
如果一个排序操作对内存的需求超过SORT_AREA_RETAINED_SIZE的规定，它就会尝试在SOR
T_AREA_SIZE规定的范围内进行外部磁盘排序，并在进程中分配多个临时段。若排序操作要
求更多的内存，则它将排序任务分为多个排序运行，并为此分配多个临时段。
使用EXPLAIN PLAN，你会发现很多SQL语句在它们执行计划中可能要求多个排序，当前正在
进行的排序称为活动排序，支持连接操作的排序称为连接排序。活动排序要求SORT_AREA_
SIZE，任何连接排序要求SORT_AREA_RETAINED_SIZE。
对于多线程序服务器，设置SORT_AREA_RETAINED远小于SORT_AREA_SIZE。作为一个参考
：SORT_AREA_RETAINED_SIZE= (SORT_AREA_SIZE/期望并发的排序数)，但不小于1/10（SO
RT_AREA_SIZE）。
当排序不能全部在内存中进行时，就必须创建临时（排序）段。也就是说，排序操作的
内存要求超过SORT_AREA_RETAINED_SIZE的设置时，它要求分配临时段，并试图在SORT_AR
EA_SIZE之内工作。利用CREATE 或ALTER TABLESPACE < tablespace_name> TEMPORARY …
语法创建临时段空间。这些临表空间由一个段组成，最初由要求它的第一个排序创建。
在SGA中，称作Sort Extend Pool(SEP)的内存结构包括组成属于临时表空间的单个排序段
的范围（extent）。在一个进程请求排序空间时，这个区提供空闲范围以便重新使用。此
外，V$SORT_SEGMENT包含信息，如使用临时排序段的用户、范围、和块数。你可以使用这
个确定击中率并帮助确定范围的大小。
Oracle可以让排序旁路数据库缓冲区高速缓冲存储器。这被称作为排序直接写。当然，你
仍然需要SORT_AREA_SIZE字节，但每一排序操作会有它自己的内存缓冲区并直接将它们写
入磁盘。缓冲器由init.ora参数SORT_WRITE_BUFFERS设置（2-8），缓冲器数有SORT_WRIT
E_BUFFER_SIZE(设置32K至64K)。每一标准排序操作要求一Sort Direct Writes Buffer，其大小为
(SORT_WRITE_BUFFERS *SORT_WRITE_BUFFER_SIZE)+SORT_AREA_SIZE
init.ora参数SORT_DIRECT_WRITEES确定排序行为而不管使用数据库缓冲区高速缓冲存储器
与否。若设置为缺省值AUTO，且若SORT_AREA_SIZE>= 10* Sort Direct Writes Buffer，
则使用Sort Direct Writes Buffer。若设置为FALSE，则排序写在磁盘之前缓冲在数据库
缓冲区高速缓冲存储器中。这些是正常的排序缓冲缓冲写。若设置为TRUE，则排序写始终是排序直接写。VLDB，
DSS和Data Warehouse应该正常地将这个参数设置为TURE(或至少为缺省值AUTO)。
四．优化I/O
Oracle中优化I/O主要是优化组成数据库的段（表格和索引）的物理结果，它们包括由
范围（extent）组成，自然也是由块组成的表空间和数据文件。
Oracle数据库管理I/O意味着读和写。从数据库角度来看，特别是从DML角度来看，SELECT
一般是读操作，INSERT、UPDATE和DELETE是写操作；DDL（CREATE、ALTER、DROP）始终是
写操作。
在Oracle中考虑优化I/O时，不仅要考虑优化表空间、范围（extent）、块和数据文件，还
要考虑优化回滚段和重做日志（因为用户DML产生所有种类的I/O）。下面逐一介始每种I/
O的优化。
1．优化表空间和数据文件
通过前面的学习，我们知道：表空间是进行物理存储的Oracle结构，表空间存储一群段—
—表格和格引。表空间在操作系统级映射到一个或多个数据文件上。
优化表空间和数据文件的一条重要原则就是我们以前提到的分而治之。具体来讲就是将表
空间（及其数据文件）尽可能地分放在不同的磁盘上。将表空间分放在不同磁盘上可以消
除或者至少减少磁盘竞争。
划分表空间
划分表间就是尽可能将各表空间分放在不同的磁盘上，有可能的话：
SYSTEM位于一单独的磁盘上。
TEMP至少位于N个磁盘上。
DATA1至DATAN位于N个磁盘上。
INDEX1至INDEXN位于N个磁盘上。
ROLLBACK至少位于N个磁盘上。
记位：初始创建用户时，一个用户的缺省表空间和缺省暂存表空间指向SYSTEM，若它们没
有被正确地创建，通过执行下列语句改变这些：
ALTER USER <user_name>
DEFAULT TABLESPACE <tablespace>
DEFAULT TEMPORARY TABLESPACE <temp_tablespace>;
为了用户避免在SYSTEM表空间生成对象，任何关于系统的限额必须取消使用：
ALTER USER <user_name> QUOTA 0 ON SYSTEM;
我们按表的访问频度将表格分类——活动分类，换言之，按它们的活动程度分类：
若一个表格活动非常频繁，则称之为热门表格，对于一个表格内的列的活动列同样成立，
这也被称为热门列。
相对热门组件而言，活动一般的表格或列被称为温和表格或列。
不常被访问的组件（表格或列），称为冷门组件。
H 高活动，或热门
M 中活动，或温和
L 低活动，或冷门
虽然这是一个简单的分类，但这将有助于您的物理设计和性能优化。例如：高活动的DML表
格将导致高碎片。若存储在它们自己的单独的表空间或磁盘上，这会阻止它们导致低活动
表格的不必要碎片。
分类工作结束后，我们可以按以原则划分表格和索引。
将每一个热门表格/索引放在它自己的单独的表空间/磁盘上。
将每一个温和表格/索引放在它自己的单独的表空间/磁盘上。
将每一个冷门表格/索引放在它自己的单独的表空间/磁盘上。
始终将合并表格/索引放在它自己的单独的表空间/磁盘上。
将数据和索引表空间分开。
将您最热门的表空间放在你最快的磁盘上，将最冷门的表空间放在最慢上的磁盘上。
若有必要，由于磁盘空间限制的原故，将温和表格/索引和冷门表格/索引放在同一表空间
或磁盘上。
若非并发访问，将“类似”的表格/索引放在同一表空间/磁盘上。
另外成簇还为存储频繁的合并的表格而提供一个可选存储方案。（参阅前面P27、P28）。
监控
虽然我们已经看到了帮助减少热门表格竞争的准则和成簇，但我们如何告知哪一个表
空间是热门表空间呢？在应用早期建模时，若你有一个良好的估计，即良好的事务分析计
算，你就有一个好的开端，并应该使用这些计算指导你的初始物理设计。
我们也可以使用Oracle备用工具帮助实施监控：V$动态性能视图和utlbstat.sql/ utlb
estat.sql正确运行时的report.txt输出。
在V$DATAFILE和V$FILESTAT视图作以下查询：
SELECT NAME,PHYSRDS,PHYSWRTS
FROM V$DATAFILE DF,V$FILESTAT FS
WHERE DF.FILE#=FS.FILE#;
或在report.txt的I/O部分检查PHYS_READ和PHYS_WRITES。物理读和物理写次数之和是总的
文件或表空间I/O次数。
（结束时的I/O次数 -开始时的I/O次数）/ 时间 = I/O频度
我们应尽可能地平衡I/O——实质上，在全部磁盘之间分担I/O。
2. 优化块和范围
Oracle按范围组织，而范围组成表空间。它们是存储表空间的物理基础。因此，我们
访问其中的数据并管理它们的增长越有效，我们的性能就越好。
使用预分配
我们知道，动态分配开销太大，并且有损I/O性能。在差不多的情况下均优选静态分配
。你可以静态地预分配一个段（表格或索引）或一个表空间。
前面介绍中，我们已经知道了一些存储数的含义，现在让我们来简单地观察一下同一表
空间存储两个不相同表格的两种不同方法，假设你有两个表格，每一个表格要求最大空间
为100MB。让我们来创建具有单个数据文件的256M表空间，从而使能一些额外的增长。
第一个方法：预分配表空间。
CREATE TABLESPACE ts1
DATAFILE ‘/data1/file1.dat’ SIZE 256M
STORAGE (INITIAL 100M NEXT100M MINEXTENTS 2);
CREATE TALBE t1 (a number(8), … , z number(8))
TABLESPACE ts1;
CREATE TABLE t2 (a number(9),….,z number(9))
TABLESPACE ts1;
第二种方法：预分配表格。
CREATE TABLESPACE ts1
DATAFILE ‘/data1/file1.data’ SIZE 256M;
CREATE TALBE t1 (a number(8), … , z number(8))
TABLESPACE ts1
STORAGE (INITIAL 100M NEXT 10M MINEXTENTS 1);
CREATE TABLE t2 (a number(9),….,z number(9))
TABLESPACE ts1
STORAGE (INITIAL 100M NEXT 10M MINEXTENTS 1);
通过以上比较，我们可以看出，预分配表格明显地给我们更细粒度地控制增长以及与该
表空间关联的性能。一般来讲，预分配的存储单元越细，性能越好。
使用Oracle分离
Oracle分离本质上讲是一种范围（extent）预分配形式，以便每一范围占据它的全部对应
数据文件，它很方便地位于一个单独的磁盘上。假设你有一个活动性极高的表格要分离，
其峰值规模小于600M，并且你可以使用三个盘对其进行分离，则可以用以下方式创建：
CREATE TABLESPACE ts1
DATAFILE ‘/data1/file1.dat’ SIZE 200M,
DATAFILE ‘/data2/file2.dat’ SIZE 200M,
DATAFILE ‘/data3/file3.dat’ SIZE 200M;
CREATE TABLE t1 (a varchar2(25),…,z varchar2(25))
TABLESPACE ts1
STORAGE (INITIAL 198M NEXT 198M MINEXTENTS 3 PCINCREASE 0);
避免碎片
在一个表空间内分配和释放范围时会出现范围碎片。虽然碎片一般主要作为空间问题考虑
，但它象数据系统的全部事情一样，会影响性能。
使用下列语句帮助确定表格碎片的程度：
SELECT SEGMENT_NAME,COUNT(TABLESPACE_NAME)
FROM DBA_EXTENTS
WHERE EXTENTS>4
ORDER BY EXTENTS;
如果返回任何段，考虑这些太零碎了，为了优化这一点，你可以选择新的存储参数创建
又一个表格，重建。
使用下列语句帮助确定你的自由碎片程度：
SELECT TABLESPACE_NAME,COUNT(TABLESPACE_NAME)
FROM DBA_FREE_SPACE
ORDER BY TABLESPACE_NAME
GROUP BY TABLESPACE_NAME
HAVING COUNT(TABLESPACE_NAME)>10;
若返回任何表空间，考虑这些表空太零碎了。执行ALTER TABLESPACE <tablespace_name
> COALSCE语句以帮助调整这个表空间。然后重新运行查询。若有些表空间仍然碎片很多（
大于5*段数），则你可能可考虑重建。
为了使SMON能合并表空间的自由空间，无论是自动还是通过ALTER命令，表空间必须让
它的PCTFREE>0。然后，这可能导致表空间中的表格按几何级数增长，除非表格替换PCTFR
EE设置。建议：对表空间设置PCTFREE为1，对表格设置为0。
优化回滚段
回滚段是或多或少的随机I/O组件，它们由DBWR并发地写，缓冲在数据库高速缓冲器高速缓
存中，并且存放在特殊化的表空间。回滚段之间以及回滚段和其它诸如数据表空间之间之
类的数据库I/O组件之间都会存在竞争。如何检测这一竞争呢？可以使用以下语句：
SELECT CLASS,COUNT
FROM V$WAITSTAT
WHRE CLASS LIKE ‘%undo%’
AND COUNT>0;
或 SELECT USN,WAITS
FROM V$ROLLSTAT
WHERE WAITS>0;
或从report.txt的回滚段部分检查TRANS_TBL_WAITS>0的UNIDO_SEGMENT。你还可以查看
report.txt的System wide wait events部分找到%undo%事件，或者从视图V$SYSTEM_EVEN
T中选择这些事件。另外，若你遇到ORA-01555：snapshot too old错误，则你已经用完了
回滚。你需要更多的回滚段，更大的回滚段，或两者。
对于超大型的数据库或对于极高的查询并发数据库（如一些DSS系统），若V$ROLLSTAT
的WAITS/GETS或TRANS_TBL_WAITS/TRANS_TBL_GETS比率>1%，则可能是一个回滚段竞争问题
。分配回滚段数为并发用户和程序数据除以4，最高为50。
下面我们给出一些回滚段的优化建议：
设置INITIAL=NEXT,PCTINCREASE为0。
设置MINEXTENTS>=20以便INITIAL*MINEXTENTS较表空间（数据文件）大小约低于1%以防支
态扩展。
设置OPTIMAL=INITIAL*MINEXTENTS以阻止不必要的回滚段收缩。
对于小事务(小事务指产生少量撤消信息的事务，一般在OLTP系统中找到)，使用高达5
0个回滚段，并且尽可能在应用内频繁地提交。大型事务（一般在批处理系统中找到）要求
单个大规模回滚段，对于这种情况，不必需要许多回滚段，一个大规模回滚段就够了。使
用
SET TRANSACTION USE ROLLBACK SEGMENT <rollback_segment>;
确保运行时间特别长的事务（大型事务）使用特别大的回滚段。
4. 优化重做日志
重做日志是顺序的I/O组件。一个时刻它们只被LGWR写一次，被缓冲存SGA的一个单独
部分（日志缓冲器）中，并且存作OS文件。因此，重做日志之间没有竞争，只有重做日志
与其他组件之间的竞争。
LGWR进程在发生下列事件之一时写向重做日志（本质上讲，是完成日志清仓）：
COMMIT，提交
CHECKPOINT，DBWR数据库清仓
检查点超时（时间间隔为LOG_CHECKPOINT_INTERVAL）或者日志缓冲器使用超过1/3。
下面是要达到最小化的一些主要性能优化目标：
对于给定应用，检查点尽可能多
重做等待事件尽可能多
重做锁存竞争问题
下面是配置检查点机制的建议：
让成员数不少于2的至少2组在不同的I/O通道（磁盘或控制器）上，每一成员同样大小。
在init.ora中将CHECKPOINT_PROCESS设置为TURE,以便将文件头同步义务由LGWR进程转给C
KPT进程。
Init.ora参数LOG_CHECKPOINT_TIMEOUT=0
设置LOG_CHECKPOINT_INTERVAL等于OS块中的一个日志大小加上一个OS块。举个例了：全部
重做日志成员大小为1M，1M=1024K=2048个OS块（一个OS块为512字节）。因些，将CHECKP
OINT_INTERVAL设置为2048+1,即时2049。这加上LOG_CHECKPOINT_TIMEOUT=0，将使能检查
点只发生在日志开关边界处，这会大大提高性能。
如何监控和优化重做等待事件和重做锁存竞争问题呢？在report.txt的Statistics部分检
查’redo log space request’，在report.txt的System Wide Wait events部分检查’l
og file space/switch’。另外，还可以使用v$SYSSTAT和V$SYSTEM_EVENT视图分别查看这
些事件。
若这些事件中的任何一个远大于0，这表示LOG_BUFFER的值太小。将它提高5%或更多，让你
的应用运行一段时间，并再次查询这些测量。重复这一过程直至这些事件为0或非常接近0
。
第八章：卸库与装库
一.卸库与装库的功能、及异同
二.操作方法
交互式卸库与装库：无参数调用
参数驱动方式的卸库与装库
Parfile关键字参数
交互式与参数驱动式混合使用
三. 卸库与装库方式
表方式
用户方式
全库方式
方式转换
方式使用
四.错误情况及解决办法
ORACLE不运行
读取DBA创建的卸库文件
3.不启动全库方式卸库
第九章：备份与恢复
一．由备份提供的保护措施
保护丢失的对象
保护丢失的数据库
二．ORACLE企业管理器备份
Backup Wizard
数据库打开时的备份
三．卸库与装库备份
具有足够长的空闲时间
空闲时限不够长
从卸库文件恢复
四．映象备份
1.冷备份
2.热备分
五．恢复
第十章：连接Internet应用(ORACLE Web Server)
一． ORACLE的网络体系结构
ORACLE网络体系结构简介
中间件技术
C/S式和S/B式
CORBA
二．ORACLE数据库连到Web Server
三．Web Listener
四．Web Application Server
五．DAD Administrator
六．Web请求代理
附：参考资料：
轻松掌握Oracle８数据库开发 David Lockman 著, 电子工业出版社
ORACLE数据库系统基础 俞盘祥 编著，清华大学出版社
数据库管理及应用开发 沈佩娟 汤荷美编著，清华大学出版社
Oracle7与客户／服务器计算技术从入门到精通 电子工业出版社
Oracle8初学者指南 Oracle Press,机械工业出版社
Oracle8数据库管理员手册 Oracle Press,机械工业出版社
Oracle 8 Server V8.0.4 On-line Generic Documentation CD
即学即用Oracle8 Willian G.Page Jr, Nathan Hughes 电子工业出版社
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/seawavecau/archive/2006/11/22/1403903.aspx
扫一扫
1343
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
