配置: 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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10144097/viewspace-934571/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10144097/viewspace-934571/