安裝Oracle后，經常使用的修改表空間的SQL代碼
2024-08-29 13:29:07
供稿：網友
配置：
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參數文件。
