分类: Oracle
2008-05-24 19:09:54
来源: |
|
配置: Windows NT 4.0 中文版 5块10.2GB SCSI硬盘 分:C:盘、D:盘、E:盘、F:盘、G:盘 Oracle 8.0.4 for Windows NT NT安装在C:WINNT,Oracle安装在C:ORANT : 因系统的回滚段太小,现打算生成新的回滚段, 建立大的、新的表空间(数据表空间、索引表空间、回滚表空间、临时表空间、) 建两个数据表空间、两个索引表空间,这样建的目的是根据实际, 如:现有10个应用用户,每个用户是一个独立子系统(如:商业进销存MIS系统中的财务、收款、库存、人事、总经理等) 尤其大型商场中收款机众多,同时访问进程很多,经常达到50-100个进程同时访问, 这样,通过建立多个用户表空间、索引表空间,把各个用户分别建在不同的表空间里(多个用户表空间放在不同的物理磁盘上), 减少了用户之间的I/O竞争、读写数据与写读索引的竞争(用户表空间、索引表空间也分别放在不同的物理磁盘上) 规划: C:盘、NT系统,Oracle系统 D:盘、数据表空间1(3GB、自动)、回滚表空间1(1GB、自动扩展) E:盘、数据表空间2(3GB、自动扩展)、回滚表空间2(1GB、自动扩展) F:盘、索引表空间1(2GB、自动扩展)、临时表空间1(0.5GB、不自动扩展) G:盘、索引表空间2(2GB、自动扩展)、临时表空间2(0.5GB、不自动扩展) 注:这只是一个简单的规划,实际规划要依系统需求来定,尽量减少I/O竞争 实现: 1、首先查看系统有哪些回滚段及其状态。 SQL> col owner format a20 SQL> col status format a10 SQL> col segment_name format a20 SQL> col tablespace_name format a20 SQL> SELECT OWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM(BYTES)/1024/1024 M 2 FROM DBA_SEGMENTS 3 WHERE SEGMENT_TYPE='ROLLBACK' 4 GROUP BY OWNER,SEGMENT_NAME,TABLESPACE_NAME 5 / OWNER SEGMENT_NAME TABLESPACE_NAME M -------------------- -------------------- -------------------- --------- SYS RB1 ROLLBACK_DATA .09765625 SYS RB10 ROLLBACK_DATA .09765625 SYS RB11 ROLLBACK_DATA .09765625 SYS RB12 ROLLBACK_DATA .09765625 SYS RB13 ROLLBACK_DATA .09765625 SYS RB14 ROLLBACK_DATA .09765625 SYS RB15 ROLLBACK_DATA .09765625 SYS RB16 ROLLBACK_DATA .09765625 SYS RB2 ROLLBACK_DATA .09765625 SYS RB3 ROLLBACK_DATA .09765625 SYS RB4 ROLLBACK_DATA .09765625 SYS RB5 ROLLBACK_DATA .09765625 SYS RB6 ROLLBACK_DATA .09765625 SYS RB7 ROLLBACK_DATA .09765625 SYS RB8 ROLLBACK_DATA .09765625 SYS RB9 ROLLBACK_DATA .09765625 SYS RB_TEMP SYSTEM .24414063 SYS SYSTEM SYSTEM .1953125 查询到18记录. SQL> SELECT SEGMENT_NAME,OWNER, 2 TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS 3 FROM DBA_ROLLBACK_SEGS 4 / SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID STATUS -------------------- -------------------- -------------------- ---------- --------- ---------- SYSTEM SYS SYSTEM 0 1 ONLINE RB_TEMP SYS SYSTEM 1 1 OFFLINE RB1 PUBLIC ROLLBACK_DATA 2 3 ONLINE RB2 PUBLIC ROLLBACK_DATA 3 3 ONLINE RB3 PUBLIC ROLLBACK_DATA 4 3 ONLINE RB4 PUBLIC ROLLBACK_DATA 5 3 ONLINE RB5 PUBLIC ROLLBACK_DATA 6 3 ONLINE RB6 PUBLIC ROLLBACK_DATA 7 3 OFFLINE RB7 PUBLIC ROLLBACK_DATA 8 3 OFFLINE RB8 PUBLIC ROLLBACK_DATA 9 3 OFFLINE RB9 PUBLIC ROLLBACK_DATA 10 3 OFFLINE RB10 PUBLIC ROLLBACK_DATA 11 3 OFFLINE RB11 PUBLIC ROLLBACK_DATA 12 3 OFFLINE RB12 PUBLIC ROLLBACK_DATA 13 3 OFFLINE RB13 PUBLIC ROLLBACK_DATA 14 3 OFFLINE RB14 PUBLIC ROLLBACK_DATA 15 3 OFFLINE RB15 PUBLIC ROLLBACK_DATA 16 3 OFFLINE RB16 PUBLIC ROLLBACK_DATA 17 3 OFFLINE 查询到18记录. 2、修改代码如下,可把以下代码存入一.sql文件,如cg_sys.sql,然后以SQL> @cg_sys.sql调用执行。 --注意:各个硬盘上要事先建好oradata目录 --修改现有回滚段,使之失效,下线 alter rollback segment rb1 offline; alter rollback segment rb2 offline; alter rollback segment rb3 offline; alter rollback segment rb4 offline; alter rollback segment rb5 offline; alter rollback segment rb6 offline; alter rollback segment rb7 offline; alter rollback segment rb8 offline; alter rollback segment rb9 offline; alter rollback segment rb10 offline; alter rollback segment rb11 offline; alter rollback segment rb12 offline; alter rollback segment rb13 offline; alter rollback segment rb14 offline; alter rollback segment rb15 offline; alter rollback segment rb16 offline; --删除原有回滚段 drop rollback segment rb1; drop rollback segment rb2; drop rollback segment rb3; drop rollback segment rb4; drop rollback segment rb5; drop rollback segment rb6; drop rollback segment rb7; drop rollback segment rb8; drop rollback segment rb9; drop rollback segment rb10; drop rollback segment rb11; drop rollback segment rb12; drop rollback segment rb13; drop rollback segment rb14; drop rollback segment rb15; drop rollback segment rb16; --建数据表空间1 --收款、库存、订货、远程通信 create tablespace USER_DATA1 datafile 'd:oradatauser1_1.ora' size 512M, 'd:oradatauser1_2.ora' size 512M, 'd:oradatauser1_3.ora' size 512M, 'd:oradatauser1_4.ora' size 512M, 'd:oradatauser1_5.ora' size 512M, 'd:oradatauser1_6.ora' size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED default storage (initial 128K next 2M pctincrease 0); --initial 128K,因为,用户建在表空间上,而表建在用户里,为用户所拥有, --用户继承数据表空间的存储参数,表继承用户的存储参数 --如果initial设的过大,如:5M,则每建一个空表就要占用5M的空间,即使一条记录也没有 --AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,设置数据文件自动扩展,每一次扩展增加5M,最大空间不受限 --建数据表空间2 --物价、人事、结算、财务、总经理、合同、统计 create tablespace USER_DATA2 datafile 'e:oradatauser2_1.ora' size 512M, 'e:oradatauser2_2.ora' size 512M, 'e:oradatauser2_3.ora' size 512M, 'e:oradatauser2_4.ora' size 512M, 'e:oradatauser2_5.ora' size 512M, 'e:oradatauser2_6.ora' size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED default storage (initial 128K next 2M pctincrease 0); --建索引表空间1 create tablespace INDEX_DATA1 datafile 'f:oradataindex1_1.ora' size 512M, 'f:oradataindex1_2.ora' size 512M, 'f:oradataindex1_3.ora' size 512M, 'f:oradataindex1_4.ora' size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED default storage (initial 128K next 2M pctincrease 0); --建索引表空间2 create tablespace INDEX_DATA2 datafile 'g:oradataindex2_1.ora' size 512M, 'g:oradataindex2_2.ora' size 512M, 'g:oradataindex2_3.ora' size 512M, 'g:oradataindex2_4.ora' size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED default storage (initial 128K next 2M pctincrease 0); --建回滚表空间1 --设置初始值40M(initial 40M),则每在这个表空间中建一个回滚段, --此回滚段自动继承此回滚表空间的存储参数,也即默认文件为40M create tablespace ROLLBACK_DATA1 datafile 'd:oradataroll1_1.ora' size 512M, 'd:oradataroll1_2.ora' size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED default storage (initial 40M next 5M pctincrease 0); --建回滚表空间2 create tablespace ROLLBACK_DATA2 datafile 'e:oradataroll2_1.ora' size 512M, 'e:oradataroll2_2.ora' size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED default storage (initial 40M next 5M pctincrease 0); --建临时表空间1 create tablespace TEMPORARY_DATA1 datafile 'f:oradatatemp1_1.ora' size 512M default storage (initial 10M next 3M pctincrease 0); --建临时表空间2 create tablespace TEMPORARY_DATA2 datafile 'g:oradatatemp2_1.ora' size 512M default storage (initial 10M next 3M pctincrease 0); --使其真正成为临时的 alter tablespace TEMPORARY_DATA1 temporary; alter tablespace TEMPORARY_DATA2 temporary; --建立新的回滚段,每个都一样大,不同大小的回滚段没有什么意义,系统是随机选择的。 --建多少个,根据并发访问用户的多少, --如果你们每天有50-100个人员使用Oracle系统开发的管理软件,应该20个以上 create public rollback segment rb01 tablespace rollback_data1; create public rollback segment rb02 tablespace rollback_data1; create public rollback segment rb03 tablespace rollback_data1; create public rollback segment rb04 tablespace rollback_data1; create public rollback segment rb05 tablespace rollback_data1; create public rollback segment rb06 tablespace rollback_data1; create public rollback segment rb07 tablespace rollback_data1; create public rollback segment rb08 tablespace rollback_data1; create public rollback segment rb09 tablespace rollback_data2; create public rollback segment rb10 tablespace rollback_data2; --前8个建在回滚表空间1中,后8个在回滚表空间2 create public rollback segment rb11 tablespace rollback_data2; create public rollback segment rb12 tablespace rollback_data2; create public rollback segment rb13 tablespace rollback_data2; create public rollback segment rb14 tablespace rollback_data2; create public rollback segment rb15 tablespace rollback_data2; create public rollback segment rb16 tablespace rollback_data2; create public rollback segment rb17 tablespace rollback_data2; create public rollback segment rb18 tablespace rollback_data2; create public rollback segment rb19 tablespace rollback_data2; create public rollback segment rb20 tablespace rollback_data2; --使回滚段online,即有效 alter rollback segment rb01 online; alter rollback segment rb02 online; alter rollback segment rb03 online; alter rollback segment rb04 online; alter rollback segment rb05 online; alter rollback segment rb06 online; alter rollback segment rb07 online; alter rollback segment rb08 online; alter rollback segment rb09 online; alter rollback segment rb10 online; alter rollback segment rb11 online; alter rollback segment rb12 online; alter rollback segment rb13 online; alter rollback segment rb14 online; alter rollback segment rb15 online; alter rollback segment rb16 online; alter rollback segment rb17 online; alter rollback segment rb18 online; alter rollback segment rb19 online; alter rollback segment rb20 online; --查看现有回滚段及其状态 col segment format a30 SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS; --查看数据文件及其所在表空间、大小、状态 col file_name format a40 col tablespace_name format a20 select file_name,file_id,tablespace_name,bytes,status from dba_data_files; 至此,表空间重新规划完毕,这里讲的比较通俗,还有好多参数值得设置,能够把Oracle设置到最优的境界, 表空间设置完了,下面,就该好好的整理一下Oracle的内存区了, Oracle很有意思,内存越大,越明显,所以有必要好好调整一下SGA区,也就是主要配置ininorcl.ora参数文件。 |