您的当前位置：首页 ORACLE日常维护手册

ORACLE日常维护手册

来源：乌哈旅游

DOC.NO. TIMESON-SM-2002-11-0001A

Normal (公开)



本地电信业务计费帐务系统

ORACLE操作手册



Version 1.0.0



2002.11.05

TIMESON

长沙天辰科技有限公司2000,2001,2002

ORACLE操作手册

前言.............................................................................................................................. 3 1.

数据库的创建........................................................................................................ 4 1.1. 1.2. 2.

以下为ORACLE启动初始文件INITORA.ORA ............................................ 4 以下是建库脚本............................................................................................ 5

数据库基本操作.................................................................................................... 9 2.1. 数据库的正常启动........................................................................................ 9 2.2. 安装启动与非安装启动.............................................................................. 10 2.3. 独占和共享启动.......................................................................................... 10 2.4. 约束启动...................................................................................................... 10 2.5. 强制启动...................................................................................................... 11 2.6. 数据库关闭.................................................................................................. 11 2.7. PL/SQL基本程序的编写 ........................................................................... 11 2.7.1. SQL语言简介 ..................................................................................... 12 2.7.2. PL/SQL简介........................................................................................ 16

3. 解决RDBMS问题 ............................................................................................. 22 3.1. 性能优化...................................................................................................... 22 3.1.1. 优化内存.............................................................................................. 23 3.1.2. 优化输入/输出..................................................................................... 25 3.1.3. 优化排序.............................................................................................. 27 3.1.4. 优化索引建立...................................................................................... 27 3.2. 备份和恢复.................................................................................................. 27 3.2.1. 备份提示.............................................................................................. 28 3.3. ORACLE 8 SERVER诊断特性........................................................................ 29 3.3.1. Oracle跟踪文件.................................................................................. 30 3.3.2. 设置跟踪事件...................................................................................... 31 3.3.3. V$监视视图......................................................................................... 34 3.3.4. 锁实用程序.......................................................................................... 36 3.4. ORACLE错误分析和解决方案.................................................................... 37 3.4.1. 常见Oracle错误................................................................................. 37 3.4.2. ORACLE内部错误 ............................................................................. 45 3.4.3. 优先权1/优先权2问题分类和诊断操作 ......................................... 46 3.5. 常见问题...................................................................................................... 48

长沙天辰科技有限公司

ORACLE操作手册

前言

为了加强计费系统数据库的操作安全及更有效的管理计费系统数据库,编写该操作手册。

在该手册若有错误及遗漏的地方还望各位读者不吝指出与谅解。

长沙天辰科技有限公司

ORACLE操作手册

1. 数据库的创建

以下仅为数据库创建的样例脚本，在实际系统中应根据数据库设计要与系统实际配置而改变参数：

1.1. 以下为ORACLE启动初始文件initora.ora

#this sampale init file writen by wzy 2002/11/15 db_name = \"jf\"

instance_name = ora8 #service_names = ora #db_files = 1024 control_files = (\"/home/oracle/OraCtl/control01.ctl\\"/home/oracle/OraCtl/control02.ctl\open_cursors = 100 max_enabled_roles = 50

db_file_multiblock_read_count = 8 db_block_buffers = 4096 shared_pool_size = 52428800 large_pool_size = 78643200 java_pool_size = 20971520

log_checkpoint_interval = 10000 log_checkpoint_timeout = 1800 processes = 115

parallel_max_servers = 5 log_buffer = 32768

max_dump_file_size = 10240 global_names = true

#oracle_trace_collection_name = \"\" db_block_size = 16384

#remote_login_passwordfile = exclusive #os_authent_prefix = \"\" job_queue_processes = 4 job_queue_interval = 60 open_links = 10

#large_pool_size = 614400 java_pool_size = 20971520 distributed_transactions = 10 mts_dispatchers = \"(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)\"

长沙天辰科技有限公司

ORACLE操作手册

mts_dispatchers = \"(protocol=TCP)\" #compatible = 8.1.0 sort_area_size = 65536

sort_area_retained_size = 65536 # log_archive_start = true

1.2. 以下是建库脚本

#!/bin/sh

ORACLE_SID=ora8 export ORACLE_SID

svrmgrl

connect internal

startup nomount pfile = $ORACLE_HOME/dbs/initora.ora CREATE DATABASE \"app1\" controlfile reuse maxdatafiles 500 maxinstances 8 maxlogfiles 32

character set ZHS16GBK

national character set ZHS16GBK

DATAFILE '/oradata/app1/system01.dbf' SIZE 200M

logfile group 1 ('/opt/oracle/oradata/app1/app1_redo01.log', '/oradata/app1/app1_redo01.log') SIZE 20M,

group 2 ('/opt/oracle/oradata/app1/app1_redo02.log', '/oradata/app1/app1_redo02.log') SIZE 20M, group 3 ('/opt/oracle/oradata/app1/app1_redo03.log', '/oradata/app1/app1_redo03.log') SIZE 20M; @/opt/oracle/product/8.1.7/rdbms/admin/catalog.sql;

CREATE ROLLBACK SEGMENT r0 TABLESPACE SYSTEM

STORAGE (INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS 20); ALTER ROLLBACK SEGMENT r0 ONLINE;

REM ************ TABLESPACE FOR OEM_REPOSITORY *************** CREATE TABLESPACE OEM_REPOSITORY DATAFILE '/oradata/app1/oemrep01.dbf' SIZE 5M REUSE

AUTOEXTEND ON NEXT 25M MAXSIZE 80M MINIMUM EXTENT 128k

DEFAULT STORAGE ( INITIAL 128k NEXT 128k MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);

REM ************** TABLESPACE FOR ROLLBACK ***************** CREATE TABLESPACE RBS DATAFILE '/oradata/app1/rbs01.dbf' SIZE 200M REUSE

长沙天辰科技有限公司

ORACLE操作手册

DEFAULT STORAGE ( INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED PCTINCREASE 0);

REM ************** TABLESPACE FOR TEMPORARY ***************** CREATE TABLESPACE TEMP DATAFILE '/oradata/app1/temp01.dbf' SIZE 200M REUSE DEFAULT STORAGE ( INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED PCTINCREASE 0) TEMPORARY;

REM ************** TABLESPACE FOR USER *********************

CREATE TABLESPACE USERS DATAFILE '/oradata/app1/users01.dbf' SIZE 50M REUSE DEFAULT STORAGE ( INITIAL 128k NEXT 128k MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);

REM ************** TABLESPACE FOR INDEX *********************

CREATE TABLESPACE INDX DATAFILE '/oradata/app1/indx01.dbf' SIZE 50M REUSE DEFAULT STORAGE ( INITIAL 128k NEXT 128k MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);

REM **** Creating four rollback segments **************add rollback segment to 20*** CREATE ROLLBACK SEGMENT r01 TABLESPACE RBS

STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r02 TABLESPACE RBS STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r03 TABLESPACE RBS STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r04 TABLESPACE RBS

STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r05 TABLESPACE RBS

STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r06 TABLESPACE RBS

STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r07 TABLESPACE RBS

长沙天辰科技有限公司

ORACLE操作手册

STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS

UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r08 TABLESPACE RBS STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r09 TABLESPACE RBS STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r10 TABLESPACE RBS

STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r11 TABLESPACE RBS

STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r12 TABLESPACE RBS

STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r13 TABLESPACE RBS STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r14 TABLESPACE RBS STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r15 TABLESPACE RBS

STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r16 TABLESPACE RBS

STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r17 TABLESPACE RBS

STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r18 TABLESPACE RBS

长沙天辰科技有限公司

ORACLE操作手册

STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS

UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r19 TABLESPACE RBS STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

CREATE ROLLBACK SEGMENT r20 TABLESPACE RBS STORAGE(INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS UNLIMITED optimal 4096K);

ALTER ROLLBACK SEGMENT r01 ONLINE; ALTER ROLLBACK SEGMENT r02 ONLINE; ALTER ROLLBACK SEGMENT r03 ONLINE; ALTER ROLLBACK SEGMENT r04 ONLINE; ALTER ROLLBACK SEGMENT r05 ONLINE; ALTER ROLLBACK SEGMENT r06 ONLINE; ALTER ROLLBACK SEGMENT r07 ONLINE; ALTER ROLLBACK SEGMENT r08 ONLINE; ALTER ROLLBACK SEGMENT r09 ONLINE; ALTER ROLLBACK SEGMENT r10 ONLINE; ALTER ROLLBACK SEGMENT r11 ONLINE; ALTER ROLLBACK SEGMENT r12 ONLINE; ALTER ROLLBACK SEGMENT r13 ONLINE; ALTER ROLLBACK SEGMENT r14 ONLINE; ALTER ROLLBACK SEGMENT r15 ONLINE; ALTER ROLLBACK SEGMENT r16 ONLINE; ALTER ROLLBACK SEGMENT r17 ONLINE; ALTER ROLLBACK SEGMENT r18 ONLINE; ALTER ROLLBACK SEGMENT r19 ONLINE; ALTER ROLLBACK SEGMENT r20 ONLINE;

ALTER ROLLBACK SEGMENT r0 OFFLINE;

REM **** SYS and SYSTEM users **************** alter user sys temporary tablespace TEMP;

alter user system temporary tablespace TEMP;

@/opt/oracle/product/8.1.7/rdbms/admin/catproc.sql; @/opt/oracle/product/8.1.7/rdbms/admin/caths.sql; @/opt/oracle/product/8.1.7/rdbms/admin/otrcsvr.sql; @/opt/oracle/product/8.1.7/rdbms/admin/catexp.sql;

@/opt/oracle/product/8.1.7/rdbms/admin/catdbsyn.sql;

长沙天辰科技有限公司

ORACLE操作手册

@/opt/oracle/product/8.1.7/rdbms/admin/catdefer.sql;

@/opt/oracle/product/8.1.7/rdbms/admin/catrep.sql;

@/opt/oracle/product/8.1.7/rdbms/admin/dbmspool.sql; @/opt/oracle/product/8.1.7/rdbms/admin/catparr.sql; @/opt/oracle/product/8.1.7/rdbms/admin/catblock.sql;

connect system/manager

@/opt/oracle/product/8.1.7/sqlplus/admin/pupbld.sql;

--create the product_profile and user_profile

--CATALOG.SQL, ---加载数据库本身的数据字典视图

--CATPROC.SQL, ---加载PL/SQL使用的PACKAGE --CATEXP.SQL ---加载EXPORT/IMPORT工具使用的数据字典

disconnect spool off exit

在数据库建立完后，可用以下语句（也可以在建库脚本中直接增加）对对tablespace增加数据文件： ALTER TABLESPACE \"tablespace_name\" ADD DATAFILE

'data_file_path/data_file_name' SIZE

2. 数据库基本操作

2.1. 数据库的正常启动

正常启动数据库的选项是normal，这也是数据启动的缺省选项。如果以这种方式启动则将打开一个现场（现场：现场为一逻辑概念，是系统为数据库打开提供的一系列的必需资源），并把数据库装入，然后打开数据库供用户使用。具体操作如下：

1．先进入SVRMGR管理器

$svrmgrl SVRMGR>

2．再联入inernal用户

$connect internal

3．在用startup命令启动（若不在启动文件所在目录进入的SVRMGR或

长沙天辰科技有限公司

ORACLE操作手册

启动文件名不是ORACLE缺省的文件名，则启动时应带启动目录与文件名）

SVRMGR>startup pfile=

2.2. 安装启动与非安装启动

安装启动的选项是mount，表示只将数据库装入现场而不打开数据库；非安装启动的选项是nomount，表示只建立数据库现场而不装入数据库，当然也不打开数据库。

1．安装启动

SVRMGR>startup {pfile=} mount

该方式启动数据库的的用途一般为：  为数据文件更名；

 增加、删除或改名事物日志文件；

 使事物日志归档模式选项有效或失效；  进行完整数据库的恢复操作

2．非安装启动

SVRMGR> startup {pfile=} nomount

该方式启动数据库的用途一般为：  创建一新的数据库

2.3. 独占和共享启动

独占启动选项是exclusive，表示只允许一个例程使用该数据库；共享启动的参数是shared，表示允许多个例程并行使用该数据库，即将数据库装入多个现场。

1．共享启动

SVRMGR>startup {pfile=} exclusive 2．独占启动

SVRMGR> startup {pfile=} shared

2.4. 约束启动

这种方式的选项是restrict，它启动数据库时装入并打开它，但此时的数据库只能为有特殊权限的数据库管理员使用，一般用户不能联入数据库。

1．约束启动 10

长沙天辰科技有限公司

ORACLE操作手册

SVRMGR> startup {pfile=} restrict

一般来说当用户有create session权限十可联入数据库，但对于restrict方式启动的数据库只有restricted session系统权限用户才允许联入。若在restrict方式下改变数据库运行方式，则可用alter system命令，如下： SVRMGR>alter system disable restricted session;

2.5. 强制启动

在一些非正常情况下有可能在正常方式下启动数据库遇到麻烦，或在上次因不能正常关闭数据库（如：用了参数abort）而导致不能正常启动数据库，则可考虑使用强制启动数据库，其参数为：force.

1．强制启动

SVRMGR> startup {pfile=} force;

此时，除非数据库有重大系统错误，一般情况下数据库可以起来。在应注意的是用此方式启动的数据库会上次非正常关闭数据导致的非正常数据丢掉，数据库启动后应及时的查看数据库的日志文件。

2.6. 数据库关闭

1．正常关闭

SVRMGR>shutdown normal;

这种方式下关闭数据库在关闭前检查所有的连接，并且发出命令后不允许再有新的连接，在等所有用户断开连接后关闭数据库。在此方式下关闭的数据库下次启动时不需要任何恢复过程。 2．立即关闭

SVRMGR>shutdown immediate;

这种方式下关闭数据库并不等待用户断开连接，而是由系统断开与用户的连接，然后关闭数据库。 3．异常关闭

SVRMGR>shutdown abort;

这种方式下关闭数据库系统不做任何的检查与回退操作而直接将数据库现场撤消。

2.7. PL/SQL基本程序的编写

ORACLE提供功能了功能强大的第三代编程语言PL/SQL。在本小节主要介绍基本的存储过程、存储函数及触发器的编写。

长沙天辰科技有限公司

ORACLE操作手册

2.7.1. SQL语言简介

2.7.1.1. SQL概述

SQL是一种面向数据库的通用数据处理语言规范，能完成以下几类功能：提取查询数据，插入修改删除数据，生成修改和删除数据库对象，数据库安全控制，数据库完整性及数据保护控制。

数据库对象包括表、视图、索引、同义词、簇、触发器、函数、过程、包、数据库链、快照等（表空间、回滚段、角色、用户）。数据库通过对表的操作来管理存储在其中的数据。 2.7.1.1.1. SQL*PLUS界面

退出：输入EXIT即可。 2.7.1.1.2. 命令的编辑与运行

 在命令提示符后输入SQL命令并运行，以分号结束输入；以斜杠结

束输入；以空行结束输入；

 利用SQL缓冲区进行PL/SQL块的编辑和运行；  利用命令文件进行PL/SQL块的编辑和运行。

2.7.1.2. 数据库查询

2.7.1.2.1. 用SELECT语句从表中提取查询数据

SELECT 的语法为：

SELECT [DISTINCT] {column1,column2,…} FROM tablename WHERE {conditions} GROUP BY {conditions} ORDER BY {expressions} [ASC/DESC];

说明：SELECT子句用于指定检索数据库的中哪些列，FROM子句用于指定从哪一个表或视图中检索数据。

2.7.1.2.2. SELECT中的操作符及多表查询WHERE子句

WHERE子句中的条件可以是一个包含等号或不等号的条件表达式，也可以是一个含有IN、NOT IN、BETWEEN、LIKE、IS NOT NULL等比较运算符的条件式，还可以是由单一的条件表达通过逻辑运算符组合成复合条件。 2.7.1.2.3. ORDER BY 子句

ORDER BY 子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDER BY 子句指定的表达式的值确定。

长沙天辰科技有限公司

ORACLE操作手册

2.7.1.2.4. 连接查询

利用SELECT语句进行数据库查询时，可以把多个表、视图的数据结合起来，使得查询结果的每一行中包含来自多个表达式或视图的数据，这种操作被称为连接查询。

连接查询的方法是在SELECT命令的FROM子句中指定两个或多个将被连接查询的表或视图，并且在WHERE子句告诉ORACLE如何把多个表的数据进行合并。根据WHERE子句中的条件表达式是等还是不等式，可以把连接查询分为等式连接和不等式连接。 2.7.1.2.5. 子查询

如果某一个SELECT命令（查询1）出现在另一个SQL命令（查询2）的一个子句中，则称查询1是查询2的子查询。 2.7.1.3. 基本数据类型

ORACEL支持下列内部数据类型：

       

VARCHAR2 变长字符串，最长为2000字符。 NUMBER 数值型。

LONG 变长字符数据，最长为2G字节。 DATE 日期型。

RAW 二进制数据，最长为255字节。

LONG RAW 变长二进制数据，最长为2G字节。 ROWID 二六进制串，表示表的行的唯一地址。 CHAR 定长字符数据，最长为255。

2.7.1.4. 常用函数

一个函数类似于一个算符，它操作数据项，返回一个结果。函数在格式上不同于算符，它个具有变元，可操作0个、一个、二个或多个变元，形式为：

函数名（变元，变元，„）函数具有下列一般类形：  单行函数  分组函数 1．单行函数对查询的表或视图的每一行返回一个结果行。它有数值函

数，字符函数，日期函数，转换函数等。

2．分组函数返回的结果是基于行组而不是单行，所以分组函数不同于单

行函数。在许多分组函数中可有下列选项：

 DISTRNCT 该选项使分组函数只考虑变元表达式中的不同值。  ALL该选项使分组函数考虑全部值，包含全部重复。

全部分组函数（除COUNT（*）外）忽略空值。如果具有分组函数的查询，没有返回行或只有空值（分组函数的变元取值的行），则分组函数返回空值。

长沙天辰科技有限公司

ORACLE操作手册

2.7.1.5. 数据操纵语言命令

数据库操纵语言（DML）命令用于查询和操纵模式对象中的数据，它不隐式地提交当前事务。它包含UPDATE、INSERT、DELETE、EXPLAIN PLAN、SELECT和LOCK TABLE 等命令。下面简单介绍一下：

1)UPDATE tablename SET {column1=expression1,column2=expression2,…} WHERE {conditions};

例如：

SQL> update serv set state=‟F0K‟ where serv_id=123456 and serv_seq_nbr=1;

UPDATE子句指明了要修改的数据库是EMP,并用WHERE子句限制了只对名字(ENAME)为‟MARTIN‟的职工的数据进行修改,SET子句则说明修改的方式,即把‟MARTION‟的工作名称(JOB)改为‟MARAGER‟.

2)INSERT INTO

{expression1,expression2,…}; 例如：

tablename

{column1,column2,…}

VALUES

SQL> insert into bill.serv select * from trans.serv_bak;

3)DELETE FROM tablename WHERE {conditions};

例如： SQL>

delete

from

acct_item_area01

where

acct_id=123245

and

account_date=‟200110‟ and acct_item_type_id = 11001;

DELETE命令删除一条记录,而且DELETE命令只能删除整行,而不

能删除某行中的部分数据.

4)事务控制语句

提交命令(COMMIT):可以使数据库的修改永久化.设置

AUTOCOMMIT为允许状态:SQL >SET AUTOCOMMIT ON;

回滚命令(ROLLBACK):消除上一个COMMIT命令后的所做的全部修改,使得数据库的内容恢复到上一个COMMIT执行后的状态.使用方法是:

2.7.1.6. 创建表、视图、索引、同义词、用户 2.7.1.6.1. 表

建立表主要指定义下列信息：  列定义

 完整性约束  表所在表空间  存储特性

 可选择的聚集

 从一查询获得数据语法如下：

CREATE TABLE tablename (column1 datatype [DEFAULT expression] [constraint],

column1 datatype [DEFAULT expression] [constraint], „„)

[STORAGE子句] [其他子句…]；

例如： SQL> create table serv (serv_id number(10),serv_seq_nbr number(3))

长沙天辰科技有限公司

ORACLE操作手册

2 3

tablespace data_bill

storage (initial 10m next 5m);

2.7.1.6.2. 视图

视图是一个逻辑表，它允许操作者从其它表或视图存取数据，视图本身不包含数据。视图所基于的表称为基表。

引入视图有下列作用：

 提供附加的表安全级，限制存取基表的行或/和列集合。  隐藏数据复杂性。

 为数据提供另一种观点。

 促使ORACLE的某些操作在包含视图的数据库上执行，而不在另一个

数据库上执行。 2.7.1.6.3. 索引

索引是种数据库对象。对于在表或聚集的索引列上的每一值将包含一项，为行提供直接的快速存取。在下列情况ORACLE可利用索引改进性能：

 按指定的索引列的值查找行。  按索引列的顺序存取表。语法：

create index on [storage语句] [其它语句]; 例如：

SQL> create index idx_serv_01 on serv(srev_id,serv_seq_nbr) 2 tablespace data_bill_idx 3 storage (initial 5m next 5m); 2.7.1.6.4. 同义词

同义词：为表、视图、序列、存储函数、包、快照或其它同义词的另一个名字。使用同义词为了安全和方便。对一对象建立同义词可有下列好处：  引用对象不需指出对象的持有者。

 引用对象不需指出它所位于的数据库。  为对象提供另一个名字。语法：

craete synonym for [user_name].; 例如：

（假定在CALL用户下）

SQL> create synonym serv for bill.serv;

注意：在同义词建立后要授权

长沙天辰科技有限公司

ORACLE操作手册

2.7.1.6.5. 用户

语法：

CREATE USER username IDENTIFIED BY password; 例如：

SQL> create user bill identified by jfxt;

2.7.2. PL/SQL简介

2.7.2.1. PL/SQL概述

PL/SQL是Oracle对SQL规范的扩展，是一种块结构语言，即构成一个PL/SQL程序的基本单位（过程、函数和无名块）是逻辑块，可包含任何数目的嵌套了快。这种程序结构支持逐步求精方法解决问题。一个块（或子块）将逻辑上相关的说明和语句组合在一起，其形式为：

DECLARE --说明 BEGIN --语句序列 EXCEPTION --例外处理 END；它有以下优点:

 支持SQL;  生产率高;  性能好;  可称植性;

 与ORACLE集成. 2.7.2.2. PL/SQL体系结构

PL/SQL运行系统是种技术，不是一种独立产品，可认为这种技术是PL/SQL块和子程序的一种机，它可接收任何有效的PL/SQL块或子程序。如图所示：

PL/SQL机 PL/SQL块过程性语句 PL/SQL 执行器

SQL语句

PL/SQL机可执行过程性语句，而将SQL语句发送到ORACLE服务器上的SQL语句执行器。在ORACLE预编译程序或OCI程序中可嵌入无名的PL/SQL块。如果ORACLE具有PROCEDURAL选件，有名的PL/SQL块（子程序）可单独编译，永久地存储在数据库中，准备执行。

长沙天辰科技有限公司

ORACLE操作手册

2.7.2.3. PL/SQL基础

PL/SQL有一字符集、保留字、标点、数据类型、严密语法等，它与SQL有相同表示，现重点介绍。 2.7.2.3.1. 数据类型

如下表所示：数据类型子类型纯量数值 BINARY_INTEGER NATURAL,POSITIVE 类型 NUMBER DEC,DECIMAL,DOUBLE PRECISION,PLOAT,INTEGER,INT,NUMERIC,REAL,SMALLINT 字符 CHAR CHARACTER,STRING VARCHAR2 VARCHAR LONG LONG RAW RAW RAWID 逻辑 BOOLEAN 日期 DATE 组合记录 RECORD 类型表 TABLE 2.7.2.3.2. 变量和常量

在PL/SQL程序中可将值存储在变量和常量中，当程序执行时，变量的值可以改变，而常量的值不能改变。 2.7.2.3.3. 程序块式结构

DECLARE

--变量说明部分 BEGIN „„„„ „„„„ „„„„ END； 2.7.2.3.4. 控制语句

分支语句：

IF condition THEN

长沙天辰科技有限公司

ORACLE操作手册

sequence_statements; END IF;

IF condition1 THEN

Sequence_statements1; ELSE

Sequence_statements2; END IF;

IF condition1 THEN

Sequence_statements1; ELSIF condition2 THEN Sequence_statements2; „„„„ ELSE

Sequence_statements(n+1); END IF;

2.7.2.3.5. 循环语句

LOOP

Statements;

IF condition THEN EXIT; END IF; END LOOP;

WHILE condition LOOP Statements; END LOOP;

FOR counter IN lower_con..high_con|(data-cube) LOOP Statements; END LOOP; 2.7.2.3.6. 子程序 2.7.2.3.6.1. 存储过程

语法：

create [or replace] procedure 函数名(参数1，参数2„„) IS | AS [局部变量说明]

BEGIN 执行语句；

长沙天辰科技有限公司

ORACLE操作手册

END；

例：

以下为一简单的样例存储过程：

CREATE OR REPLACE PROCEDURE

utl_analyze_prg(

iOnwer IN VARCHAR2 DEFAULT NULL,

/*该参数为需要统计的用户，缺省则为全部*/

/*该程序用来计算统计*/

/*注意：该程序应在8i及以上版本中运行*/ -- -- tOwner t_Level

VARCHAR2(30); --存储输入的用户名 VARCHAR2(30); --存储输入的统计参数 VARCHAR2(30); --存储选取的表名 VARCHAR2(300);--存储形成的分析语句

iLevel IN VARCHAR2 DEFAULT NULL /*参数iLevel为统计，

*/ )

其值为：1.for table

2.for colums 3.for all columNs 4.for all indexed columns 5.for all indexes

t_TaleName t_AnalyzeStr

BEGIN

t_Owner := UPPER(TRIM(iOwer)); t_Level := UPPER(LTRIM(RTRIM((iLevel)));

若输入的用户名为空时则退出程序 IF t_Owner IS NULL THEN

DBMS_OUTPUT.PUT_LINE('请输入要用户名'); RETURN;

END IF;

若输入的iLevel不合法则退出程序

IF t_Level NOT IN ('FOR TABLE','FOR COLUMS','FOR ALL COLUMS',

'FOR ALL INDEXED COLUMNS','FOR ALL INDEXES')

THEN

DBMS_OUTPUT.PUT_LINE('输入的第二参数不合法'); RETURN;

END IF;

FOR Rec IN (SELECT table_name FROM all_tables WHERE owner=t_Owner) LOOP BEGIN t_AnalyzeStr := 'analyze table '||Rec.table_name||' compute statistics '|| t_Level;

长沙天辰科技有限公司

ORACLE操作手册

EXECUTE IMMEDIATE t_AnalyzeStr;

EXCEPTION WHEN OTHERS THEN

ROLLBACK; RAISE;

END;

END LOOP;

END;

2.7.2.3.6.2. 存储函数

语法：

create [or replace] function 函数名(参数1，参数2„„) RETURN 类型 IS | AS

[局部变量说明] BEGIN 执行语句； END；

例：

CREATE OR REPLACE FUNCTION IS

/*该程序用来估算统计*/

/*注意：该程序应在8i及以上版本中运行*/

tOwner t_Per

VARCHAR2(30); --存储输入的用户名

utl_analyze_prg(iOnwer IN VARCHAR2 DEFAULT NULL,

/*该参数为需要统计的用户，缺省则为全部*/ iEstimatePercent IN NUMBER DEFAULT 100 /*该参数为需要估算统计的百分比*/ )

RETURN INTEGER

NUMBER(3); --存储输入的估算统计的百分比参数

VARCHAR2(30); --存储选取的表名

t_TaleName t_AnalyzeStr

VARCHAR2(300);--存储形成的分析语句

BEGIN

t_Owner := UPPER(TRIM(iOwer));

-- t_Per := UPPER(LTRIM(RTRIM((iLevel)));

-- 若输入的用户名为空时则退出程序

IF t_Owner IS NULL THEN

长沙天辰科技有限公司

ORACLE操作手册

DBMS_OUTPUT.PUT_LINE('请输入要用户名'); RETURN -1;

END IF;

-- 若输入的估算统计百分比参数不正确则退出

IF t_Per NOT BETWEEN 1 AND 100 THEN

DBMS_OUTPUT.PUT_LINE('输入的估算统计百分比参数不正确'); RETURN -2;

END IF;

FOR Rec IN (SELECT table_name FROM all_tables WHERE owner=t_Owner) LOOP

BEGIN

t_AnalyzeStr := 'analyze table '||Rec.table_name||' estimate statistics '||

'sample '||to_char(t_Per)||' percent ';

EXECUTE IMMEDIATE t_AnalyzeStr;

EXCEPTION WHEN OTHERS THEN

ROLLBACK; RETURN -3;

END;

END LOOP; RETURN 0

END;

2.7.2.3.6.3. 触发器

语法： create [or replace ] trigger 触发器名[参数1，参数2„„] after|before update|delete|insert on 表名 [for each row] begin 语句序列； end; 例：

以下为一简单的触发器程序。写触发器需要注意的是：在触发器中不能有COMMIT或ROLLBACK等事务操作语句。

CREATE OR REPLACE TRIGGER tr_serv

长沙天辰科技有限公司

ORACLE操作手册

AFTER UPDATE OR DELETE OR INSERT ON serv FOR EACH ROW BEGIN

IF UPDATING('STATE') THEN

INSERT INTO serv_history(serv_id,serv_seq_nbr,state,state_date)

VALUES (:OLD.serv_id,:OLD.serv_seq_nbr,:OLD.state_date);

END IF;

IF deleting THEN

INSERT INTO serv_history(serv_id,serv_seq_nbr,state_date,fixed_address,

serv_type_id,billing_type_id,state,cust_id)

VALUES (:OLD.serv_id,:OLD.serv_seq_nbr,:OLD.state_date,:OLD.fixed_address,

:OLD.serv_type_id,:OLD.billing_type_id,:OLD.state,:OLD.cusr_id);

END IF;

IF ISNERTING THEN

INSERT INTO serv_history(serv_id,serv_seq_nbr,state_date,fixed_address,

serv_type_id,billing_type_id,state,cust_id)

VALUES

(:NEW.serv_id,:NEW.serv_seq_nbr,:NEW.state_date,:NEW.fixed_address,

:NEW.serv_type_id,:NEW.billing_type_id,:NEW.state,:NEW.cusr_id);

END IF; END;

3. 解决RDBMS问题

Oracle 8 Server是O r a c l e软件中商业事务解决方案的核心。本章讲解Oracle 8性能优化和诊断特性的技术和技巧，重点讨论O r a c l e错误解决方案，包括内部错误，最后一节FA Q，是从O S S接收的实时支持电话中汇编出来的。

3.1. 性能优化

Oracle RDBMS优化是一项要反复进行的工作，要耗费大量的时间和财力。下面介绍的是一些基本的优化提示和技术这对一般的D B A会很有帮助。尽管优

长沙天辰科技有限公司

ORACLE操作手册

化既昂贵又费时，应用程序在设计和开发期间仍要特别充分考虑优化，这一节将不涉及有关硬件或操作系统的优化处理，然而，所介绍的知识有助于O r a c l e用户了解Oracle RDBMS的优化处理。

3.1.1. 优化内存

在O r a c l e体系结构中实现分布式快速存取和充实内存是很重要的。建议用户在优化应用程序后和输入/输出( I / O )优化前，进行与内存有关的优化操作。而且首先要优化操作系统，并在处理共享池前考虑私有S Q L和P L / S Q L区。要不惜任何代价避免页面调度或交换。每次都必须把系统全局区( S G A )放入内存。用户可以连接Server Manager(服务器管理员)和数据库，并执行SHOW SGA命令，确定S G A的大小。 SVRMGR> show sga

系统全局区域合计有 221909152个字节 Fixed Size 73888个字节 Variable Size 154648576个字节 Database Buffers 67108864个字节 Redo Buffers 77824个字节

此外，可以查询V $ G A视图得到类似的结果。 SVRMGR> select * from v$sga;

NAME VALUE

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

Fixed Size 73888 Variable Size 154648576 Database Buffers 67108864 Redo Buffers 77824 已选择4行。

用户还可以在i n i t . o r a文件中设置参数P R E _ PA G E _ S G A，将S G A预装到物理内存中。

pre_page_sga=yes

除了S G A之外，还要确保S Q L和P L / S Q L区正确优化。用户必须尽量少地调用P a r s e。可以为事务产生一个S Q L跟踪文件，来查看P a r s e与E x e c u t e的调用比率(计数统计如下)，这个比率应尽可能低。下面的内容摘自S Q L跟踪文件。

call count cpu elapsed disk query current rows ---- ------- ------ -------- -------- ------ --------- ---------

长沙天辰科技有限公司

ORACLE操作手册

parse 4 0.00 0.00 0 0 0 0 execute 7 0.00 0.00 0 0 0 0 fetch 0 0.00 0.00 0 0 0 0 ------ ------- ------- ---------- ---------- ----------- ------- ---------- 总计 11 0.00 0.00 0 0 0 0 在这个例子中， P a r s e调用与E x e c u t e调用的比率为4 / 7。应尽量优化应用程序，使这个比率最低。

通过i n i t . o r a文件O P E N _ C U R S O R S参数控制私有S Q L区也是很重要的。该参数决定了用户在某时刻可拥有的游标的最大数。但这个参数不控制整个系统，只决定每个进程使用的内存空间量。

用户必须最大程度地优化共享池中的库高速缓存和数据字典高速缓存。库高速缓存包括S Q L和P L / S Q L区。再重复一遍，这样做的宗旨是尽可能减少库高速缓存中的重载。用户应尽量减少对数据库的a d _ h o c查询，以确保库高速缓存的使用最优化。可以用下面的查询确定库高速缓存的性能：

SVRMGR> select sum(pins),sum(reloads) from v$librarycache; SUM(PINS) SUM(RELOAD ---------- ---------- 921798 152 已选择 1 行。

用户必须保证r e l o a d s和p i n s的比率尽可能低。为了优化库高速缓存，必须增加i n i t . o r a文件中SHARED_POOL_SIZE及(或) O P E N _ C U R S O R S的参数值。

另一项与库高速缓存有关的是游标在会话中的缓存方式。当相关应用程序游标打开时，必须确保S Q L区不会再重新分配。此外，必须保证在一个会话中打开游标的最大数被放入缓存。可以使用i n i t . o r a文件中的参数，优化会话中缓存游标数：

# the following parameter will ensure that a shared SQL area never be de-allocated #when an associated cursor is open; cursor_space_for_time=true

#we have specified that a maxium of 50 cursors can be cached for a user session session_cached_cursor=50

要确定S E S S I O N _ C A C H E D _ C U R S O R S的正确值，可用类似下面的查询：

SQL> select sid,username,program from v$session;

SID USERNAME PROGRAM

长沙天辰科技有限公司

ORACLE操作手册

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

1 bill oracle@JF_SRV (PMON) 2 bill oracle@JF_SRV (DBW0) 3 bill oracle@JF_SRV (LGWR) 4 oracle@JF_SRV (CKPT) 5 oracle@JF_SRV (SMON)

必须尽量确保查询到的值与S Q L跟踪的P a r s e总计数之比接近1。与数据字典高速缓存相关的问题类似于库高速缓存。其目标是，使数据字典高速缓存里的内存数据库块尽可能多。下面的查询可以查看性能的优劣：

SQL> select sum(gets),sum(getmisses) from v$rowcache;

SUM(GETS) SUM(GETMISSES) ----------

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

oracle@JF_SRV (RECO)

258411 43943

必须尽量使g e t m i s s e s比率接近0。要优化性能，可以增加i n i t . o r a文件中S H A R E D _ P O O L _ S I Z E和/或D B _ B L O C K _ B U F F E R S参数的值。

3.1.2. 优化输入/输出

输入/输出的优化必须在内存优化之后进行。

建议不要把非O r a c l e文件与O r a c l e文件放在同一个磁盘上。此外，用户也应当把重做日志文件与数据文件分开放置。这些表的相关表和索引应放在独立磁盘中具有数据文件的表空间内，其目的是减少对磁盘的并发存取。 V $ F I L E S TAT视图提供有关物理读写的信息(物理输入/输出针对磁盘，逻辑输入/输出针对内存)。

SQLWKS> select name,phyrds,phywrts from v$datafile a,v$filestat b 2> where a.file# = b.file#; NAME PHYRDS PHYWS RT ----------------------------------------------- ---------- ---------- /data2/ora_data/system01.dbf 1175 1123 /data1/ora_idx_data/rbs_system01.dbf 34 2281 /data2/ora_data/data_bill01.dbf 7 100

长沙天辰科技有限公司

ORACLE操作手册

/data1/ora_idx_data/data_bill_idx01.dbf 6 24 /data1/ora_data/temp01.dbf 12 12 /data1/ora_data/tool01.dbf 6 4 /data2/ora_data/data_bill02.dbf 7 55 /data2/ora_data/data_bill03.dbf 6 45 /data2/ora_data/data_bill04.dbf 6 115 /data2/ora_data/data_bill05.dbf 6 525 /data1/ora_idx_data/data_bill_idx02.dbf 6 192 /data2/ora_data/data_bill06.dbf 6 540 /data2/ora_data/data_bill07.dbf 6 101 /data1/ora_idx_data/data_bill_idx03.dbf 6 284 /data1/ora_idx_data/rbs_system02.dbf 6 4 /data1/ora_idx_data/rbs_system03.dbf 6 4 /data1/ora_idx_data/rbs_system04.dbf 6 4 /data2/ora_data/system02.dbf 2586 4054 /data2/ora_data/data_bill08.dbf 6 136 已选择19行。

必须把物理读写的总量控制在用户硬件和操作系统的最优限制内。另一个普遍的输入/输出瓶颈是由链接的行引起的(链接的行跨多个块)，可以采用下面描述的过程来减少链接的行。

首先从安装目的( $ ORACLE_HOME /rdbms /admin )中运行utlchain.sql脚本文件，生成CHAINED_ROWS表。使用AYALYZETABLE命令得到CHAINED _ R O W S表中链接的列表。

SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql 表已创建。

SQL> desc chained_rows

名称空? 类型 ----------------------- -------- ------------- OWNER_NAME VARCHAR2(30) TABLE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) PARTITION_NAME VARCHAR2(30)

长沙天辰科技有限公司

ORACLE操作手册

SUBPARTITION_NAME VARCHAR2(30) HEAD_ROWID ROWID ANALYZE_TIMESTAMP DATE SQL>ayalyze table list chined rows; Table Ayalyzed.

SQL>select * from chined rows; No rows selected.

如果想得到C H A I N E D _ R O W S表的记录，可以使用C R E ATE TABLES SELECT语句创建一个临时表。从现存表中用D E L E T E命令删除各项记录，再用I N S E RT命令将其从临时表中插回。

3.1.3. 优化排序

排序是一项花销很大的操作，而且对性能的影响程度也较大。因此，使大部分排序操作在内存中完成，而不在磁盘上进行，是至关重要的。要确定内存中排序的量和磁盘上排序的量，可用下述查询：

SQL> select name,values from v$sysstat where upper(name) in 2 („sorts (memory)‟,‟sorts(disk)‟);

如果用户认为在磁盘上的排序意义较大，可以增加i n i t . o r a文件S O RT _ A R E A _ S I Z E参数的设置值。

3.1.4. 优化索引建立

索引建立要花费大量时间。Oracle 7.3 中介绍了索引创建的一种新的方式，即A LT E R INDEX REBUILD，这比起删除和重建索引要迅速得多。如果用户的数据已经排序，在构造索引时可以使用N O S O RT选项。

SQL> alter index rebuild;