安装Oracle后,经常使用的修改表空间的SQL代码

类别:数据库 点击:0 评论:0 推荐:

配置:
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:\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 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:\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 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);

--建索引表空间1
create 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 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);

--建索引表空间2
create 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 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:\oradata\roll1_1.ora' size 512M,
'd:\oradata\roll1_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:\oradata\roll2_1.ora' size 512M,
'e:\oradata\roll2_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:\oradata\temp1_1.ora' size 512M
default storage (initial 10M next 3M pctincrease 0);

--建临时表空间2
create tablespace TEMPORARY_DATA2 datafile
'g:\oradata\temp2_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参数文件。

本文地址:http://com.8s8s.com/it/it19624.htm