资源介绍
目录
序 ............................................................................................... 17
前言 ............................................................................................ 20
本书内容.................................................................................... 21
读者对象.................................................................................... 21
本书组织结构............................................................................... 23
源代码和有关更新.......................................................................... 29
勘误表....................................................................................... 29
配置环境....................................................................................... 30
建立SCOTT/TIGER 模式.................................................................. 30
环境.......................................................................................... 32
设置SQL*Plus 的AUTOTRACE ......................................................... 35
配置Statspack............................................................................. 37
定制脚本.................................................................................... 38
SHOW_SPACE ............................................................................. 49
BIG_TABLE ................................................................................. 57
代码约定.................................................................................... 60
第 1章 开发成功的Oracle应用程序...................................................... 61
1.1 我的方法................................................................................ 63
3 / 976
1.2 黑盒方法................................................................................ 65
1.3 开发数据库应用的正确（和不正确）方法.......................................... 70
1.3.1 了解Oracle体系结构............................................................ 70
1.3.2 理解并发控制...................................................................... 78
1.3.3 多版本.............................................................................. 84
1.3.4 数据库独立性? .................................................................... 93
1.3.5 “怎么能让应用运行得更快?”................................................114
1.3.6 DBA与开发人员的关系.........................................................119
1.4 小结....................................................................................121
第 2章体系结构概述........................................................................123
2.1 定义数据库和实例....................................................................125
2.2 SGA和后台进程......................................................................133
2.3 连接Oracle ...........................................................................136
2.3.1 专用服务器.......................................................................137
2.3.2 共享服务器.......................................................................138
2.3.3 TCP/IP连接的基本原理........................................................140
2.4 小结....................................................................................143
第 3章文件..................................................................................144
3.1 参数文件...............................................................................146
4 / 976
3.1.1 什么是参数？.....................................................................147
3.1.2 遗留的init.ora 参数文件........................................................150
3.1.3 服务器参数文件..................................................................152
3.1.4 参数文件小结.....................................................................163
3.2 跟踪文件...............................................................................163
3.2.1 请求的跟踪文件..................................................................165
3.2.2 针对内部错误生成的跟踪文件..................................................170
3.2.3 跟踪文件小结.....................................................................174
3.3 警告文件...............................................................................175
3.4 数据文件...............................................................................179
3.4.1 简要回顾文件系统机制..........................................................180
3.4.2 Oracle数据库中的存储层次体系..............................................181
3.4.3 字典管理和本地管理的表空间..................................................186
3.5 临时文件...............................................................................188
3.6 控制文件...............................................................................192
3.7 重做日志文件..........................................................................192
3.7.1 在线重做日志.....................................................................193
3.7.2 归档重做日志.....................................................................196
3.8 密码文件...............................................................................198
5 / 976
3.9 修改跟踪文件..........................................................................203
3.10 闪回日志文件........................................................................205
3.10.1 闪回数据库......................................................................205
3.10.2 闪回恢复区......................................................................206
3.11 DMP文件（EXP/IMP文件） .....................................................207
3.12 数据泵文件...........................................................................210
3.13 平面文件.............................................................................214
3.14 小结...................................................................................215
第 4章内存结构............................................................................215
4.1 进程全局区和用户全局区............................................................216
4.1.1 手动PGA内存管理..............................................................217
4.1.2 自动PGA内存管理..............................................................227
4.1.3 手动和自动内存管理的选择....................................................244
4.1.4 PGA和UGA小结...............................................................246
4.2 系统全局区............................................................................246
4.2.1 固定SGA .........................................................................254
4.2.2 重做缓冲区.......................................................................254
4.2.3 块缓冲区缓存.....................................................................256
4.2.4 共享池.............................................................................266
6 / 976
4.2.5 大池...............................................................................269
4.2.6 Java池............................................................................271
4.2.7 流池...............................................................................272
4.2.8 自动SGA内存管理..............................................................272
4.3 小结....................................................................................274
第 5章Oracle进程.........................................................................275
5.1 服务器进程............................................................................276
5.1.1 专用服务器连接..................................................................277
5.1.2 共享服务器连接..................................................................279
5.1.3 连接与会话.......................................................................280
5.1.4 专用服务器与共享服务器.......................................................289
5.1.5 专用/共享服务器小结...........................................................293
5.2 后台进程...............................................................................294
5.2.1 中心后台进程.....................................................................295
5.2.2 工具后台进程.....................................................................234
5.3 从属进程...............................................................................236
5.3.1 I/O从属进程.....................................................................236
5.3.2 并行查询从属进程...............................................................237
5.4 小结....................................................................................237
7 / 976
第 6章 锁.....................................................................................238
6.1 什么是锁？............................................................................238
6.2 锁定问题...............................................................................240
6.2.1 丢失更新..........................................................................240
6.2.2 悲观锁定..........................................................................241
6.2.3 乐观锁定..........................................................................243
6.2.4 乐观锁定还是悲观锁定？.......................................................256
6.2.5 阻塞...............................................................................257
6.2.6 死锁...............................................................................260
6.2.7 锁升级.............................................................................266
6.3 锁类型..................................................................................266
6.3.1 DML锁...........................................................................267
6.3.2 DDL锁............................................................................276
6.3.3 闩..................................................................................280
6.3.4 手动锁定和用户定义锁..........................................................290
6.4 小结....................................................................................291
第 7章 并发与多版本.......................................................................292
7.1 什么是并发控制？....................................................................292
7.2 事务隔离级别..........................................................................293
8 / 976
7.2.1 READ UNCOMMITTED........................................................294
7.2.2 READ COMMITTED............................................................296
7.2.3 REPEATABLE READ ............................................................297
7.2.4 SEAIALIZABLE...................................................................299
7.2.5 READ ONLY .....................................................................302
7.3 多版本读一致性的含义...............................................................302
7.3.1 一种会失败的常用数据仓库技术...............................................303
7.3.2 解释热表上超出期望的I/O .....................................................304
7.4 写一致性...............................................................................307
7.4.1 一致读和当前读..................................................................307
7.4.2 查看重启动.......................................................................311
7.4.3 为什么重启动对我们很重要？..................................................314
7.5 小结....................................................................................315
第 8章 事务..................................................................................317
8.1 事务控制语句..........................................................................317
8.2 原子性..................................................................................318
8.2.1 语句级原子性.....................................................................318
8.2.2 过程级原子性.....................................................................321
8.2.3 事务级原子性.....................................................................325
9 / 976
8.3 完整性约束和事务....................................................................325
8.3.1 IMMEDIATE 约束...............................................................325
8.3.2 DEFERRABLE 约束和级联更新.................................................326
8.4 不好的事务习惯.......................................................................329
8.4.1 在循环中提交.....................................................................329
8.4.2 使用自动提交.....................................................................337
8.5 分布式事务............................................................................338
8.6 自治事务...............................................................................340
8.6.1 自治事务如果工作？ ............................................................340
8.6.2 何时使用自治事务？ ............................................................343
8.7 小结....................................................................................348
第 9章 redo与undo ......................................................................349
9.1 什么是redo？ ........................................................................349
9.2 什么是undo？ .......................................................................350
9.2.1 redo和undo如何协作？......................................................353
9.3 提交和回滚处理.......................................................................357
9.3.1 COMMIT 做什么？..............................................................357
9.3.2 ROLLBACK做什么？ ...........................................................365
9.4 分析redo..............................................................................366
10 / 976
9.4.1 测量redo.........................................................................367
9.4.2 redo生成和BEFORE/AFTER触发器.........................................369
9.4.3 我能关掉重做日志生成吗？ ....................................................378
9.4.4 为什么不能分配一个新日志？..................................................383
9.4.5 块清除.............................................................................384
9.4.6 日志竞争..........................................................................388
9.4.7 临时表和redo/undo ...........................................................390
9.5 分析undo.............................................................................394
9.5.1 什么操作会生成最多和最少的undo？........................................394
9.5.2 ORA-01555:snapshot too old 错误.........................................397
9.6 小结....................................................................................409
第 10 章 数据库表...........................................................................411
10.1 表类型................................................................................411
10.2 术语...................................................................................412
10.2.1 段................................................................................413
10.2.2 段空间管理......................................................................414
10.2.3 高水位线........................................................................415
10.2.4 freelists .........................................................................417
10.2.5 PCTFREE 和PCTUSED........................................................421
11 / 976
10.2.6 LOGGING和NOLOGGING .................................................423
10.2.7 INITRANS 和MAXTRANS ...................................................424
10.3 堆组织表.............................................................................424
10.4 索引组织表...........................................................................427
10.5 索引聚簇表...........................................................................445
10.6 散列聚簇表...........................................................................455
10.7 有序散列聚簇表.....................................................................465
10.8 嵌套表................................................................................469
10.8.1 嵌套表语法......................................................................469
10.8.2 嵌套表存储......................................................................478
10.8.3 嵌套表小结......................................................................482
10.9 临时表................................................................................483
10.10对象表..............................................................................491
10.11小结.................................................................................500
第 11 章 索引................................................................................502
11.1 Oracle索引概述....................................................................502
11.2 B*树索引.............................................................................503
11.2.1 索引键压缩......................................................................506
11.2.2 反向键索引......................................................................509
12 / 976
11.2.3 降序索引........................................................................517
11.2.4 什么情况下应该使用B*树索引？.............................................519
11.2.5 B*树小结........................................................................532
11.3 位图索引.............................................................................532
11.3.1 什么情况下应该使用位图索引？ .............................................533
11.3.2 位图联结索引...................................................................538
11.3.3 位图索引小结...................................................................541
11.4 基于函数的索引.....................................................................542
11.4.1 重要的实现细节................................................................542
11.4.2 一个简单的基于函数的索引例子.............................................543
11.4.3 只对部分行建立索引...........................................................554
11.4.4 实现有选择的惟一性...........................................................556
11.4.5 关于CASE 的警告..............................................................557
11.4.6 关于ORA-01743的警告.....................................................559
11.4.7 基于函数的索引小结...........................................................560
11.5 应用域索引...........................................................................560
11.6 关于索引的常见问题和神话........................................................562
11.6.1 视图能使用索引吗？...........................................................562
11.6.2 Null和索引能协作吗？........................................................562
13 / 976
11.6.3 外键是否应该加索引？ ........................................................566
11.6.4 为什么没有使用我的索引？...................................................567
11.6.5 神话：索引中从不重用空间...................................................576
11.6.6 神话：最有差别的元素应该在最前面........................................581
11.7 小结...................................................................................585
第 12 章 数据类型...........................................................................587
12.1 Oracle数据类型概述...............................................................587
12.2 字符和二进制串类型................................................................589
12.2.1 NLS 概述........................................................................589
12.2.2 字符串...........................................................................593
12.3 二进制串：RAW类型..............................................................601
12.4 数值类型.............................................................................604
12.4.1 NUMBER类型的语法和用法.................................................607
12.4.2 BINARY_FLOAT/BINARY_DOUBLE 类型的语法和用法..................612
12.4.3 非固有数据类型................................................................612
12.4.4 性能考虑........................................................................613
12.5 LONG 类型..........................................................................615
12.5.1LONG和LONG RAW 类型的限制...........................................615
12.5.2 处理遗留的LONG类型.......................................................616
14 / 976
12.6 DATE、TIMESTAMP和INTERVAL类型........................................625
12.6.1 格式..............................................................................625
12.6.2 DATE 类型......................................................................626
12.6.3 TIMESTAMP类型.............................................................637
12.6.4 INTERVAL类型................................................................647
12.7 LOB 类型.............................................................................651
12.7.1 内部LOB........................................................................652
12.7.2 BFILE ............................................................................666
12.8 ROWID/UROWID 类型............................................................666
12.9 小结...................................................................................666
第 13 章 分区................................................................................666
13.1 分区概述.............................................................................666
13.1.1 提高可用性......................................................................666
13.1.2 减少管理负担...................................................................666
13.1.3 改善语句性能...................................................................666
13.2 表分区机制...........................................................................666
13.2.1 区间分区........................................................................666
13.2.2 散列分区........................................................................666
13.2.3 列表分区........................................................................666
15 / 976
13.2.4 组合分区........................................................................666
13.2.5 行移动...........................................................................666
13.2.6 表分区机制小结................................................................666
13.3 索引分区.............................................................................666
13.3.1 局部索引........................................................................666
13.3.2 全局索引........................................................................666
13.4 再论分区和性能.....................................................................666
13.5 审计和段空间压缩...................................................................666
13.6 小结...................................................................................666
第 14 章 并行执行...........................................................................666
14.1 何时使用并行执行...................................................................666
14.2 并行查询.............................................................................666
14.3 并行DML............................................................................666
14.4 并行DDL.............................................................................666
14.4.1 并行DDL和使用外部表的数据加载.........................................666
14.4.2 并行DDL和区段截断.........................................................666
14.5 并行恢复.............................................................................666
14.6 过程并行化...........................................................................666
14.6.1 并行管道函数...................................................................666
16 / 976
14.6.2 DIY 并行化......................................................................666
14.7 小结...................................................................................666
第 15 章 数据加载和卸载...................................................................666
15.1 SQL*Loader.........................................................................666
15.1.1 用SQLLDR加载数据的FAQ .................................................666
15.1.2 SQLLDR 警告...................................................................666
15.1.3 SQLLDR小结...................................................................666
15.2 外部表................................................................................666
15.2.1 建立外部表......................................................................666
15.2.2 处理错...........................................................................666
15.2.3 使用外部表加载不同的文件...................................................666
15.2.4 多用户问题......................................................................666
15.2.5 外部表小结......................................................................666
15.3 平面文件卸载........................................................................666
15.4 数据泵卸载...........................................................................666
15.5 小结...................................................................................666