oracle表空间规划示例

配置：Windows NT 4.0 中文版5块10.2GB SCSI硬盘分：C:盘、D:盘、E:盘、F:盘、G:盘Oracle 8.0.4 for Windows NTNT安装在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 a20SQL> col status format a10SQL> col segment_name format a20SQL> 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        .09765625SYS                  RB10                 ROLLBACK_DATA        .09765625SYS                  RB11                 ROLLBACK_DATA        .09765625SYS                  RB12                 ROLLBACK_DATA        .09765625SYS                  RB13                 ROLLBACK_DATA        .09765625SYS                  RB14                 ROLLBACK_DATA        .09765625SYS                  RB15                 ROLLBACK_DATA        .09765625SYS                  RB16                 ROLLBACK_DATA        .09765625SYS                  RB2                  ROLLBACK_DATA        .09765625SYS                  RB3                  ROLLBACK_DATA        .09765625SYS                  RB4                  ROLLBACK_DATA        .09765625SYS                  RB5                  ROLLBACK_DATA        .09765625SYS                  RB6                  ROLLBACK_DATA        .09765625SYS                  RB7                  ROLLBACK_DATA        .09765625SYS                  RB8                  ROLLBACK_DATA        .09765625SYS                  RB9                  ROLLBACK_DATA        .09765625SYS                  RB_TEMP              SYSTEM               .24414063SYS                  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 ONLINERB_TEMP              SYS                  SYSTEM                        1         1 OFFLINERB1                  PUBLIC               ROLLBACK_DATA                 2         3 ONLINERB2                  PUBLIC               ROLLBACK_DATA                 3         3 ONLINERB3                  PUBLIC               ROLLBACK_DATA                 4         3 ONLINERB4                  PUBLIC               ROLLBACK_DATA                 5         3 ONLINERB5                  PUBLIC               ROLLBACK_DATA                 6         3 ONLINERB6                  PUBLIC               ROLLBACK_DATA                 7         3 OFFLINERB7                  PUBLIC               ROLLBACK_DATA                 8         3 OFFLINERB8                  PUBLIC               ROLLBACK_DATA                 9         3 OFFLINERB9                  PUBLIC               ROLLBACK_DATA                10         3 OFFLINERB10                 PUBLIC               ROLLBACK_DATA                11         3 OFFLINERB11                 PUBLIC               ROLLBACK_DATA                12         3 OFFLINERB12                 PUBLIC               ROLLBACK_DATA                13         3 OFFLINERB13                 PUBLIC               ROLLBACK_DATA                14         3 OFFLINERB14                 PUBLIC               ROLLBACK_DATA                15         3 OFFLINERB15                 PUBLIC               ROLLBACK_DATA                16         3 OFFLINERB16                 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:/oradata/user1_1.ora' size 512M,'d:/oradata/user1_2.ora' size 512M,'d:/oradata/user1_3.ora' size 512M,'d:/oradata/user1_4.ora' size 512M,'d:/oradata/user1_5.ora' size 512M,'d:/oradata/user1_6.ora' size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault 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:/oradata/user2_1.ora' size 512M,'e:/oradata/user2_2.ora' size 512M,'e:/oradata/user2_3.ora' size 512M,'e:/oradata/user2_4.ora' size 512M,'e:/oradata/user2_5.ora' size 512M,'e:/oradata/user2_6.ora' size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);

 

--建索引表空间1create tablespace INDEX_DATA1 datafile'f:/oradata/index1_1.ora' size 512M,'f:/oradata/index1_2.ora' size 512M,'f:/oradata/index1_3.ora' size 512M,'f:/oradata/index1_4.ora' size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);

 

--建索引表空间2create tablespace INDEX_DATA2 datafile'g:/oradata/index2_1.ora' size 512M,'g:/oradata/index2_2.ora' size 512M,'g:/oradata/index2_3.ora' size 512M,'g:/oradata/index2_4.ora' size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);

 

--建回滚表空间1--设置初始值40M（initial 40M），则每在这个表空间中建一个回滚段，--此回滚段自动继承此回滚表空间的存储参数，也即默认文件为40Mcreate tablespace ROLLBACK_DATA1 datafile'd:/oradata/roll1_1.ora' size 512M，'d:/oradata/roll1_2.ora' size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 40M next 5M pctincrease 0);

 

--建回滚表空间2create tablespace ROLLBACK_DATA2 datafile'e:/oradata/roll2_1.ora' size 512M,'e:/oradata/roll2_2.ora' size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 40M next 5M pctincrease 0);

 

--建临时表空间1create tablespace TEMPORARY_DATA1 datafile'f:/oradata/temp1_1.ora' size 512Mdefault storage (initial 10M next 3M pctincrease 0);

 

--建临时表空间2create tablespace TEMPORARY_DATA2 datafile'g:/oradata/temp2_1.ora' size 512Mdefault 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个在回滚表空间2create 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 a30SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;

 

--查看数据文件及其所在表空间、大小、状态col file_name format a40col tablespace_name format a20select file_name,file_id,tablespace_name,bytes,status from dba_data_files;

 

至此，表空间重新规划完毕，这里讲的比较通俗，还有好多参数值得设置，能够把Oracle设置到最优的境界，表空间设置完了，下面，就该好好的整理一下Oracle的内存区了，Oracle很有意思，内存越大，效果越明显，所以有必要好好调整一下SGA区，也就是主要配置ininorcl.ora参数文件。