oracle表空間

概念

表空間和數據文件
●表空間是邏輯存儲概念，一個表空間是一個或多個數據文件的邏輯集合
●存儲對象(表、索引)邏輯的存儲在表空間上，而存儲對象的數據物理的存放在數據文件上
●數據庫至少需要一個叫做system的表空間，也就是系統表空間
●一個表空間至少包括一個數據文件，數據文件是最終的數據存儲體，一個數據文件只屬于一個表空間

管理表空間
●通常一個數據庫由多個表空間構成，每個表空間存儲不同特性的數據
●一個正常運行的數據庫通常包括下面這些表空間:
-system存放數據字典信息，?是數據庫必須的表空間，也是創
建數據庫時第一個被創建的表空間
-sysaux存放除了數據字典之外的一些系統管理輔助信息，是
10g新增加的表空間，用于分擔系統表空間的負荷存放自動管理的回滾段信息，提供事務的回滾功能-temp???存放用戶排序時的臨時數據
存放用戶表上的索引信息
*除了systemsysaux表空間必須在創建數據庫的時候創建外，其他表空間都可以在數據庫生成后由DBA創建并進行管理，通常對系統表空間的管理任務較少:

●表空間的空間管理是Oracle描述表空間中空間分配狀況的方法，從Oracle8i開始，空間管理方法有以下兩種:數據字典管理表空間:

??????一是以前一直被沿用的空間管理方法，從9i開始逐漸被淘汰

??????-使用數據字典表Uet$和Fet$描述和管理表空間中空閑空間和使用空間一當空間被分配或者釋放時，相應的數據字典信息被更新本地管理表空間:

??????一直接在各個表空間上管理空閑空間和使用空間

??????-使用存儲在表空間上的Bitmap標示空閑空間和使用空間的信息-Bitmap中的每位描述一個block或者一組block的狀態-從9i開始成為默認的表空間屬性。

? ? ? ? ?數據字典和本地管理表空間

??????●字典管理表空間使用數據字典表描述表空間上的空間分布情況●在表空間中的每個段需要使用存儲子句設置自己的存儲特性:

??????●字典管理表空間的空間管理開銷很大，性能很差，所以DMT技術已經被放棄

??????CREATE?TABL?ESPACE?datas

??????datafle?'/u01/oradata/orcldatas01?.dbf

??????SIZE?500M?EXTENT?MANAGEMENT?DICTIONARY

??????DEFAULT?STORAGE?(initial?1M?NEXT?1M?PCTINCREASE?0),

??????●本地管理表空間使用bitmap描述空間分布情況，減少在數據字典中的競爭●不需要為每個段單獨設置存儲參數，在本地管理表空間上段的區大小遵循表空間的設置

??????●當空間分配或者釋放的時候不產生回滾信息，空間管理開銷很小，對性能非常有好處，所以現在廣泛采用LMT

??????CREATE?TABL?ESPACE?datas

??????datafile?'/u01/oradatmporc/datas01?dbf?SIZE?500M

??????EXTENT?MANAGEMENT?LOCAL?UNIFORM?SIZE?128K;

移植一個DMT表空間

??????●隨著Oracle版本的不斷更新，LMT技術已經逐漸取代了DMT,但是老的系統仍然采用著字典管理方式

??????●表空間的本地管理和字典管理方式不能直接轉換

??????●在升級老版本數據庫到新版本數據庫后，大多數表空間都可以通過創建新的本地管理表空間，并移動存儲對象到新表空間的方式把字典管理表空間替換掉，但是系統表空間卻無法這樣處理

??????●Oralce提供了移植數據字典管理的系統表空問到本地管理的表空間的方法:

??????DBMS_?SPACE_?ADMIN.TABLESPACE_?MIGRATE?_TO_?LOCAL('SYSTEM');

表空間的狀態 ●正常的表空間運行在Online狀態下，Offline狀?態的表空間是不可訪問的，意味著表空間上所有的數據都不能被訪問●表空間在Offline的時候會帶來檢查點事件
●大多數表空間是可以Ofline的，?但是也有些表空間不能被離線:
一系統表空間
:?-存在激活的回滾段的表空間-默認的臨時表空間●Offline表空間的語句:
ALTER?TABLESPACE?users?OFFLINE;●Online表空間的語句:
ALTER?TABLESPACE?users?ONLINE;

tablespace	online	read ?write
system	必須onlimne	必須read ?write
sysaux	可以offline	不能read noly
undo	不能offline	不能read noly

調整表空間

增加文件大小

ALTER TABLESPACE TBS ADD DATAFILE '/oradata/rTBS_TR_IND_002.dbf'  SIZE 32G AUTOEXTEND OFF;   ALTER TABLESPACE TBS ADD DATAFILE 'G:\datafile\TBS_EDS_DAT01.DBF'SIZE 100M   REUSE;ALTER TABLESPACE TBS AUTOEXTEND ON;

重置數據文件的大小

ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf'
RESIZE 500M;

刪除數據文件

ALTER TABLESPACE TEST
DROP DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf'

數據文件自動擴展

ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' AUTOEXTEND ON;
ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' AUTOEXTEND OFF;ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' AUTOEXTEND ON netxt 1M maxsize 1T;

移動數據文件將表空間離線
SQL> alter tablespace users offline;
在操作系統下將數據文件移到另一位置
SQL> host mv /u01/app/oracle/oradata/ocp/users01.dbf /u02/ ?
SQL> host ls /u02/
修改控制文件的記錄指針?
SQL> alter database rename file ??
'/u01/app/oracle/oradata/ocp/users01.dbf' to '/u02/users01.dbf'; ?
或者
SQL> alter tablespace users rename datafile ?
'/u01/app/oracle/oradata/ocp/users01.dbf' to '/u02/users01.dbf'; ?
注：執行此項時，目標文件（TO后面的那一段）一定要存在。 ??
5、將表空間在線 ?
SQL> alter tablespace users online;
對于那些不能offline的表空間，只能關閉數據，在mount狀態下修改，修改后再OPEN

表空間脫機

SQL>SELECT TABLESPACE_NAME, CONTENTS, STATUS FROM DBA_TABLESPACES;
SQL> ALTER TABLESPACE TBS_DM_DAT OFFLINE IMMEDIATE;

表空間的ONLINE和OFFLINE狀態

1. 只要數據庫處于OPEN狀態，除了SYSTEM表空間外的其他表空間，都可以將其置為online或offline狀態。SYSTEM表空間之所以不能置為offline，是因為其中保存的數據字典需要一直使用。將表空間置為offline，可能的原因包括維護、備份以及恢復等目的。

3. 如果表空間處于offline狀態，那么Oracle不會允許任何對該表空間中對象的SQL執行。表空間置為offline時仍處于活動狀態的語句在交易級別并不會受影響。Oracle會保存這些尚未完成語句相關的回滾數據到SYSTEM表空間。當表空間置為online時，如果有必要，那么Oracle會應用這些回滾數據。

3. 除了Oracle外，沒有任何應用能夠讀取或編輯offline的表空間。因此，offline的表空間更不能導到其他數據庫。

4. Oracle在一些場景下會自動切換表空間狀態從online到offline。例如，當數據庫寫進程DBWn，嘗試幾次仍不能寫入表空間的數據文件中。用戶訪問offline表空間時會收到一個報錯。如果造成這種磁盤IO失敗的問題是介質錯誤，解決這問題之后需要恢復表空間。

5. 將表空間置為offline可能包含如下原因：

(1) 需要禁止訪問一部分數據庫的場景。

(2) 執行一次offline表空間備份(盡管表空間可以在online和使用情況下備份)。

(3) 當更新或維護應用時，需要讓應用和對應的表臨時不能訪問。

(4) 重命名或重分配表空間中的數據文件。

當表空間置為offline，數據庫會將關聯的所有數據文件都置為offline狀態。但是如下表空間不能置為offline：

> SYSTEM

> UNDO表空間

> 臨時表空間

6. 將表空間置為offline之前，若有用戶設置該表空間為其默認表空間，需要考慮是否需要改變其默認表空間。之所以這樣做，是因為這些用戶將不能訪問offline表空間中的對象。

7. 使用ALTER TABLESPACE ... OFFLINE語句可以添加如下參數：

NORMAL：如果表空間的任何數據文件沒有出現錯誤，表空間能夠正常地置為offline。如果出現寫錯誤，那么表空間中的數據文件不會被置為offline。當明確使用OFFLINE NORMAL，數據庫會對所有數據文件進行一次checkpoint檢查點事件，NORMAL是默認設置。

TEMPORARY：表空間能夠臨時置為offline狀態，即使一個或多個文件出現錯誤。當使用OFFLINE TEMPORARY，數據庫會將還沒有置為offline的數據文件進行置位操作，并執行檢查點事件。

如果沒有文件是offline，但使用了temporary語句，那么當將表空間置為online時不需要介質恢復。然而，如果表空間的一個或多個文件由于寫入錯誤因此導致offline狀態，能夠臨時將表空間置為offline狀態，恢復online之前需要對表空間進行恢復操作。

IMMEDIATE：表空間能夠立即設置為offline狀態，數據庫不會進行任何數據文件的檢查點事件。當使用OFFLINE IMMEDIATE，那表空間置為online之前需要對表空間進行介質恢復操作。但是如果數據庫處于NOARCHIVELOG模式，表空間就不能立即置為offline狀態。

8. 如果必須設置表空間為offline狀態，建議首先使用默認的NORMAL語句。他會確保當表空間置為online前不需要執行恢復操作，盡管在不完全恢復之后，使用LATER DATABASE OPEN RESTLOGS語句重置了redo日志sequence。

僅僅當不能使用NORMAL方式將表空間置為offline時，可以使用TEMPORARY。使用TEMPORARY后，在表空間置為online前，僅需要恢復那些出錯的offline文件。

如果NORMAL和TEMPORARY都失敗時，才可以選擇使用IMMEDIATE選項。

9. 如果表空間沒有“干凈地”置為offline(也就是沒有使用NORMAL語句執行的offline操作)，那么置為online前需要首先對該表空間執行介質恢復操作。否則數據庫會報錯，表空間仍處于offline狀態。

置脫機狀態，可以使用下面4個參數來控制脫機方式

NORMAL 該參數表示將表空間以正常方式切換到脫機狀態，在進入脫機狀態過程中，ORACLE會執行一次檢查點，將SGA區中與該表空間相關的臟緩存塊寫入數據文件中，然后再關閉表空間的所有數據文件。如果在這過程中沒有發生任何錯誤，則可以使用NORMAL參數，這也是默認的方式。

TEMPORARY 該參數將表空間以臨時方式切換到脫機狀態。這時ORACLE在執行檢查點時并不會檢查各個數據文件的狀態，即使某些數據文件處于不可用狀態，ORACLE也會忽略這些錯誤。這樣將表空間設置為聯機狀態時，可能需要進行數據恢復。

IMMEDIATE 該參數將表空間以立即方式切換到脫機狀態，這時ORACLE不會執行檢查點，也不會檢查數據文件是否可用。而是直接將屬于表空間的數據文件設置為脫機狀態。下一次將表空間恢復為聯機狀態時必須進行數據庫恢復。

FOR RECOVER 該參數將表空間以用于恢復方式切換到脫機狀態，如果要對表空間進行基于時間的恢復，可以使用這個參數將表空間切換到脫機狀態。

如果數據庫運行在非歸檔模式下(NOARCHIVELOG),由于無法保留恢復表空間所需要的重做數據，所以不能將表空間以立即方式切換到脫機狀態。如果表空間脫機了，則查詢表空間下的表，會報錯誤：ORA-00376 此時無法讀取文件以及 ORA-01110：數據文件x......

注意：脫機（offline）一般用于數據庫的聯機備份，數據恢復等維護操作。有些表空間不能OFFLINE，如：SYTEM,UNDO等

1. SYTEM 不能offline，也不能read only

2. 當前的UNDO表空空間，不能offline,也不能read only

3. 當前的臨時表空間不能offline,也不能read only

4. SYSAUX可以offline 不能read only

SQL> ALTER TABLESPACE SYSTEM OFFLINE;

ALTER TABLESPACE SYSTEM OFFLINE

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

SQL> ALTER TABLESPACE SYSTEM OFFLINE;

ALTER TABLESPACE SYSTEM OFFLINE

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

表空間offline,數據文件offline 的區別

在對表空間進行offline時,oracle需要針對這個表空間生成檢查點,刷新表空間中數據文件的相關事務,更新數據文件頭SCN,數據文件 SCN,文件終止SCN,達到一致狀態,當表空間被再次ONLINE時,oracle會捕捉online時刻的SCN,更新上面各個位置的SCN（當然文件結束SCN號會置為無窮大）,將數據文件都打開

在對單個數據文件做offline時,是立即offline,此時不會針對數據文件生成檢查點,所以數據文件的終止SCN為無窮大,處于不一致狀態,數據文件也需要recover,在v$recover_file視圖也可以看到這個文件的信息,要想將數據文件online,必須先對文件執行recover操作,所以建議offline數據文件后應該立刻執行recover操作,使他處于一致狀態,以免后面出現不必要的麻煩

對于非歸檔模式下進行數據文件offline操作需要接上drop子句,他不會物理刪除文件的,在歸檔模式下指定drop子句會被Oracle忽略

另外對于offline的數據文件,正常重起是不能對他進行recover的,因為他的狀態是offline,數據庫啟動時是不檢查offline文件的 SCN是否一致的,所以啟動后你仍然可以在v$recover_file視圖中看到他,需要手工對他進行recover才能恢復,如果應用的聯機日志已經 switch掉,則要從歸檔中進行恢復,沒有歸檔的話,文件就只能一直處于recover狀態
?

對一個datafile執行offline和offline drop的區別?
1、對一個datafile執行offline或offline drop本質上是一回事，但對一個datafile執行offline只能在歸檔模式下；而對一個datafile執行offline drop則既可以在歸檔模式也可以在非歸檔模式下；
2、對一個datafile無論是執行offline還是offline drop，都是只改寫了control文件，不會更新file$和ts$，這就是為什么可以在mount狀態下對某個datafile執行offline/offline drop的本質原因；

3、只有當對datafile所在的表空間執行offline normal的時候，才會既改寫control文件，又更新ts$和seg$，oracle這里會把ts$的online$字段的值由1改為2，但依然不會去更新file$；

4、只有當對datafile所在的表空間執行drop操作的時候，oracle才會去更新ts$和file$，oracle這里會把ts$的online$字段的值由1改為3，會把file$的status$字段由2改為1；

注意，無論是file$的file#還是ts$的ts#，它們都是連續的！并且oracle會重用file$的file#，但是不會重用ts$里的ts#，這本質上是因為ts$里會記錄tablespace的名字，而file$里并沒有記錄datafile的名字，所以file$里的記錄可以重用而ts$則不能。

5、只要你對一個datafile執行了offline或者offline drop操作，則oracle在open的時候就不會去存儲上（無論是文件系統、裸設備還是ASM）校驗這個文件了，所以即使這個文件已經在存儲上被刪掉了，此時庫依然可以open。

6、無論你是在歸檔模式還是在非歸檔模式，且無論你對某個datafile是執行了offline還是offline drop操作，只要歸檔日志還在（對應于歸檔模式）或者相關的online redo log沒有被logfile switch覆蓋（對應于非歸檔模式），則這個datafile始終是可以online的，里面的數據都還在。當然，即使歸檔日志不在了，online redo log被logfile switch覆蓋了，這個datafile也是可以online的，只是里面的數據可能會不一致。

查看表空間容量

select tablespace_name,sum(bytes) from dba_free_space group by tablespace_name;

TABLESPACE_NAME ???????????SUM(BYTES)
------------------------------ ----------
SYSAUX????????????????34209792
UNDOTBS1??????????????1638400
USERS????????????????102170624
SYSTEM????????????????7077888
TEST_TABLESPACE ??????????9437184

查看表空間剩余總量

SELECT dbf.tablespace_name,
dbf.totalspace/1024 "總量(G)",
dbf.totalblocks AS 總塊數,
dfs.freespace/1024 "剩余總量(G)",
dfs.freeblocks "剩余塊數",
(dfs.freespace / dbf.totalspace) * 100 "空閑比例"
FROM (SELECT t.tablespace_name,
SUM(t.bytes) / 1024 / 1024 totalspace,
SUM(t.blocks) totalblocks
FROM dba_data_files t
GROUP BY t.tablespace_name) dbf,
(SELECT tt.tablespace_name,
SUM(tt.bytes) / 1024 / 1024 freespace,
SUM(tt.blocks) freeblocks
FROM dba_free_space tt
GROUP BY tt.tablespace_name) dfs
WHERE TRIM(dbf.tablespace_name) = TRIM(dfs.tablespace_name)
AND dfs.tablespace_name='ISC';

縮表空間語句

select 'alter database datafile ''' || a.file_name || ''' resize ' ||
round(a.filesize - (a.filesize - c.hwmsize - 100) * 0.8) || 'M;',
a.filesize || 'M' as "數據文件的總大小",
c.hwmsize || 'M' as "數據文件的實用大小"
from (select file_id, file_name, round(bytes / 1024 / 1024) as filesize
from dba_data_files
WHERE file_id=6) a,?
(select file_id, round(max(block_id) * 8 / 1024) as HWMsize
from dba_extents
group by file_id) c?
where a.file_id = c.file_id
and a.filesize - c.hwmsize > 100
AND c.file_id='6';

--Oracle 表空間使用率
SELECT D.TABLESPACE_NAME,  SPACE || 'M' "SUM_SPACE(M)",  SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",  ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'  "USED_RATE(%)",  FREE_SPACE || 'M' "FREE_SPACE(M)"  FROM (  SELECT TABLESPACE_NAME,  ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  SUM (BLOCKS) BLOCKS  FROM DBA_DATA_FILES  GROUP BY TABLESPACE_NAME) D,  (  SELECT TABLESPACE_NAME,  ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE  FROM DBA_FREE_SPACE  GROUP BY TABLESPACE_NAME) F  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  
UNION ALL                                                           --如果有臨時表空間  
SELECT D.TABLESPACE_NAME,  SPACE || 'M' "SUM_SPACE(M)",  USED_SPACE || 'M' "USED_SPACE(M)",  ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",  NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"  FROM (  SELECT TABLESPACE_NAME,  ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  SUM (BLOCKS) BLOCKS  FROM DBA_TEMP_FILES  GROUP BY TABLESPACE_NAME) D,  (  SELECT TABLESPACE_NAME,  ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,  ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE  FROM V$TEMP_SPACE_HEADER  GROUP BY TABLESPACE_NAME) F  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  
ORDER BY 1;

Oracle查看用戶表空間使用情況：select a.file_id "FileNo",a.tablespace_name   
"Tablespace_name",   
a.bytes "Bytes",a.bytes-sum(nvl(b.bytes,0)) "Used",   
sum(nvl(b.bytes,0)) "Free",   
sum(nvl(b.bytes,0))/a.bytes*100 "%free"   
from dba_data_files a, dba_free_space b   
where a.file_id = b .file_id(+)   
group by a.tablespace_name ,   
a.file_id,a.bytes order by a.tablespace_name;

??????select?segment_?name,?blocks,?extents,?bytes,?segment_?type,?tablespace_?name?fromdba_?segments?where?segment_?name='?T2?;

??????select?blocks,?empty_?blocks?from?dba_?tables?where?table_?name?=?,?T2'?;

analyze?table?tablename??compute?statlstlcs?;
analyze?table?tabl?ename?compute?statistics?for?all?indexes;analyze?table?tablename?delete?statistics

DBMS_?STATS?E?E?Nstatistics?iItT:
GATHER_?INDEX?STATSIndex?statistics

GATHER_?TABLE_?STATS
Table,??column,?and?index?statistics

exec?dbms_?stats.?gather_?table_?stats('?SYS'?,'?T2'?)?;
GATHER_?SCHEMA_?STATS
Statistics?for?al1?objects?in?a?schema

GATHER_?DICTIONARY_?STATS
Statistics?for?al1?dictionary?objects

??SELECT?NUM?ROWS,?-表中的記錄數BLOCKS,一表中數據所占的數據塊數EMPTY_?BLOCKS,?--表中的空塊數

??????AVG_?SPACE,??數據塊中平均的使用空間CHAIN?CNT,??表中行連接和行遷移的數量AVG_?ROW_?LENJ?--?每條記錄的平均長度FROM?USER?TABLES索引列的統計信息

??????包含索引的深度(B-Tree的級別)，索引葉級的塊數量，集群因子(clustering_?factor),?唯-?-值的個數。SELECT?BLEVEL,?--索引的層數

??????LEAF_?BLOCKS，?--葉子結點的個數DISTINCT_?KEYS，--唯-?-?-值的個數

??????AVG_?LEAF_?BLOCKS_?PER?_KEY，--每個KEY的平均葉塊個數AVG_?DATA_?BLOCKS_?PER_?_KEY，?--每個KEY的平均數據塊個數CLUSTERING_?FACTOR?--?群集因子FROM?USER?INDEXES列的統計信息

??????包含唯一一的值個數，列最大小值，密度(選擇率)，數據分布(直方圖信息)，NU11值個數

列的統計信息

包含唯一的值個數，列最大小值，密度(選擇率)，數據分布(直方圖信息)，NU11值個數ELECT?NUM?DISTINCT,?--唯一值的個數

??????LOW_?VALUE,?--列上的最小值HIGH_?VALUE,?--列上的最大值DENSITY,?--?選擇率因子(密度)NUM_?NULLS，一-?空值的個數

??????NUM?BUCKETS,一直方圖的BUCKET個數HISTOGRAM?--直方圖的類型ROM?USER_?TAB_?COLUMNS

? ?