oracle+

Oracle资料大集合

create table emp （no number(12), name char(20), …,constraint emp_x00 primary key(no)） storage（initial 100M next 100M pctincrease 0 maxextents 5000） pctused 70 pctfree 10 tablespace tbs_data enable primary key using index tablespace tbs_index; 
然而从简化数据对象配置、减少表空间碎片的角度考虑，不推荐为每张表单独指定storage选项，存储参数使用建于其上的表空间的缺省存储参数。不同表对扩展块大小的要求，可以通过分析归类，建立相应具有不同缺省存储参数的表空间的方法解决。这样数据库设计就能变得简洁明了。 
命令简化为： 
create table emp （no number(12), name char(20), …, constraint emp_x00 primary key(no)）pctused 70 pctfree 10 tablespace tbs_data enable primary key using index tablespace tbs_index; 
primary key关键字建立同名的primary key constraint和unique index，表的每个域都有自身的constraint。 
相关系统表： 
user_tables(tabs)，dba_tables #表属性 
user_tab_columns(cols),dba_tab_columns #表各列属性 
索引（index） 
create index emp_x01 on emp(name) storage(initial 10M next 10M pctincrease 0 maxextents 5000) pctfree 10 tablespace tbs_index; 
可参照表对storage的处理方式。 
create index emp_x01 on emp(name) pctfree 10 tablespace tbs_index; 
相关系统表： 
user_indexes(ind),dba_indexes #索引属性 
user_ind_columns,dba_ind_columns #索引各列属性，以index_position为顺序 
序列（sequence） 
create sequence emp_seq increment by 1 start with 1 nomaxvalue nocycle; 
相关系统表： 
user(dba)_sequences(seq) 序列属性 
视图（view） 
create emp_depart_view as select emp.name,emp_duty.name from emp,emp_duty where emp.duty=emp_duty.duty; 
相关系统表： 
user(dba)_views 视图属性 
Oracle将view,sequence，用户参数等定义均存放于系统表空间，而用户创建的表空间仅存放table,index实体，因此可以大胆删除用户表空间，再用备份重新恢复，不必担心view,sequence等会被一并删去。 
2.1.10 创建只读用户 
假定数据库用户dbbrsr需要对dbuser的表emp拥有select权力 
connect dbuser 
grant select on emp to dbbrsr 
connect dbbrsr 
create synonym emp for dbuser.emp; 
这样，dbbrsr就能象使用自己的表一样对dbuser的表执行select操作 
2.1.11 启动及关闭数据库实例 
oracle用户，dbstart和dbshut启动及关闭/var/opt/oracle/oratab或/etc/oratab中设定的数据库实例，dbstart采用normal方式，dbshut采用immediate方式。 
或者使用手工方式 
sqlplus "/ as sysdba" 
启动 
normal 
SQL>startup 
mount 
SQL>startup mount; #启动实例进程，载入数据库文件，允许DBA权限的某些操作，但禁止对数据库文件的一般性操作 
SQL>完成某些操作 
SQL>alter database open; 
nomount 
SQL>startup nomount; #启动实例进程，但不允许访问数据库，常用于创建数据库、介质恢复或创建controlfile 
SQL>完成某些操作 
SQL>alter database open; 
关闭 
normal 
SQL>shutdown或SQL>shutdown transactional; #等待每个连接交易完成后，切断连接，再关闭数据库 
immediate 
SQL>shutdown immediate; #立刻中止每个连接，交易回滚 
abort 
SQL>shutdown abort; #立刻关闭数据库，不保证交易完整性，在下一次启动打开数据库文件时会进行介质恢复 
2.1.12 网络配置 
假定某一台机器为client，ORACLE_SID为oraclient，数据库用户为dbclient；另一台机器为server，ORACLE_SID为oraserver，数据库用户为dbserver在server上$ORACLE_HOME/dbs/initoraserver.ora中有以下设定： 
db_name = oraserver 
instance_name = oraserver 
Oracle 8i 
service_names=oraserver 
2.1.12.1 TNS 
Client端配置 
修改$ORACLE_HOME/network/admin/tnsnames.ora，增加一条PROTOCOL=TCP的记录。 
Oracle8 
db_server 
(DESCRIPTION = 
(ADDRESS = (PROTOCOL= TCP)(Host= server)(Port= 1521)) 
(CONNECT_DATA = (SID = oraserver)) 
) 
Oracle8i 
db_server 
(DESCRIPTION = 
(ADDRESS = (PROTOCOL= TCP)(HOST= server)(PORT= 1521)) 
(CONNECT_DATA = (SERVICE_NAME=oraserver 
) 
HOST可在/etc/hosts或DNS中配置，或直接写上IP地址 
sqlplus dbserver/passwd@db_server 
Server端配置 
修改$ORACLE_HOME/network/admin/listener.ora 
在LISTENER中增加ADDRESS的记录 
LISTENER = 
(DESCRIPTION_LIST = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) 
(ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521)) 
) 
) 
) 
在SID_LIST_LISTENER中增加SID_DESC记录 
SID_LIST_LISTENER = 
(SID_LIST = 
(SID_DESC = 
(SID_NAME = PLSExtProc) 
(ORACLE_HOME = /opt/oracle/app/oracle/product/8.1.5) 
(PROGRAM = extproc) 
) 
(SID_DESC = 
(GLOBAL_DBNAME = oraserver) 
(ORACLE_HOME = /opt/oracle/app/oracle/product/8.1.7) 
(SID_NAME = oraserver) 
) 
) 
HOST可在/etc/hosts或DNS中配置，或直接写上IP地址 
注意：LISTENER和SID_LIST_LISTENER是成对出现的，可配置多个监听服务进程和相应的SID_LIST，如LISTENER_1和SID_LIST_LISTENER_1 
2.1.12.2 DB Link 
如果client的instance需要在访问本地数据对象同时访问server中的数据对象，可在instance中创建对server的数据库连接，实现间接访问 
在tnsnames.ora中建立"db_server"配置 
sqlplus dbclient/passwd1 
SQL>create database link server_link connect to dbserver identified by passwd using 'db_server'; 
使用emp@server_link访问server上的emp，如同访问本地instance中的数据对象一样。为了更方便的使用，可建立synonym 
2.2 Oracle 9i 
Oracle 9i相较于Oracle 8&8i，在兼容Oracle 8&8i的基础上，回滚和临时表空间配置发生比较大的变化，导致建库操作出现一些不同。在数据库配置文件initoradb.ora中有关于回滚表空间的选项，详细情况在"数据库配置"中解释。而且Oracle 9i简化了表空间的创建。所以此小节主要描述Oracle 9i相对于Oracle 8i的差异，其它相同的操作可参考Oracle 8i。 
2.2.1 手工创建 
由于在Oracle 9i中工具dbassist的使用方法与在Oracle 8i中类似，因此工具建库过程省略，只记录手工建库过程 
Oracle 9i中的建库过程已经变得极为简洁，大致如下： 
create database ${ORACLE_SID} 
user sys identified by sys 
user system identified by system 
logfile group 1 ('${ORACLE_BASE}/oradata/${ORACLE_SID}/redo01.log') size 10M, 
group 2 ('${ORACLE_BASE}/oradata/${ORACLE_SID}/redo02.log') size 10M, 
group 3 ('${ORACLE_BASE}/oradata/${ORACLE_SID}/redo03.log') size 10M 
maxlogfiles 5 
maxlogmembers 5 
maxloghistory 1 
maxdatafiles 254 
maxinstances 1 
archivelog 
character set ZHS32GB18030 
national character set AL16UTF16 
datafile '${ORACLE_BASE}/oradata/${ORACLE_SID}/system01.dbf' size 300M 
default temporary tablespace tbstemp tempfile '${ORACLE_BASE}/oradata/${ORACLE_SID}/temp01.dbf' size 500M 
undo tablespace tbsundo datafile '${ORACLE_BASE}/oradata/${ORACLE_SID}/undo01.dbf' size 500M; 
其特点为使用专用的回滚和临时表空间，而不象Oracle 8i中的那样，回滚和临时表空间与普通表空间没有差异，这样既简化了配置也有利于效能提高。要注意临时表空间的指定文件关键字是tempfile而不是通用的datafile，而且临时表空间的存储选项必须为uniform，由Oracle系统决定。同样回滚表空间也是由Oracle系统决定。不必人工干预。 
Oracle 9i在$ORACLE_HOME/dbs下可使用二进制配置文件，缺省为spfile{实例名}.ora，如spfileoradb.ora，支持Oracle系统进程在不重启的情况下动态调整参数，这对要求不间断运行的系统是有利的。在建库阶段就可将此配置文件创建起来。 
create spfile from pfile= '${ORACLE_BASE}/admin/${ORACLE_SID}/init${ORACLE_SID}.ora 
完整步骤见/9i/createdb.sh，编辑如pfile,数据文件目录等参数，以符合实际情况，并转移到$ORACLE_BASE/admin/oradb/create/下，执行。 
2.2.2 创建用户表空间 
Oracle 9i对于表空间管理一个明显的变化是改数据字典管理（extent management dictionary）为表空间本地管理（extent management local），还可以根据建立的数据对象对空间的要求自动确定扩展块的大小（autoallocate），最小为64K，这两项都是创建表空间的缺省选项。 
create tablespace tbsdata datafile '…' [ extent management local ] [ autoallocate ]; 
而对于指定每个扩展块大小的创建策略，设立了新选项：统一扩展块大小（uniform [size xxx[K|M]]），可覆盖autoallocate选项，如果不加上具体的size xxx[K|M]，缺省为1M，这样就不必考虑Oracle 8i中的如initial,next,pctincrease,maxextents等default storage参数应如何组合，事实上Oracle 8i的这些设置原本就没有什么意义。 
不能够同时指定extent management local和default storage，换言之，default storage只能和extent management dictionary一起显式指定。 
如果未指定extent management的类型，Oracle 9i缺省使用local方式，如果又同时使用default storage选项，就有以下的判断： 
如果使用minimun extent，Oracle检查是否minumum extent=initial=next且pctincrease＝0,如是，Oracle使用uniform选项，size=initial；如不是，Oracle忽略指定选项，使用autoallocate。 
如果未指定minimum extent，Oracle检查是否initial=next且pctincrease＝0,如是Oracle使用uniform选项，size=initial；如不是Oracle忽略指定选项，使用autoallocate。 
为了避免与Oracle 8i的习惯做法混淆，建议只使用Oracle 9i较简洁的方法。 
对于存储少量静态数据的表空间来说，如配置信息等，可简单地写为： 
create tablespace tbsdata datafile '…'; 
对于必须关心其扩展块大小的表空间，如大批量的记录或索引，可简单地写为： 
create tablespace tbsdata datafile '…' uniform size 10M; 

3 初始化文件配置 
所有参见内容都在附件01_install_02_create_03_init/下。 
描述initoradb.ora中各选项。 
3.1 Oracle 8 & 8i 
具体参见8i/initoradb.ora。 
db_block_size 
数据库基本数据块尺寸，字节为单位。 
当涉及到大量数据交换时，例如export/import操作时，此参数对数据库性能有非常大的影响，设定一个较大的值，有利于提高数据吞吐量，但由于db block是文件和内存之间交换的基本单位，过大的值反而会交换不需要的记录，增加额外的I/O。 
一般取8k就已能获得较满意效果。 
db_block_buffers 
数据缓冲区，db_block_size为单位，不超过1/4内存 
计算查询缓冲命中率： 
SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets', 'physical reads'); 
Hit Ratio = 1 - ( physical reads / (db block gets + consistent gets) ) 
SELECT name, phyrds, phywrts FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file# 
db block gets:在内存buffer中的命中次数 
consistent gets:一致性命中次数，指在内存buffer中未命中，但从回滚段或数据文件中获得命中 
physical reads:在数据文件中的读次数 
注意：一般HitRatio达到90%以上就可以认为已达到优化，这个数值应在系统运行稳定后进行统计。 
shared_pool_size 
数据字典和SQL操作缓冲区，字节为单位，不超过1/4内存 
select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache; 
select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache" from v$rowcache; 
select * from v$sgastat where name = 'free memory' 
注意：Cache命中率达到95%以上就可以认为已达到优化，这个数值应在系统运行稳定后进行统计 
log_checkpoint_interval 
日志提交点数据量间隔 
以操作系统block(通常512-byte)为单位，当日志累计至此参数，会使sga中dirty buffer被同步至数据文件，日志切换时也会引起此操作，如设为0，则相当于无限大，此参数失去作用，日志提交仅依靠日志文件的切换。 
应选择适当大小的日志文件，同时使log_checkpoint_interval略大于日志文件或设为0。原则上应该避免过于频繁的checkpoint操作，控制在30分钟以上为好。 
推荐此参数设为0。 
log_buffer 
在线日志缓冲，字节为单位，512K或128K*CPU数量，取较大值 
processes和sessions 
dedicated server模式下每一个连接都有一个Oracle服务进程（process）为之服务，这个连接本身也就是一个会话（session）。 
shared server模式下所有连接共享一个Oracle服务进程池，这样process和session就不再是一一对应，sessions要大于processes。 
sort_area_size和sort_area_retained_size 
排序缓冲区，字节为单位。 
当排序记录被全部取走后，缓冲区缩减到sort_area_retained_size，为减少缓冲区缩放的开销，可使sort_area_size和sort_area_retained_size取相同值。 
hash_area_size 
hash join缓冲区，字节为单位，缺省为2*sort_area_size。 
db_file_multiblock_read_count 
每次读取的db block数，对大规模查询性能有提高，特别是表扫描效率。在线系统应避免这种类型的查询。 
db_writer_processes 
同步数据进程数，与checkpoint的频率和数据量有关。 
db_block_lru_latches 
LRU锁集，一般设为CPU数目。RedHat Linux 6.x下的Oracle 8.1.6设此参数会导致系统挂起，疑对smp支持有问题 
log_archive_start 
系统启动时是否同时启动归档进程（archive）。 
log_archive_dest_1 
归档日志目录，最后的标号表明归档线程编号，一般只用1。 
log_archive_format 
归档日志名称，%t指归档线程编号， %s指归档日志序列号 
rollback_segments 
如果创建回滚段（rollback segment）时不使用public选项，那就是使用私有的回滚段，这样就必须在系统启动时激活。 
推荐使用public rollback segment的做法，这个选项可以废弃。 
background_dump_dest 
Oracle系统进程记录log和trc目录。 
alert_{实例名}.log以文本方式记录系统启动、关闭、出错、存储变化、日志切换等log信息。 
系统进程以各自名称和进程号记录错误信息，文件以trc为后缀，文本格式。 
core_dump_dest 
Oracle服务进程的core dump目录。 
user_dump_dest 
Oracle服务进程以各自名称和进程号记录错误信息，文件以trc为后缀，文本格式。 
3.2 Oracle 9i 
参见9i/initoradb.ora。 
pga_aggregate_target 
以K、M、G为单位 
sort, group-by, hash-join, bitmap merge, bitmap create等对内存有一定需求的SQL操作，都由此选项统一动态分配内存区域大小，因此Oracle 8i中如sort_area_size,sort_area_retained_size,hash_area_size，bitmap_merge_area_size等选项可以废弃。 
db_cache_size 
数据缓冲区，以K、M、G为单位，自动对齐到粒度单位。 
取代Oracle 8i的db_block_buffers选项。 
undo_management 
回滚空间管理模式，缺省为manual，使用回滚段（rollback segment），如设为auto，则使用Oracle 9i的回滚表空间。此选项决定了以下关于undo的其它选项。 
undo_retention 
已提交数据在回滚表空间中保留时间，以秒为单位，缺省900。 
当某些较长时间的查询需要通过回滚数据重建老数据块的时候，此选项可使新事务尽可能使用空闲的回滚表空间，这样就减少了查询过程因snapshot too old而失败的几率。 
然而当空闲回滚表空间不足以应付新事务时，系统仍然会重用此选项保留的空间，因此不能保证长查询一定能成功执行完毕。 
undo_tablespace 
指定系统启动时的回滚表空间。 

4 工具 
所有参见内容都在附件04_tool/下。 
在《优化》一节中讨论以下工具使用的效率。 
4.1 sqlldr 
参见sqlldr/。 
用于将格式化的文本数据上载到表中去 
以表emp为例 
首先编写一个控制命令的脚本文件，通常以ctl结尾，内容如下： 
emp.ctl 
load data 
append 
into table emp 
fields terminated by '|' 
( 
no float external, 
name char(20), 
age integer external, 
duty char(1), 
salary float external, 
upd_ts date(14) 'YYYYMMDDHH24MISS' 
) 
括号里对数据文件里每个数据域进行解释，以此在上载时与目标表进行比对。 
除了append外，还有insert、replace、truncate等方式，与append大同小异，不作更多的解释。 
再将上载数据组织成数据文件，通常以dat结尾，内容如下： 
emp.dat 
100000000001|Tom|000020|1|000000005000|20020101000000 
100000000002|Jerry|000025|2|000000008000|20020101235959