Oracle9i数据库设计指引全集二
2.4.4 视图设计
视图是虚拟的数据库表，在使用时要遵循以下原则：
从一个或多个库表中查询部分数据项；
为简化查询，将复杂的检索或字查询通过视图实现；
提高数据的安全性，只将需要查看的数据信息显示给权限有限的人员；
视图中如果嵌套使用视图，级数不得超过3级；
由于视图中只能固定条件或没有条件，所以对于数据量较大或随时间的推移逐渐增多的库表，不宜使用视图；可以采用实体化视图代替。
除特殊需要，避免类似Select * from [TableName] 而没有检索条件的视图；
视图中尽量避免出现数据排序的SQL语句。
2.4.5 包设计
存储过程、函数、外部游标必须在指定的数据包对象PACKAGE中实现。存储过程、函数的建立如同其它语言形式的编程过程，适合采用模块化设计方法；当具体算法改变时，只需要修改需要存储过程即可，不需要修改其它语言的源程序。当和数据库频繁交换数据是通过存储过程可以提高运行速度，由于只有被授权的用户才能执行存储过程，所以存储过程有利于提高系统的安全性。
存储过程、函数必须检索数据库表记录或数据库其他对象，甚至修改（执行Insert、Delete、Update、Drop、Create等操作）数据库信息。如果某项功能不需要和数据库打交道，则不得通过数据库存储过程或函数的方式实现。在函数中避免采用DML或DDL语句。
在数据包采用存储过程、函数重载的方法，简化数据包设计，提高代码效率。存储过程、函数必须有相应的出错处理功能。
2.4.6 安全性设计
4.4.6.1 管理默认用户
在生产环境中，必须严格管理sys和system用户，必须修改其默认密码，禁止用该用户建立数据库应用对象。删除或锁定数据库测试用户scott 。
2.4.6.2 数据库级用户权限设计
必须按照应用需求，设计不同的用户访问权限。包括应用系统管理用户，普通用户等，按照业务需求建立不同的应用角色。
用户访问另外的用户对象时，应该通过创建同义词对象synonym进行访问。
2.4.6.3 角色与权限
确定每个角色对数据库表的操作权限，如创建、检索、更新、删除等。每个角色拥有刚好能够完成任务的权限，不多也不少。在应用时再为用户分配角色，则每个用户的权限等于他所兼角色的权限之和。
2.4.6.4 应用级用户设计
应用级的用户帐号密码不能与数据库相同，防止用户直接操作数据库。用户只能用帐号登陆到应用软件，通过应用软件访问数据库，而没有其它途径操作数据库。
2.4.6.5 用户密码管理
用户帐号的密码必须进行加密处理，确保在任何地方的查询都不会出现密码的明文。
2.5 SQL编写
2.5.1 字符类型数据
SQL中的字符类型数据应该统一使用单引号。特别对纯数字的字串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。利用trim(),lower()等函数格式化匹配条件。
2.5.2 复杂sql
对于非常复杂的sql(特别是有多层嵌套，带子句或相关查询的)，应该先考虑是否设计不当引起的。对于一些复杂SQL可以考虑使用程序实现。
USER_TAB_COMMENTS 数据字典
Comment on 可加注解
2.5.3 高效性
2.5.3.1 避免In子句
使用In 或 not In子句时，特别是当子句中有多个值时，且查询数据表数据较多时，速度会明显下降。可以采用连接查询或外连接查询来提高性能。
Char 比 varchar 查询时高询
在进行查询及建立索引时，char比varchar的效率要高，当然varchar在存储上比char要好
2.5.3.2 避免嵌套的Select子句
这个实际上是In子句的特例。
2.5.3.3 避免使用Select * 语句
如果不是必要取出所有数据，不要用*来代替，应给出字段列表，注：不含select count(*)。
2.5.3.4 避免不必要的排序
不必要的数据排序大大的降低系统性能。
2.5.4 健壮性
2.5.4.1 Insert语句
使用Insert语句一定要给出要插入值的字段列表，这样即使更改了表结构加了字段也不会影响现有系统的运行。
2.5.4.2 Count(*)、Count(*)、count(distinct id)的区别
Select count(*) from testtab
得到表testtab的记录数
select count(id) from testtab
得到表testtab id字段非空记录数
select count(distinct id) from testtab
得到表testtab id字段值非相同记录数
2.5.4.3 Not null 为字段类型性质的约束
本约束功能在后期无语法使期失效，可使用修改字段类型方式
alter table modify 字段名 类型 not null
alter table modify 字段名 类型
2.5.4.4 外键值可用null的问题
外键列如没有明确说明not null，可插入null记录（而null是在外部表的记录中没有的），如无可插null记录的想法，要对外键字段加not null约束。
2.5.4.5 序列 sequence 跳号的问题
sequence 因回滚，系统崩溃（使用cache 内的值将认为已用），多表引用都将使其跳号，所以不能用于为连续序号 utl_row.cast_to_row
2.5.4.6 unicn\ intersect\ minus 使用ordey by的注意事项
以上语句进行连表操作，而表同表的字段顺序的类型相同但字段标题名可不同，使用ordey by时后面如果是字段名，要求所有的表的字段标题名相同，否则用字段的顺序号
select id,name,year from user1
union
select no,name,to_number(null)
year
from user2
order by 1,name,year
2.5.5 安全性
2.5.5.1 Where 条件
无论在使用Select,还是使用破坏力极大的Update和Delete语句时，一定要检查Where条件判断的完整性，不要在运行时出现数据的重大丢失。如果不确定，最好先用Select语句带上相同条件来果一下结果集，来检验条件是否正确。
2.5.6 完整性
有依赖关系的表，例如主外键关系表，在删除父表时必须级联删除其子表相应数据，或则按照某种业务规则转移该数据。9I中表中字段缩小及变类型，字段为空或表空，varchar和char长度不变可任意改，字段名和表名可字段可用 ALTER TABLE table SET UNUSED (column) 设定为不可用，注意无命令再设为可用
3 备份恢复设计原则
3.1 数据库exp/imp备份恢复
Oracle数据库的Exp、Imp提供了数据快速的备份和恢复手段，提供了数据库级、用户级和表级的数据备份恢复方式。这种方法一般作为数据库辅助备份手段。
3.1.1 数据库级备份原则
在数据库的数据量比较小，或数据库初始建立的情况下采用。不适合7*24的在线生产环境数据库备份。
3.1.2 用户级备份原则
在用户对象表数据容量比较小、或则用户对象初始建立的情况下使用。
3.1.3 表级备份原则
主要在以下场合采用的备份方式：
参数表备份
静态表备份
分区表的分区备份。
3.2 数据库冷备份原则
数据库冷备份必须符合以下原则：
数据库容量比较小。
数据库允许关闭的情况。
3.3 Rman备份恢复原则
这种方式适用于7*24环境下的联机热备份情形。
3.3.1 Catalog数据库
单独建立备份恢复用的数据库实例，尽可能与生产环境的数据库分开，确保catalog与生产数据库的网络连接良好。在9I系统使用良好的备份策略以可，支持完全使用控制文件保存catalog信息，备份策略如下：
backup spfile format '/data/backup/%d_SPFILE_%T_%s_%p.bak';
sql "alter system archive log current";
backup archivelog all format '/data/backup/%d_ARC_%T_%s_%p.bak' delete all input;
backup current controlfile format '/data/backup/%d_CTL_%T_%s_%p.bak';
在spfile、控制文件、数据库全丢的情况下可通过下面的方式恢复
RMAN connect target
connected to target database (not started)
RMAN startup
RMAN
restore spfile from '/data/backup/COMMDB_SPFILE_20030411_9_1.bak';
SQL startup
ORA-00205: error in identifying controlfile, check alert log for more info
RMAN restore controlfile from 'd:\DB92_CTL_20031113_9_1.BAK';
Mout database:
RMAN recover database;
RMAN alter database open resetlogs;
注意：对数据库设定控制文件保存备份信息为365天，具体语句如下。
alter system set control_file_record_keep_time=365 SCOPE=BOTH;
3.3.2 Archive Log
设置Archive Log 的位置，确保存储介质有足够的空间来保留指定时间内archive log的总量。建设定期对RMAN进行全备份，删除冗余归档日志文件。
3.3.3 全备份策略
对于小容量数据库，可以采用全备份策略。对于大容量数据库，必须制定全备份策略方案，备份时对archive log进行转储，同时冷备份catalog 数据库。
3.3.4 增量备份策略
对于大容量数据库，必须制定增量备份、累积备份和全备份的周期，备份时对archive log进行转储，同时冷备份catalog 数据库。
3.3.5 恢复原则
采用Rman脚本进行数据库恢复。数据库恢复有以下几种：
3.3.5.1 局部恢复
主要用于恢复表空间、数据文件，一般不影响数据库其他操作。
3.3.5.2 完全恢复
数据库恢复到故障点，由catalog当前数据库决定。
3.3.5.3 不完全恢复
恢复到数据库的某一时间点或备份点。
恢复catalog数据库。
恢复数据库control file 。
恢复到数据库某一时间点。
重设日志序列。
3.4 备用数据库原则
数据库系统在以下情况下可以考虑采用备用数据库data guard原则：
数据库容量适中。
数据库严格要求7*24不间断，或间断时间要求控制在最小范围内。
数据库要求有异地备份冗余。
3.5 一些小经验
使用oemc的oms时，首选项要求是节点和数据库分别加入系统用户（如：administrator）和数据库DBA用户(system)。节点的系统用户必须有批处理作业登录的权限
agent 不能启动，lisnter修改后都要手动删除oracle\ora9\network\agent 中的*.q文件
oracle\admin\my9i\bdump 中是用户的出错日志
改变表的空间的方式alter table hr.ssss move TABLESPACE example（要重建索引）; 或用imp导入时，设定导入用户只有某一表空间的使用权，无RESOURCE角色和UNLIMITED TABLESPACE权限
aleter system set log_checkpoint_to_alter=true，后可报警文件发现checkpoint的起动和结束时间。
3.6 系统调优知识
3.6.1.1 生成状态报表（statspack的使用）
使用（存放位置@?\rdbms\admin\）的文件生成报表用户
@?\rdbms\admin\Spcreate.sql建表
将timed_statistics设定true
使用生成的perfstat用户登录，执行以下语句手动收集信息
Exex statspack.snap
Exec statspack.snap(I_SNAP_LEVEL=0,I_MODEFY_PRAMETER=TRUE) 0级，最少10最大
使用下面的语句生成状态报表
@?\rdbms\admin\Spreport.sql
其他相关文件
delete stats$snapshot ;清原来记录数据
@?\rdbms\admin\Saputo.sql
select job from user_jobs 取用户作业号
exec dbms_remove(作业号)
timed_statistics=true要求
@?\rdbms\admin\spdrop.sql ;
3.6.1.2 sql追踪
设定全部用户跟踪
alter system set sql_trace=true;
用户级别跟踪
alter session set sql_trace=true;
用户的跟踪文件生成在 admin\{pid}\udump\{pid} _ora_{ SPID}.trc 中,spid从下面语句得到
SELECT b.name bkpr, s.username, p.spid,s.sid,s.serial# FROM v$bgprocess b, v$session s, v$process p WHERE p.addr = b.paddr(+) AND p.addr = s.paddr and s.username=user;
DBA对特定用户跟踪
exec dbms_system_set_Sql_trace_in_session(sid,serial#,true)
信息从下面得到
SELECT b.name bkpr, s.username, p.spid,s.sid,s.serial#,osuser,s.program
FROM v$bgprocess b, v$session s, v$process p
WHERE p.addr = b.paddr(+)
AND p.addr = s.paddr;
/*p.spid用于sql_trace时日志编号，dbms_system.set_sql_trace_in_session(sid,erial#,true)*/
用户的跟踪文件生成在 admin\{pid}\udump 中
系统的跟踪文件生成在admin\{pid}\bdump\alert_{pid}.log
tkprof.exe将log文件生成格式化文本
在av Rd(ms) 20以上说明表空间使用过用频繁，考虑将表分开其他表空间上
系统变量fast_start_mttr_target的值要大到不产生log等待，当然也可通过加log组使其不等待
reao log大小应为每30分钟切换一次
建议表空间的利用率不超80%
buffer hit 要达80%以上为好
3.6.1.3 内存调整
一般的内存分配原则
SGA 50%（其中80% DATA BUFFER，15% SHARE POOL，5其他）
PGA30%
OS 20%
例如：2G的WINDOWS的平台，OS 300M，SAG 1.2G,PGA 500M
内存分配的基本单位
ＳＧＡ《＝１２８Ｍ
４Ｍ
ＳＧＡ》１２８Ｍ
６４位系统１６Ｍ，３２Ｍ系统８Ｍ
动态分配时总值不可大于sga_max_size
通过V$SGA_DYNAMIC_FREE_MEMORY取空闲内存空间
在缩小时如果内存空间实际在应用中，ＣＰＵ利用率将达１００％，最后将语句出错。
V$SGASTAT
可看实际的使用情况
Redo log buffer一般在５Ｍ内，可通过v$sessuon_wait看是否等，v$sysstat
可也通过报警文件看是否等切换，方法可加组。可通过nologging（数据库也要设定支持nologging）方法减少日志文件产生量。
java_pool 没有设定时，使用shared_pool_size
3.6.1.3.1 shared_pool
本缓冲区用于sql语句，pl sql等的对象保存
Cursor_sharing{Exact|Similar|force} 游标共享设定
Force方式适用OLTP数据库，Exact方式适合数据仓库，similar为智能方式
hard parses 硬SQL语句分析，每秒要底于100次,小要加大shared_pool
soft parse 软SQL语句分析，OLTP要达90%以上，小要加大shared_pool
不建议用无命名PL SQL段
如果有大PL SQL(存储过程)对象可强制保存于内存，也可加大SHARED_POOL_RESERVED_SIZE，大小不可过SHARED_POOL_SIZE的50%，不然实例不能起动
3.6.1.3.2 db_cache
本缓冲区用于数据库数据对象保存
db_cache_advice 为on，可以提出通过企业管理器看到系统建议
通过select * from v$system_event 进行系统查看。
发现存在free buffer waits，说明不能将data buffer及时写入data file;
可通过增加加CPU后，加db_writer_processes=CPU数改善。
也可设disk_asynch_io为true,使用异步IO（前提同要操作系统支持）db_writer_processes=1时（只有一个CPU的情况下），也可通加大dbwr_io_slaves来改善。db_writer_processes1,不可用本功能
调整效果排序：异步IOCPUdbwr_io_slaves
Buffer Busy Waits大说明出现IO冲突
Buffer Busy Waits 大 和 dbbock大说明全表扫描多，说明数据不能读入，可加大
db_cache_size来改善.
Undo
block大要加大回滚段（手动管理方式，９Ｉ默认是自动管理）
undo header 大要加大回滚段（手动管理方式，９Ｉ默认是自动管理）
db_cache命中率99%，不是唯一因素，关系是不要出现等待。建议达90%以上。
内存使用建议：
系统可以设三个缓冲区，建表时可设定用那个缓冲区（默认在db_cache_size）
db_cache_size
（默认区）
db_keep_cache_size （常访问，小于db_keep_cache_size的10%的表可放于本区）
db_recycle_cache_size （一个事物完成后常时间不再使用，或两倍大小于缓冲区）
3.6.2 排序的优化
9I为专用服务器时系统变量workarea_size_policy 设定为auto, statistics_level设定为 TYPICAL 可获取v$pga_target_advice中的优化建议。参数pga_aggregate_target值为所有连接用户可用排序内存。
9I为共享服务器时workarea_size_policy设定为menaul, sort_area_size值为每用户排序内存。
如果内存不足将使用TEMP表空间进行排序,排序使用比率disk/meme应小于5%
尽量少用排序,如果使用排序功能，尽量在字段上加索引进行优化。
SQL分析模式:RBO(基于规则)方案小表(驱动表)放在最后,优先使用索引,对SQL语句要求严格(8I以前的模式);CBO （基于开销）根据统计值进行选择开销最少,性能最优的最佳方式进行,但本方式DBA(使用analyze table语句)要定期进行分析统计.系统设定通过optimizer_mode 系统参数
说明: 指定优化程序的行为。如果设置为 RULE, 就会使用基于规则的优化程序, 除非查询含有提示。如果设置为 CHOOSE, 就会使用基于成本的优化程序, 除非语句中的表不包含统计信息。ALL_ROWS 或 FIRST_ROWS
始终使用基于成本的优化程序。
值范围: RULE | CHOOSE | FIRST_ROWS | ALL_ROWS
默认值: CHOOSE
｛rule（RBO）|choose（自动选择）|fist_rows| fist_rows_n|all_row｝
3.6.3 统计信息
进行某表的统计分析
EXECUTE dbms_stats.gather_table_stats ('HR','EMPLOYEES');
查看结果
SELECT
num_rows, blocks, empty_blocks as empty,
avg_space, chain_cnt, avg_row_len
FROM
dba_tables
WHERE
owner = 'HR'
AND
table_name = 'EMPLOYEES';
4 设计工具
统一使用sybase power designer设计工具，在该工具上完成物理模型的设计。所有的数据库对象尽可能在物理模型上进行设计，而且每个物理模型都要有相应的文字描述。
所有的数据库对象变更以数据库物理模型为基准。为了避免字符敏感问题，产生的脚本以大写字母为标准。
