oracle維護表空間和資料檔案

oracle維護表空間和資料檔案

一： oracle 表空間概念

表空間是聯系資料庫的實體磁盤（資料檔案）和邏輯元件的橋梁，表空間是用來存儲oralce資料庫對象例如：tables、indexes和 rollback segments。可以想象oracle實體磁盤和邏輯磁盤之間的關系，表空間的概念和這個一樣。 PS： 資料檔案 就相當于一個省的的範圍（這個是實實在在存在的），而 表空間相當于在省上建立的管理機構，行政機構等，這些是相當于邏輯的結構，這些 表空間的 資訊在資料字典中存儲。

一個表空間是由至少一個資料庫 datafiles（資料檔案）組成，在表空間的總大小是可變的，所有配置設定在表空間的資料檔案的實體磁盤空間大小也是可變的。

當表空間被定義的 時候，datafiles自動建立，在大多數情況下，所有的datafile是被提前配置設定的，當datafile建立的時候，空間即被設定，你，你能夠定義datafile的初始大小。

tablespace 就是把一組data file 放在一起 成為一個 tablespace；是一個邏輯概念。

關于oracle的一些概念：

先科普以下概念：

1：oracle block concept（塊概念）

一個oracle塊是一個oracle資料塊的最小的存儲單元，oracle資料庫塊的大小當建立資料的時候被指定，除非oracle資料進行重構，否則不能進行修改了，資料庫塊大小通常為2K,4K,8K,16KOR 32K。一旦定義了塊的大小，就能夠建立新的 表空間了，表空間包含若幹個塊。

注意：oracle資料庫instance也包含一個RAM buffer cache，組成RAM buffer cache的塊适合和實體資料檔案資料塊相對應。

資料庫包含有多個 tablespace 一個 tablespace包含一個或多個 datafiles ，一個表或者一個index 暫居一個segment

extent 由連續的oracle data block組成 ： 邏輯上連續。

一個segment可以存儲在不同的datafiles裡面，跨越的概念；extent不能跨越datafiles。oralce data block 包含 一個或多 os block

二：表空間的類型：

1 ：一個資料庫至少包含一個tablespace 就是 SYSTEM;

system tablespace ；建立資料庫的時候，建立包含資料字典包含SYSTEM undo segment 

Non-SYSTEM tablespace

2：更科學的劃分 tablespace的類型：

1：permanent 永久表空間 (SYSTEM 和　No-SYSTEM tablespace 就是這種類型)

2： undo 表空間

3：temporary 表空間

2，3是用于管理資料庫 的表空間，所有不永久存儲資料，

PS：當一個資料被建立的時候，将包含以下表空間：

SYSTEM (the data dictionary)

SYSAUX (optional database components)

TEMP (temporary tablespace, see tablespace types below)

UNDOTBS1 (undo tablespace, see tablespace types below)

USERS (default users tablespace created)

3：建立表空間 create tablespace oracle doc 11: 

建立表空間的簡單文法：

[sql]

SQL> CREATE TABLESPACE peng DATAFILE 'D:\APP\TOPWQP\ORADATA\ORCL\peng01.DBF' S 

IZE 5M; 

表空間已建立。 

查找表空間資訊

SQL> select * from v$tablespace; 

TS# NAME INC BIG FLA ENC 

---------- ------------------------------ --- --- --- --- 

0 SYSTEM YES NO YES 

1 SYSAUX YES NO YES 

2 UNDOTBS1 YES NO YES 

4 USERS YES NO YES 

3 TEMP NO NO YES 

6 EXAMPLE YES NO YES 

7 USER_TEMP NO NO YES 

8 MY_SPACE YES NO YES 

9 TS_MYDB YES NO YES 

10 PENG YES NO YES 

已選擇10行。 

如何知道一個表空間有哪些資料檔案：

SQL> col file_name format a40 

SQL> 

SQL> select file_name ,tablespace_name from dba_data_files; 

FILE_NAME TABLESPACE_NAME 

---------------------------------------- ------------------ 

D:\APP\TOPWQP\ORADATA\ORCL\USERS01.DBF USERS 

D:\APP\TOPWQP\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1 

D:\APP\TOPWQP\ORADATA\ORCL\SYSAUX01.DBF SYSAUX 

D:\APP\TOPWQP\ORADATA\ORCL\SYSTEM01.DBF SYSTEM 

D:\APP\TOPWQP\ORADATA\ORCL\EXAMPLE01.DBF EXAMPLE 

D:\APP\TOPWQP\ORADATA\ORCL\MY_SPACE.DBF MY_SPACE 

D:\APP\TOPWQP\ORADATA\ORCL\TS_MYDB.DBF TS_MYDB 

D:\APP\TOPWQP\ORADATA\ORCL\PENG01.DBF PENG 

已選擇8行。 

4：兩種管理資料字典的方式：

1： locally managed tablespace 自治式管理：

1：自己管理tablespace 

2：資料字典中有個Bitmap 用于管理 tablespace （一個位圖對應一個extent的意味1占用 0未占用）

在每一個資料檔案的頭，都會有一個bitmap 位圖，來表示空間的占用情況。一個bit代表一個extent，占用1 未占用 0；locally-managed也是串行，不能并行，但是 每個表空可以自己管理，所有就相當于并行。如果SYSTEM表空間用的是local-managed ，其他表空間就隻能是 local-managed的了。以後就要用local-managed 方式管理表空間。

如果由于曆史的原因 dictionary 管理的 SYSTEM tablespace 想變成locally managed：需要執行如下指令：

DBMS_SPACE_ADMIN.TABLESPACE_MIGRANTE_TO_LOCAL('SYSTEM'); 

注意：做這個指令前的準備工作：　1：全備份資料庫　2：確定臨時表空間不是SYSTEM

自己管理的tablespace 有兩個表管理這些資料：

？疑問 每個表空間口有自己的 DEBIT和CREDIT表嗎？

DEBIT表：記錄所有已經被配置設定的extent

CREDIT表：記錄所有空閑的extent

當申請空間的時候，oracle就去查詢這些表，然後配置設定空間，當向表中

插入資料的時候，就是在申請空間。

2： data-dictionary 的管理。

當自己的表中插入資料的時候，oracle要去查詢資料字典（執行很多隐含sql）然後配置設定空間。因為資料字典隻有一個：對資料字典的通路必須是序列化的，不能并發，如果通過資料字典，管理表空間， 容易造成資料庫的開銷過大，是以這種方式有點過時，中央叢集的管理方式是 data-dictionary，地方似的管理是新出的管理表空間的方式。

PS ：大事中央管，小事自己管。 中央集權制放掉，搞自治

　3：undo tablespace：

主要是用來存儲undo segment； 就是再對一個表或者表空間做修改的時候，undo 就是做事先進行備份。undo一定是 loacally mananged　　以後有專門一章進行講解；

5：temporary tablespaces：

就是oracle的臨時存儲用的，比如做一些大型的排序，在記憶體中做肯定不行，記憶體不夠，這個時候就需要在臨時表空間中進行排序操作。臨時的中轉站。有一個全局的臨時表空間可以被大家共用，隻用于存放臨時的資訊，可以 data-dictionary方式，但是推薦使用locally -mananged管理方式．

強烈建議，在建立資料庫的時候額外指定 臨時表空間。如果沒有指定，預設是 SYSTEM這樣做事很危險的。是以要指定。

有兩種方式指定 default temporary tablespace：

1：建立資料庫的使用 create database的時候。

　DEFAULT TEMPORARY TABLESPACE temp　　TEMPFILE '路徑/檔案名' SIZE 400M

2： 可以使用 alter database 的時候。

ALTER DATABASE DEFAULT TEMPORYARY TABLESPACE default_temp2;（這個要首先建立表空間）

6： 查詢預設的臨時表空間

desc database_properties; 

col property_name format a20; 

col property_value format a20; 

select property_name,property_value from database_properties; 

7 ：如何看一個表空間的 類型：

SQL> select tablespace_name,contents,extent_management from dba_tablespaces; 

TABLESPACE_NAME CONTENTS EXTENT_MAN 

------------------ --------- ---------- 

SYSTEM PERMANENT LOCAL 

SYSAUX PERMANENT LOCAL 

UNDOTBS1 UNDO LOCAL 

TEMP TEMPORARY LOCAL 

USERS PERMANENT LOCAL 

EXAMPLE PERMANENT LOCAL 

USER_TEMP TEMPORARY LOCAL 

MY_SPACE PERMANENT LOCAL 

TS_MYDB PERMANENT LOCAL 

PENG PERMANENT LOCAL 

8：修改預設表空間

下面進行新加一個表空間：

SQL> create temporary tablespace mytemp tempfile 'D:\APP\TOPWQP\ORADATA\ORCL\my 

temp.DBF' size 100M extent management local; 

SQL> ALTER DATABASE DEFAULT TEMPORYARY TABLESPACE mytemp; 

ALTER DATABASE DEFAULT TEMPORYARY TABLESPACE mytemp 

* 

第 1 行出現錯誤: 

ORA-02231: ALTER DATABASE 選項缺失或無效 

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE mytemp; 

資料庫已更改。 

預設臨時表空間就修改了，可以通過如下指令查詢：

SQL> select property_name,property_value from database_properties; 

PROPERTY_NAME PROPERTY_VALUE 

-------------------- -------------------- 

DICT.BASE 2 

DEFAULT_TEMP_TABLESP MYTEMP 

ACE 

DEFAULT_PERMANENT_TA USERS 

BLESPACE 

一個資料庫可以有多個臨時表空間，但是隻有一個default的表空間。

9： 表空間隻讀設定：

ALTER TABLESPACE mytemp READ ONLY;

執行這條語句：oracle将做如下操作：

1:引起　checkpoint;

2:資料變成隻讀操作

3：依然可以從表空間中删除一個object(table index 等)　

這個在實際中用的不多，我簡單說一下方法，就不做實驗了。

[plain]

實驗： 建立一個表空間： 

create tablespace wang datafile '/檔案名/' size 100M 

extent management local uniform size 128k; 

create user wang identified by wang default tablespace wang; 

然後用user/user登入 

建立表 就在以上指定的表空間中了。 

create table wang (id integer,name char(10)); 

insert into t values(0,'wang'); 

commit; 

insert into t values(1,'qiupeng'); 

第二條不commit 

然後切換到sys使用者； 

然後執行 

alter tablespace wang read only; 

如果transcription沒有commit以上語句耗在這裡，commit後這個語句才執行。 

drop table wang ;這個指令可以執行。 

相當于 一個省的地域是實實在在的檔案， 省上面的行政機構是相當于 

tablespace，是一個邏輯的結構， 

這個tablespace的邏輯結構是存儲在資料字段中的，可以執行 drop table操作 

相當于對資料字典進行通路。 

10 ：讓一個表空間 offline

以下表空間不能離線：

1：SYSTEM表空間

2：處于active的 undo segment 的表空間

3：default temporary 表空間不能離線 ,temporary 表空間如果不是default的就可以離線。

ALTER TABLESPACE mytemp OFFLINE; 

ALTER TABLESPACE mytemp ONLINE; 

實驗：

create table tt(id integer,name char(10)); 

insert into tt values(0,'wang'); 

select * from tt; 

insert into tt values(1 ,'bbbbb'); 

這個時候第二條記錄還沒有commit； 

這意味在undo segment 有一個 active的。 

如果表空間離線，就不能對這個表空間内的表做操作。 

11： 變大和變小 表空間；（這個很有現實意義）

有二種方式：

1：表空間可以自動變大 建立表空間的時候 可以設定 AUTOEXTEND ON實作。

2：可以通過在表空間中加入新的資料檔案來增加表空間。

1：查詢表空間的使用情況：

DBA_DATA_FILES 這個表記錄了所有資料檔案情況。

DBA_FREE_SPACE　能夠知道還剩餘多少表空間。（如果是離線狀态不能查出。）

google 搜尋 ： oracle tablespace usage sql 

這個ｓｑｌ能夠查詢出資料庫的所有表空間的使用情況；

注意這是一個很重要的sql：

SELECT /* + RULE */ df.tablespace_name "Tablespace", 

df.bytes / (1024 * 1024) "Size (MB)", 

SUM(fs.bytes) / (1024 * 1024) "Free (MB)", 

Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", 

Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" 

FROM dba_free_space fs, 

(SELECT tablespace_name,SUM(bytes) bytes 

FROM dba_data_files 

GROUP BY tablespace_name) df 

WHERE fs.tablespace_name (+) = df.tablespace_name 

GROUP BY df.tablespace_name,df.bytes 

UNION ALL 

SELECT /* + RULE */ df.tablespace_name tspace, 

fs.bytes / (1024 * 1024), 

SUM(df.bytes_free) / (1024 * 1024), 

Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1), 

Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) 

FROM dba_temp_files fs, 

(SELECT tablespace_name,bytes_free,bytes_used 

FROM v$temp_space_header 

GROUP BY tablespace_name,bytes_free,bytes_used) df 

GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used 

ORDER BY 4 DESC; 

在我資料庫上的執行結果：

Tablespace Size (MB) Free (MB) % Free % Used 

------------------------------ ---------- ---------- ---------- ---------- 

TS_MYDB 500 499.9375 100 0 

MY_SPACE 500 499.9375 100 0 

PENG 5 4.9375 99 1 

MYTEMP 100 99 99 1 

UNDOTBS1 365 335.6875 92 8 

USER_TEMP 500 338 68 32 

EXAMPLE 100 22.3125 22 78 

USERS 9707.0625 462.8125 5 95 

SYSAUX 821.25 41.9375 5 95 

SYSTEM 710 1.125 0 100 

TEMP 27 0 0 100 

12 ：三種方法擴充表空間

方法一： 自動擴充表空間

三種方式： 

1： create database 指定 datafile 的時候可以增加選項 AUTOEXTEND ON NEXT 10M MAXSIZE 500M 這樣資料庫檔案就能自動增長。

2： create tablespace 這個AUTOEXTEND ON NEXT 10M MAXSIZE 500M 也可以用在create tablespace。

3：ALTER TABLESPACE ADD DATAFILE 

下面用第三種方式做實驗：（修改的表空間必須實作，這個my_）

SQL> alter tablespace MY_SPACE add datafile 'D:\APP\TOPWQP\ORADATA\ORCL\mytemp 

001.DBF' size 20M autoextend on next 10M maxsize 100M; 

表空間已更改。 

增加以後可以到 dba_data_files;表中看是否為自動增加：

desc dba_data_files; 

col file_name format a20; 

select file_name , tablespace_name,autoextensible from dba_data_files; 

SQL> select file_name , tablespace_name,autoextensible from dba_data_files; 

FILE_NAME TABLESPACE_NAME AUT 

-------------------- ------------------ --- 

D:\APP\TOPWQP\ORADAT USERS YES 

A\ORCL\USERS01.DBF 

D:\APP\TOPWQP\ORADAT UNDOTBS1 YES 

A\ORCL\UNDOTBS01.DBF 

D:\APP\TOPWQP\ORADAT SYSAUX YES 

A\ORCL\SYSAUX01.DBF 

D:\APP\TOPWQP\ORADAT SYSTEM YES 

A\ORCL\SYSTEM01.DBF 

D:\APP\TOPWQP\ORADAT EXAMPLE YES 

A\ORCL\EXAMPLE01.DBF 

D:\APP\TOPWQP\ORADAT MY_SPACE YES 

A\ORCL\MY_SPACE.DBF 

D:\APP\TOPWQP\ORADAT TS_MYDB YES 

A\ORCL\TS_MYDB.DBF 

D:\APP\TOPWQP\ORADAT PENG NO 

A\ORCL\PENG01.DBF 

A\ORCL\MYTEMP001.DBF 

已選擇9行。 

可以看到 剛增加的資料檔案 是否為自動增長 autoextensible 這個屬性顯示 ；

如果不想新增加 資料檔案的方式擴充表空間，可以使用： 如下指令 對原來的資料檔案設定為自動擴充來擴充表空間（道理一樣）

alter database datafile '資料檔案位置+資料檔案名' autoextend on next 10M maxsize 100M 

方法二：重新定義表空間中的資料檔案的大小 

手工的讓資料檔案 變大變小：使已經存在的資料檔案變大變小，

example：

ALTER DATABASE DATAFILE ' ' RESIZE 200M

這個需要考慮目前需要改變的資料檔案的大小。 

col file_name format a40; 

col tablespace_name format a15; 

select file_name, tablespace_name,byte from dba_data_files; 

注意臨時表空間在： DBA_TEMP_FILES 這個表中去找。

先查詢出，然後執行，修改資料檔案大小，然後再次進行查詢。

如果修改的大小小于真實的資料檔案的大小，這樣oracle就會報錯。

方法三： 增加 資料檔案 到 表空間。 這樣表空間 就增加了。

alter tablespace my_temp add datafile ' ' SIZE 200M

如何移動DATAFILE:

方法一：

使用alter tablespace 指令：

做之前必須做兩個工作：

1：讓tablespace offline

2：目标資料檔案必須存在。

指令：

首先讓這個檔案離線：

alter tablespace tablespace_name offline;

然後移動檔案

然後 執行如下指令：

ALTER TABLESPACE tablespace_name RENAME DATAFILE ' ' TO ' ';

相當于指針重新進行了指向了；

然後把表空間上線： 

alter tablespace tablespace_name online;

删除表空間：

1： SYSTEM 表空間 不能删除

2： 有一個 active segments 不能删除。

指令： DROP TABLESAPCE tablespace_name INCLUDING CONTENTS AND DATAFILES;

有三種可選項。

如上指令可以執行， 相應的表空間中的 datafile也被幹掉了。