oracle維護資料表空間和資料檔案

文章目錄

• 方法一： 自動擴充資料表空間
• 方法二：重新定義資料表空間中的資料檔案的大小 
• 方法三： 增加 資料檔案 到 資料表空間。 這樣資料表空間 就增加了。

1：重要參考 wiki

2： oracle doc 資料表空間參考

3：來自dba-oracle的參考

26,27,28,29

一： 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> CREATE TABLESPACE  peng DATAFILE 'D:\APP\TOPWQP\ORADATA\ORCL\peng01.DBF'  SIZE 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 a40SQL>SQL> select file_name ,tablespace_name from dba_data_files;FILE_NAME                                TABLESPACE_NAME---------------------------------------- ------------------D:\APP\TOPWQP\ORADATA\ORCL\USERS01.DBF   USERSD:\APP\TOPWQP\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1D:\APP\TOPWQP\ORADATA\ORCL\SYSAUX01.DBF  SYSAUXD:\APP\TOPWQP\ORADATA\ORCL\SYSTEM01.DBF  SYSTEMD:\APP\TOPWQP\ORADATA\ORCL\EXAMPLE01.DBF EXAMPLED:\APP\TOPWQP\ORADATA\ORCL\MY_SPACE.DBF  MY_SPACED:\APP\TOPWQP\ORADATA\ORCL\TS_MYDB.DBF   TS_MYDBD:\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 LOCALSYSAUX             PERMANENT LOCALUNDOTBS1           UNDO      LOCALTEMP               TEMPORARY LOCALUSERS              PERMANENT LOCALEXAMPLE            PERMANENT LOCALUSER_TEMP          TEMPORARY LOCALMY_SPACE           PERMANENT LOCALTS_MYDB            PERMANENT LOCALPENG               PERMANENT LOCAL已選擇10行。

 8：修改預設資料表空間

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

SQL> create temporary tablespace mytemp  tempfile 'D:\APP\TOPWQP\ORADATA\ORCL\mytemp.DBF'  size 100M extent  management local;資料表空間已建立。SQL>SQL> ALTER DATABASE DEFAULT TEMPORYARY TABLESPACE mytemp;ALTER DATABASE DEFAULT TEMPORYARY TABLESPACE mytemp                       *第 1 行出現錯誤:ORA-02231: ALTER DATABASE 選項缺失或無效SQL>SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE mytemp;資料庫已更改。SQL>

預設暫存資料表空間就修改了，可以通過如下命令查詢：

SQL> select  property_name,property_value  from  database_properties;PROPERTY_NAME        PROPERTY_VALUE-------------------- --------------------DICT.BASE            2DEFAULT_TEMP_TABLESP MYTEMPACEDEFAULT_PERMANENT_TA USERSBLESPACE

一個資料庫可以有多個暫存資料表空間，但是只有一個default的資料表空間。

9： 資料表空間唯讀設定：

ALTER TABLESPACE  mytemp READ ONLY;

執行這條語句：oracle將做如下操作：
1:引起　checkpoint;
2:資料變成唯讀操作
3：依然可以從資料表空間中刪除一個object(table index 等)　

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

實驗： 建立一個資料表空間：create tablespace wang datafile '/檔案名稱/' size 100Mextent 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');commit;select * from  tt;insert into tt values(1 ,'bbbbb');select * from  tt;這個時候第二條記錄還沒有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.bytesUNION ALLSELECT /* + 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 WHERE fs.tablespace_name (+)  = df.tablespace_name 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          0MY_SPACE                              500   499.9375        100          0PENG                                    5     4.9375         99          1MYTEMP                                100         99         99          1UNDOTBS1                              365   335.6875         92          8USER_TEMP                             500        338         68         32EXAMPLE                               100    22.3125         22         78USERS                           9707.0625   462.8125          5         95SYSAUX                             821.25    41.9375          5         95SYSTEM                                710      1.125          0        100TEMP                                   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\mytemp001.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              YESA\ORCL\USERS01.DBFD:\APP\TOPWQP\ORADAT UNDOTBS1           YESA\ORCL\UNDOTBS01.DBFD:\APP\TOPWQP\ORADAT SYSAUX             YESA\ORCL\SYSAUX01.DBFD:\APP\TOPWQP\ORADAT SYSTEM             YESA\ORCL\SYSTEM01.DBFD:\APP\TOPWQP\ORADAT EXAMPLE            YESA\ORCL\EXAMPLE01.DBFD:\APP\TOPWQP\ORADAT MY_SPACE           YESA\ORCL\MY_SPACE.DBFD:\APP\TOPWQP\ORADAT TS_MYDB            YESA\ORCL\TS_MYDB.DBFD:\APP\TOPWQP\ORADAT PENG               NOA\ORCL\PENG01.DBFD:\APP\TOPWQP\ORADAT MY_SPACE           YESA\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也被幹掉了。