Oracle 表空間和數(shù)據(jù)文件

多個表空間的優(yōu)勢:
1.能夠將數(shù)據(jù)字典與用戶數(shù)據(jù)分離出來，避免由于字典對象和用戶對象保存在同一個數(shù)據(jù)文件中而產(chǎn)生的I/O沖突
2.能夠將回退數(shù)據(jù)與用戶數(shù)據(jù)分離出來，避免由于硬盤損壞而導致永久性的數(shù)據(jù)丟失
3.能夠將表空間的數(shù)據(jù)文件分散保存到不同的硬盤上，平均分布物理I/O操作
4.能夠將某個表空間設置為脫機狀態(tài)或聯(lián)機狀態(tài)，以便對數(shù)據(jù)庫的一部分進行備份和恢復
5.能夠將某個表空間設置為只讀狀態(tài)，從而將數(shù)據(jù)庫的一部分設置為只讀狀態(tài)
6.能夠為某種特殊用途專門設置一個表空間，比如臨時表空間等，以優(yōu)化表空間的使用效率
7.能夠更佳靈活的為用戶設置表空間限額

SYSTEM表空間內存儲：
1.數(shù)據(jù)庫的數(shù)據(jù)字典
2.所有PL/SQL程序的源代碼和解析代碼
3.數(shù)據(jù)庫對象的定義

（所有表空間的數(shù)據(jù)文件總和不能超過創(chuàng)建數(shù)據(jù)庫時指定的maxdatafiles參數(shù)的限制）

本地管理表空間:
1.在存儲分配過程中不需要訪問數(shù)據(jù)庫，可以提高存儲分配操作的速度
2.能夠避免在表空間的存儲管理操作中產(chǎn)生的遞歸現(xiàn)象
3.不會產(chǎn)生重做和撤銷記錄
4.簡化DBA對表空間的管理操作
5.降低用戶對數(shù)據(jù)字典的依賴性

字典管理方式的表空間:
表空間中所有存儲空間的管理信息都保存在數(shù)據(jù)字典中，在進行存儲空間管理時會產(chǎn)生回退和重做記錄

本地管理方式的表空間:
表空間中所有存儲空間的管理信息都保存在數(shù)據(jù)文件頭部的位圖中

（在9i中創(chuàng)建表空間默認就是本地管理方式.8i以前的版本，只能用字典管理方式的表空間.）

創(chuàng)建表空間(字典管理方式)
create tablespace dmusertbs
datafile 'i:\oracle\oradata\dmusertbs.dbf' size 50M
extent management dictionary;

多個數(shù)據(jù)文件(字典 管理方式 )
create tablespace dmusertbs
datafile 'i:\oracle\oradata\dmusertbs01.dbf' size 50M,
         'i:\oracle\oradata\dmusertbs02.dbf' size 50M,
         'i:\oracle\oradata\dmusertbs03.dbf' size 50M
extent management dictionary;

指定默認存儲參數(shù)(字典管理方式)
create tablespace dmusertbs
datafile 'i:\oracle\oradata\dmusertbs.dbf' size 50M
default storage(
   initial 256K
   next 256K
   minextents 2
   pctincrease 0
   maxextents 4096)
minimum extent 128K
logging
online
permanent
extent management dictionary;

創(chuàng)建表空間(本地管理方式)

1 .create tablespace lmusertbs
  datafile 'i:\oracle\oradata\lmusertbs.dbf' size 50M
  extent management local autoallocate;
  (oracle對區(qū)的分配進行自動管理,這是默認的設置,在autoallocate方式下, 表空間中最小的區(qū)為64K)

2 .create tablespace lmusertbs
  datafile 'i:\oracle\oradata\lmusertbs.dbf' size 50M
  extent management local uniform size 512K;
  (所有的區(qū)必須具有統(tǒng)一的大小,Uniform方式帶來的最大優(yōu)點是在表空間中不會產(chǎn)生任何存儲碎片,
   如果在uniform關鍵字后面沒有指定size參數(shù)的值,SIZE參數(shù)將使用1MB做為默認值)

3. create tablespace lmusertbs
  datafile 'i:\oracle\oradata\lmusertbs.dbf' size 50M
  extent management local uniform size 512K
  segment space management auto;
  (具有自動段存儲管理方式的表空間)

創(chuàng)建臨時表空間(字典管理方式)
一個臨時表空間可以被多個數(shù)據(jù)庫用戶共享, oracle只會為一個實例創(chuàng)建一個臨時段,這個臨時段被實例中所有的排序操作共享使用，但是臨時段中的每一個區(qū)只能由一個事務使用.臨時段在數(shù)據(jù)庫啟動后執(zhí)行第一次排序操作時被創(chuàng)建.

注意 ：字典管理方式的臨時表空間可以象普通表空間那樣使用 alter tablespace 來進行更改
建議將initial和next參數(shù)設置成相同的值,并且應當是初始化參數(shù)sort_area_size和db_block_size
兩個值之和的整數(shù)倍,pctincrease參數(shù)應當設置成0
create tablespace usertemp
datafile 'i:\oracle\oradata\sort01.dbf' size 50M
extent management dictionary
default storage(
  initial 192K
  next 192K
  minextents 1
  pctincrease 0)
temporary;

創(chuàng)建臨時表空間(本地管理方式,區(qū)的分配管理方式只能是uniform)
（在Oracle 9i中，強烈建議使用本地管理方式的臨時表空間來代替字典管理方式的臨時表空間）

create temporary tablespace lmtemp
 tempfile 'i:\oracle\oradata\lmtemp01.dbf' size 50M
 extent management local  uniform  size 136K
 (必須使用tempfile子句,tempfile為臨時數(shù)據(jù)文件,與普通數(shù)據(jù)文件相比,臨時數(shù)據(jù)文件不能用alter database來創(chuàng)建)

對于本地管理方式的臨時表空間,alter tablespace語句的作用僅僅是為臨時表空間添加新的臨時文件.
增加新的臨 時文件
alter tablespace lmtemp
 add tempfile 'i:\oracle\oradata\lmtemp02.dbf' size 50M
(對于本地管理方式的表空間,alter tablespace語句的作用僅僅是為臨時表空間添加新的臨時文件,而不能對臨時表空間進行其它任何修改)

表空間的管理主要包括修改默認存儲參數(shù)，手工合并碎片，設置表空間的可用性等幾個方面。盡量讓表空間使用較少的數(shù)據(jù)文件，因為在某些操作系統(tǒng)中對一個進程可以同時打開的系統(tǒng)文件數(shù)量進行限制，由此會影響到同時處于聯(lián)機狀態(tài)的表空間數(shù)量。

修改表空間的默認存儲參數(shù)
alter tablespace duusertbs
default storage(
  next 128K
  maxextents unlimited
  pctincrease 20);
(表空間創(chuàng)建之后不能對 initial 和 minextents 參數(shù)進行修改)

如果表空間的默認存儲參數(shù) pctincrease不為0 ,oracle將定期啟動SMON后臺進程，合并表空間相鄰的存儲碎片,
如果 pctincrease是0 ,SMON進程不會執(zhí)行合并操作.
如果表空間中所有的區(qū)都具有相同的大小，則不需要手工合并存儲碎片.

本地管理方式的表空間不需要進行存儲碎片的合并操作，因為oracle會自動利用位圖自動合并.
只需要在字典管理方式的表空間中 手工合并存儲碎片.

alter tablespace dmusertbs coalesce
(如果使用了coalesce子句，則不能在使用其它任何子句)

利用下面的查詢可以看到表空間users中存在哪些存儲碎片
SQL> select block_id,bytes,blocks
  2  from dba_free_space
  3  where tablespace_name='users'
  4  order by block_id;

改變表空間的 可用性:

1. alter tablespace user01 offline normal (正常方式)
oracle會執(zhí)行一次檢查點，將SGA區(qū)中與該表空間相關的臟緩存塊都寫入數(shù)據(jù)文件中，然后再關閉表空間對應的數(shù)據(jù)文件,下一次將表空間。恢復為聯(lián)機狀態(tài)時,不需要進行數(shù)據(jù)庫恢復.

2. alter tablespace user01 offline temporary( 臨時方式)
oracle執(zhí)行檢查點的時候并不會檢查各個數(shù)據(jù)文件的狀態(tài)，即使某些數(shù)據(jù)文件不可用，oracle也會忽視這些錯誤,下一次將表空間恢復為聯(lián)機狀態(tài)時,可能需要進行數(shù)據(jù)庫恢復.如果數(shù)據(jù)文件可用,oracle會將與該表空間相關的臟緩存塊都寫入數(shù)據(jù)文件中.

3. alter tablespace user01 offline immediate (立即方式)
oracle不會執(zhí)行檢查點，也不會檢查各個數(shù)據(jù)文件的狀態(tài)，而是直接將屬于表空間的數(shù)據(jù)文件設置成脫機狀態(tài),下一次將表空間恢復為聯(lián)機狀態(tài)時,需要進行數(shù)據(jù)庫恢復.運行在noarchivelog方式下的數(shù)據(jù)庫不允許用這種方式切換到脫機狀態(tài).

恢復表空間為聯(lián)機狀態(tài)
alter tablespace user01 online;

設置表空間為只讀狀態(tài)
alter tablespace user01 read only

設置表空間為讀寫狀態(tài)
alter tablespace user01 read write

刪除表空間(不包括對應的數(shù)據(jù)文件)
drop tablespace users including contents;

刪除表空間(包括對應的數(shù)據(jù)文件)
drop tablespace users including contents and datafiles;

表空間數(shù)據(jù)字典
v$tablespace 控制文件中獲取的表空間的名稱和編號信息
v$datafile 控制文件中獲取的數(shù)據(jù)文件的名稱和編號信息
v$tempfile 所有臨時數(shù)據(jù)文件的基本信息
v$sort_segment 實例所創(chuàng)建的排序區(qū)的信息
v$sort_user 排序區(qū)的用戶使用情況信息
dba_tablespaces 數(shù)據(jù)庫中表空間的名稱和編號信息
dba_segments 表空間中段的信息
dba_extents 表空間中區(qū)的信息
dba_free_space 表空間中空閑區(qū)的信息
dba_data_files 數(shù)據(jù)文件亦即所屬表空間的信息
dba_temp_files 臨時數(shù)據(jù)文件亦即所屬表空間的信息

包括為表空間添加新的數(shù)據(jù)文件，更改已有數(shù)據(jù)文件的大小，名稱或者位置。初始化參數(shù)db_files指定在SGA區(qū)中能夠保存的數(shù)據(jù)文件信息的最大數(shù)量，也就是一個實例所能支持的數(shù)據(jù)文件的最大數(shù)量,能夠在實例運行過程中修改它.

1. 創(chuàng)建表空間lmusertbs時將數(shù)據(jù)文件設置為自動增長方式
create tablespace lmusertbs
  datafile 'i:\oracle\oradata\lmusertbs01.dbf' size 50M
  autoextend on
  next 5M
  maxsize 500M
  extent management local;
(next參數(shù)指定每次自動增長的大小,maxsize為數(shù)據(jù)文件的最大大小)

2. 在表空間lmusertbs上添加一個自動增長方式的數(shù)據(jù)文件
alter tablespace lmusertbs
  add datafile 'i:\oracle\oradata\lmusertbs02.dbf' size 50M
  autoextend on
  next 5M
  maxsize 500M;

3.如果數(shù)據(jù)文件已經(jīng)創(chuàng)建，將它設置成自動增長方式
alter database
  datafile 'i:\oracle\oradata\dmusertbs01.dbf'
  autoextend on
  next 5M
  maxsize 500M;

4.取消已有數(shù)據(jù)文件的自動增長方式
alter database
  datafile 'i:\oracle\oradata\dmusertbs01.dbf'
  autoextend off;

5.手工改變數(shù)據(jù)文件的大小:

將數(shù)據(jù)文件dmusertbs01.dbf增大為500MB

alter database datafile 'i:\oracle\oradata\dmusertbs01.dbf' resize 500M;

單獨改變數(shù)據(jù)文件的可用性(數(shù)據(jù)庫運行在歸檔模式下):
alter database datafile 'i:\oracle\oradata\dmusertbs01.dbf' online;(聯(lián)機狀態(tài))
alter database datafile 'i:\oracle\oradata\dmusertbs01.dbf' offline;(脫機狀態(tài))

單獨改變數(shù)據(jù)文件的可用性(數(shù)據(jù)庫運行在不歸檔模式下):
alter database datafile '/u02/oracle/oradata/user01.dbf' offline drop;
用offline drop可以確保使數(shù)據(jù)文件脫機時立即丟掉

6.改變數(shù)據(jù)文件的名稱和位置:
A.要改變的數(shù)據(jù)文件屬于同一個表空間

1.將包含數(shù)據(jù)文件的表空間設置為脫機狀態(tài)
       alter tablespace users offline normal

2.在操作系統(tǒng)中重新命名或者移動數(shù)據(jù)文件

3.在數(shù)據(jù)庫內部修改數(shù)據(jù)文件的名稱或者位置(用alter tablespace ... rename datafile子句,to子句后的數(shù)據(jù)文件必須存在)
改變名稱:
alter tablespace users
  rename datafile
    'i:\oracle\oradata\user01.dbf',
    'i:\oracle\oradata\user02.dbf'
  to
    'i:\oracle\oradata\lmuser01.dbf',
    'i:\oracle\oradata\lmuser02.dbf';

改變位置:
alter tablespace users
  rename datafile
    'i:\oracle\oradata\user01.dbf',
    'i:\oracle\oradata\user02.dbf'
  to
    ' h :\oracle\oradata\user01.dbf',
    ' h :\oracle\oradata\user02.dbf';

4.重新將表空間設置為聯(lián)機狀態(tài)
alter tablespace users online

5.備份控制文件

B.要改變的數(shù)據(jù)文件屬于多個表空間
1.關閉數(shù)據(jù)庫
2.在操作系統(tǒng)中重新命名或者移動數(shù)據(jù)文件
3.加載數(shù)據(jù)庫,startup mount
4.在數(shù)據(jù)庫內部修改數(shù)據(jù)文件的名稱或者位置(用alter database ... rename file子句,to子句后的數(shù)據(jù)文件必須存在)
alter database
  rename file
    'i:\oracle\oradata\user01.dbf',
    'i:\oracle\oradata\temp01.dbf'
  to
    'i:\oracle\oradata\lmuser01.dbf',
    'i:\oracle\oradata\lmtemp01.dbf';

5.alter database open;
6.備份控制文件

數(shù)據(jù)文件數(shù)據(jù)字典
DBA_DATA_FILES 數(shù)據(jù)庫中所有數(shù)據(jù)文件的信息
DBA_TEMP_FILES 數(shù)據(jù)庫中所有臨時數(shù)據(jù)文件的信息
DBA_EXTENTS 表空間中已分配的區(qū)的描述信息，包括區(qū)所屬的數(shù)據(jù)文件的編號
DBA_FREE_SPACE 表空間中空閑區(qū)的信息

刪除表空間的數(shù)據(jù)文件
(適用情況:不小心給一個表空間增加了一個數(shù)據(jù)文件，或者你把文件大小設得過大，所以想把它刪掉)

(注:Oracle不提供如刪除表。視圖一樣刪除數(shù)據(jù)文件的方法，數(shù)據(jù)文件是表空間的一部分，所以不能“移走”表空間。
 在對表空間/數(shù)據(jù)文件進行任何脫機、刪除之前，首先對數(shù)據(jù)庫進行一個全備份)

A: 如果數(shù)據(jù)文件是所在表空間的唯一的數(shù)據(jù)文件，你只要簡單地刪除表空間即可：
DROP TABLESPACE <tablespace name> INCLUDING CONTENTS

B: 如果你的表空間有多個數(shù)據(jù)文件，而你不需要表空間中的內容，或者你可以很容易重新產(chǎn)生表空間的內容，你可以使用
DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;命令來從Oracle數(shù)據(jù)字典刪除表空間、數(shù)據(jù)文件和表空間的內容。Oracle不會再訪問該表空間中的任何內容。然后重新創(chuàng)建表空間并重新導入數(shù)據(jù)。

C: 如果你的表空間有多個數(shù)據(jù)文件，而你還需保留該表空間中的其它數(shù)據(jù)文件中的內容，則你必須首先export出該表空間中的所有內容。為了確定表空間中包含那些內容，運行：
select owner,segment_name,segment_type
from dba_segments
where tablespace_name='<name of tablespace>'

export出你想保留的內容。如果export結束，你可以使用DROP TABLESPACE tablespace INCLUDING CONTENTS. ，這樣永久刪除表空間的內容，使用操作系統(tǒng)命令物理刪除數(shù)據(jù)文件，按所需數(shù)據(jù)文件重新創(chuàng)建表空間，把數(shù)據(jù)import至表空間。

注意：

ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP命令不能允許你刪除數(shù)據(jù)文件，它的目的是脫機該數(shù)據(jù)文件以刪除表空間。如果在歸檔模式下，使用ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP來代替OFFLINE DROP。一旦數(shù)據(jù)文件脫機，Oracle不會再訪問該數(shù)據(jù)文件的內容，但它仍然是表空間的一部分。這個數(shù)據(jù)文件在控制文件中標記OFFLINE，在數(shù)據(jù)庫啟動時不會對它與控制文件進行SCN的比較。在控制文件中保留這個數(shù)據(jù)文件的入口是方便以后的恢復。

如果你不想按照上述方法來刪除表空間，還有其它一些解決方法。

1.如果你想刪除數(shù)據(jù)文件的原因是因為分配了不合適的文件大小，你可以考慮RESIZE命令。
2.如果你不小心增加了一個數(shù)據(jù)文件，而這個文件還沒有分配空間，你可以使用

ALTER DATABASE DATAFILE <filename> RESIZE;命令使其小于5個 Oracle塊大小，如果數(shù)據(jù)文件的大小小于這個，Oracle將不會進行擴展數(shù)據(jù)文件。在以后，Oracle可以重建的時候來剔除這個不正確的文件。

---------------------------------------------------------------------------------------
<例子>
Question:

比如我的uses tablespace 有2個數(shù)據(jù)文件：users01.dbf 和 users02.dbf ,（數(shù)據(jù)庫為非歸檔模式）假如我執(zhí)行
alter tablespace users offline;
alter database datafile users02.dbf offline drop；
這時如果我在操作系統(tǒng)級別上刪除users02.dbf,下次重啟時會提示丟失文件，
我估計此命令只是在controlfile 級別做了修改，但dictionary中還是記錄有這個文件，請問如何完全刪除表空間中的一個數(shù)據(jù)文件？

ASK:
在noarchivelog mode， alter database datafile ...offline drop;
必須加上drop 選項，但是drop選項，并不從database 移除datafile。為了drop datafile， 你必須刪除datafile所在的表空間。用drop選項，datafile任然保留在數(shù)據(jù)字典中，狀態(tài)為recover 或者offline。
 
alter database datafile ...offline drop;
一旦數(shù)據(jù)文件offline，oracle不再訪問那個datafile，但是它still是那個表空間的一部分。datafile在controlfile中被標記為offline，在startup的時候，在controlfile和datafile之間并沒有對scn 做比較（這也運行你startup數(shù)據(jù)庫，伴隨一個不關鍵的datafile丟失）。datafile并沒有從controlfile上刪除，從而給你recover the datafile。
 
如果你真的想刪除user02.dbf, 你可以用transport tablespace特性，或者導出你想要保存的在user tablespace空間的object， 重新建立一個new tablespace

If you do not wish to follow any of these procedures, there are other things 
that can be done besides dropping the tablespace.
 
If the reason you wanted to drop the file is because you mistakenly created 
the file of the wrong size, then consider using the RESIZE command.  
 
If you really added the datafile by mistake, and Oracle has not yet allocated  any space within this datafile, then you can use ALTER DATABASE DATAFILE <filename> RESIZE; command to make the file smaller than 5 Oracle  blocks.  If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be
rebuilt to exclude the incorrect datafile.