第 1 页 共 75页
第 1 页 共 75页
ORACLE 傻 瓜 手 册
To be DBA or not to be, that is NOT the question. ---- Arron
作者允许自由散发此文档,但对其进行的任何修改应通知作者,以便于维护版本。
作者 email:zhou_arron@163.com
Oracle8 以 8.1.5 为界分为普通版本和 internet 版本。普通版版本号 8.0.x,接触较多的是
8.0.5;internet 版版本号包括 8.1.5(Release 1),8.1.6(Release 2),8.1.7(Release 3)。普通
版简称 Oracle 8,internet 版简称 Oracle 8i。如果不作特别说明,文中凡出现 Oracle 8i 均指
8.1.7 版。
Oracle9i 目前出到第二版,版本号为 9.2,简称 Oracle 9i。如果不作特别说明,文中凡出
现 Oracle 9i 均指 9.2 版。
本手册介绍 Oracle 配置的基本方法,描述的是“所然”而不是“所以然”。全部操作以
命令行方式出现,不涉及 GUI(只有白刃战才是真正的战斗)。鉴于大家对 Windows 已经十
分熟悉,同时为了避免 Windows 和 Unix 两种截然不同的使用和开发风格给描述带来的复杂
性,所以本手册不介绍在 Windows 上的 Oracle(上帝的归上帝,恺撒的归恺撒)。
文中所有例子以 oradb 作为数据库实例名,数据库用户 dbuser,口令 oracle。如果不作
特别说明,关于 Oracle 8i 所有的例子都在 Solaris 8 Intel Platform+Oracle 8iR3 上通过,关于
Oracle 9i 所有的例子都在 RedHat Linux 7.3+Oracle 9iR2 上通过。附录文件 sample.tar 包含全
部示例,简称附录。
大量使用表 emp 作为例子(参见附录 08_proc/proc/single/emp.sql):
create table emp
(
no number(12) not null,
name char(20) not null,
age number(6) not null,
duty char(1) not null,
salary number(12) not null,
upd_ts date not null,
primary key (no)
);
开发中对应 emp 表结构,定义其宿主结构(参见附录 08_proc/proc/single/db.h):
typedef struct
{
double no;
char name[21];
int age;
char duty[2];
double salary;
char upd_ts[15];
} emp_t;
第 2 页 共 75页
第 2 页 共 75页
修改历史:
2000/07 版本 1.0
2000/09 版本 1.1
增加 Linux 安装,export,import 使用,数据库监控及优化(utlbstat,utlestat,
分析 session),语言时间环境变量设置,Oracle8.0.5 手工建库脚本(wei_dick
提供,稍加修改)
2000/10 版本 1.2
修改 Linux 安装中 RedHat 6.x+Oracle 8.1.6、数据库优化中配置文件和
session 分析、常用技巧中下载上传文本数据和访问他机数据库;增加创建
数据库实例中数据字典参考、常用技巧中删除冗余记录、应用开发,常见
错误
感谢 liu_freeman,jiao_julian,huang_miles 等人对开发工具所作的努力
2001/03 版本 1.3
修改安装部分、init.ora 配置、常用技巧、应用开发;增加手工建库、MTS
配置;重写开发工具
感谢 li_bo 的大力帮助
2001/09 版本 1.4
修改数据库优化,使之较系统化;增加应用开发中多线程下的数据库连接
2002/04 版本 1.5
修改数据库优化、多线程条件下数据库编程;分离附录的程序范例
2002/12 版本 2.0
重新安排内容,增加 Oracle 9i 安装配置、OCI 开发、mysql 安装配置开发,
补充数据库优化、PROC 开发
第 3 页 共 75页
第 3 页 共 75页
ORACLE
傻
瓜
手
册
................................................................................................1
1
安装
.........................................................................................................................6
1.1 通用设置.......................................................................................................................6
1.2 UnixWare7......................................................................................................................7
1.2.1 Oracle 8
........................................................................................................................7
1.3 HP-UX...............................................................................................................................8
1.3.1 Oracle 8
........................................................................................................................8
1.4 Linux...............................................................................................................................9
1.4.1 kernel 2.0 & glibc 2.0
...........................................................................................9
1.4.2 kernel 2.2 & glibc 2.1
...........................................................................................9
1.4.3 kernel 2.4 & glibc 2.2
.........................................................................................10
1.5 Solaris.........................................................................................................................11
2
创建
.......................................................................................................................13
2.1 Oracle 8 & 8i...........................................................................................................13
2.1.1 工具创建
......................................................................................................................13
2.1.2 手工创建
......................................................................................................................13
2.1.3 MTS(multi-threaded server)
............................................................................14
2.1.4 调整临时表空间
..........................................................................................................15
2.1.5 调整回滚表空间
..........................................................................................................15
2.1.6 调整日志
......................................................................................................................15
2.1.7 调整用户表空间
..........................................................................................................16
2.1.8 创建用户
......................................................................................................................17
2.1.9 创建数据对象
..............................................................................................................17
2.1.10 创建只读用户
..............................................................................................................18
2.1.11 启动及关闭数据库实例
.............................................................................................19
2.1.12 网络配置
......................................................................................................................19
2.2 Oracle 9i....................................................................................................................21
2.2.1 手工创建
......................................................................................................................21
2.2.2 创建用户表空间
..........................................................................................................22
3
初始化文件配置
..................................................................................................23
3.1 Oracle 8 & 8i...........................................................................................................23
3.2 Oracle 9i....................................................................................................................25
4
工具
.......................................................................................................................26
4.1 sqlldr...........................................................................................................................26
4.2 exp..................................................................................................................................27
4.3 imp..................................................................................................................................28
第 4 页 共 75页
第 4 页 共 75页
4.4 sqlplus.........................................................................................................................29
4.4.1 命令行参数
..................................................................................................................29
4.4.2 提示符命令
..................................................................................................................29
4.4.3 SET 选项
.......................................................................................................................30
4.4.4 例子
..............................................................................................................................30
5
备份及恢复
..........................................................................................................32
5.1 export 与 import 方式............................................................................................32
5.2 冷备份...........................................................................................................................32
5.3 联机全备份+日志备份............................................................................................32
5.3.1 设置
..............................................................................................................................32
5.3.2 步骤
..............................................................................................................................33
5.3.3 恢复
..............................................................................................................................33
5.4 注意要点.....................................................................................................................34
6
数据库优化
..........................................................................................................35
6.1 通用设置 ......................................................................................................................35
6.1.1 硬件配置
......................................................................................................................35
6.1.2 应用配置
......................................................................................................................35
6.1.3 日常性能监控
..............................................................................................................36
6.2 实战分析.....................................................................................................................36
6.2.1 总体分析
......................................................................................................................37
6.2.2 详细分析
......................................................................................................................37
6.3 专题分析 ......................................................................................................................39
6.3.1 巨表查询
......................................................................................................................39
6.3.2 对比测试
......................................................................................................................41
6.3.3 上下载数据
..................................................................................................................44
6.3.4 回滚空间快照陈旧(snapshot too old)
............................................................46
7
常用技巧
..............................................................................................................48
7.1 增加、更改和删除域................................................................................................48
7.2 删除冗余记录 .............................................................................................................49
7.3 更改字符集..................................................................................................................49
7.4 表数据迁移..................................................................................................................50
7.5 成批生成数据 .............................................................................................................50
7.6 注意要点 ......................................................................................................................51
8
嵌入式
SQL
(
C
)
...............................................................................................53
8.1 编译 ...............................................................................................................................53
8.2 SQL 语句......................................................................................................................54
8.2.1 内部类型与宿主类型对应
.........................................................................................54
第 5 页 共 75页
第 5 页 共 75页
8.2.2 连接和断开..................................................................................................................54
8.2.3 事务
..............................................................................................................................55
8.2.4 标准 SQL 语句
..............................................................................................................55
8.2.5 动态 SQL 语句
..............................................................................................................55
8.2.6 数组操作
......................................................................................................................56
8.3 编程框架 ......................................................................................................................58
8.3.1 总体原则
......................................................................................................................58
8.3.2 单线程和多线程
..........................................................................................................59
8.3.3 开发工具
......................................................................................................................60
9 OCI—Oracle Call Interface ................................................................................61
9.1 连接和断开..................................................................................................................61
9.1.1 句柄层次
......................................................................................................................61
9.1.2 连接流程
......................................................................................................................61
9.1.3 断开流程
......................................................................................................................62
9.2 SQL 语句 .......................................................................................................................62
9.2.1 事务
..............................................................................................................................62
9.2.2 无结果集的 sql 语句
.................................................................................................63
9.2.3 有结果集的 sql 语句
.................................................................................................63
9.2.4 LOB
.................................................................................................................................65
9.3 编程框架.....................................................................................................................67
9.3.1 总体原则
......................................................................................................................67
9.3.2 sql 语句
.......................................................................................................................68
9.3.3 函数
..............................................................................................................................69
10
附录
—MYSQL .................................................................................................72
10.1 安装配置.....................................................................................................................72
10.2 管理 ..............................................................................................................................72
10.2.1 初始调整
......................................................................................................................72
10.2.2 建立用户对象
..............................................................................................................73
10.3 开发 ..............................................................................................................................73
10.3.1 连接和断开
..................................................................................................................73
10.3.2 无结果集的 sql 语句
...................................................................................................74
10.3.3 有结果集的 sql
............................................................................................................74
10.3.4 错误处理
......................................................................................................................75
评论0