Oracle 11g 學習3——資料表空間操作

標籤：

 一、資料表空間概述

         資料表空間是Oracle中最大的邏輯儲存結構，與作業系統中的資料檔案相對應；

         基本資料表空間：一般指使用者使用的永久性資料表空間，用於儲存使用者的永久性資料
         暫存資料表空間： 主要用於儲存排序或匯總過程中產生的臨時資料；
         大檔案資料表空間：用於儲存大型資料（例如LOB）
         非標準資料區塊資料表空間：用於在一個資料庫執行個體中建立資料區塊大小不同的資料表空間；
         撤銷資料表空間：用於儲存事務的撤銷資料，在資料恢複時使用。

二、建立資料表空間

文法：
         CREATE [ TEMPORARY | UNDO ] TABLESPACE tablespace_name
         [ DATAFILE | TEMPFILE  ‘file_name‘  SIZE  size  K|M  [ REUSE ] ]
         [ AUTOEXTEND  OFF|ON
         [ NEXT number K|M MAXSIZE NULIMITED|number K|M ]
               ][,...]
         [ MININUM EXTENT number K|M ]
         [ BLOCKSIZE number K ]
         [ ONLINE|OFFLINE ]
         [ LOGGING|NOLOGGING ]
         [ FORCE LOGGING ]
         [ DEFAULT STORAGE storage ]
         [ COMPRESS|NOCOMPRESS ]
         [ PERMANENT|TEMPORARY ]
         [ EXTENT MANAGEMENT DICTIONARY | LOCAL
             [ AUTOALLOCATE|UNIFORM SIZE number K|M ] ]
         [ SEGMENT SPACE MANAGEMENT AUTO|MANUAL ];

說明：
        TEMPORARY:指定資料表空間為暫存資料表空間
        UNDO:指定資料表空間為撤銷資料表空間
        如果不指定TEMPORARY或UNDO:表示指定的是基本資料表空間
        tablespace_name:資料表空間的名稱
        DATAFILE:指定是基本資料表空間時，為資料表空間指定資料檔案
        TEMPFILE:指定是暫存資料表空間時，為資料表空間指定臨時檔案
        REUSE：標識檔案已經存在

//對檔案的設定
        AUTOEXTEND ：標識檔案是否自動擴充
        NEXT:標識檔案下次擴充的大小
        MAXSIZE:標識檔案的最大容量，UNLIMITED：標識大小不受限制
        MINIMUM EXTENT:標識盤區可以分配的最小容量
        BLOCKSIZE:標準資料區塊大小（只能用於標準資料表空間）
        ONLINE:  標識建立的資料表空間立即可用   OFFLINE:不能立即使用
        LOGGING: 組建記錄檔記錄項             NOLOGGING:不組建記錄檔記錄項
        FORCE LOGGING:強制日誌記錄項
        DEFAULT STORAGE:資料庫物件預設的儲存物件
        COMPRESS: 壓縮資料     NOCOMPRESS:不壓縮
        PERMANENT: 持久儲存資料對象      TEMPORARY:臨時儲存資料對象
        EXTENT MANAGEMENT DICTIONARY:資料字典的管理方式為資料字典管理方式
        LOCAL:資料字典的管理方式為本地化管理方式
        AUTOALLOCATE:LOCAL管理方式時，盤區大小自動分配
        UNIFORM SIZE :LOCAL管理方式時，盤區大小均勻分配，可以指定大小
        SEGMENT SPACE MANAGEMENT :標識資料表空間中段的管理方式
        AUTO:自動管理
        MANUAL:人工管理

例如：
CREATE TABLESPACE myspace
DATAFILE ‘E:\app\myspace\myspace.dbf‘

SIZE 10M AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

三、資料表空間狀態設定
資料表空間的狀態屬性主要有:線上（ONLINE）、離線（OFFLINE）、唯讀（READ ONLY）和讀寫（READ WRITE）這4種。
通過設定資料表空間的狀態屬性，可以對錶空間的使用進行管理。


//1、線上
        當資料表空間的狀態為ONLINE時，才允許訪問該資料表空間中的資料

例如：
        //修改資料表空間的狀態為ONLINE
        ALTER TABLESPACE tablespace_name ONLINE;

//2、離線
        OFFLINE狀態，不允許訪問該資料表空間中的資料。這時可以對錶空間進行離線備份，也可以對應用程式進行升級和維護等。

例如：
        //將資料表空間狀態修改為離線狀態
        ALTER TABLESPACE tablespace_name OFFLINE parameter;

說明：
        parameter表示將資料表空間切換為OFFLINE狀態可以使用的參數：
        NORMAL:  正常方式切換，預設
        TEMPORARY:臨時方式，Oracle在檢查時不會檢查資料檔案是否可用
        IMMEDIATE:立即方式，Oracle不會執行檢查點
        FOR RECOVER:以恢複方式，常用於基於時間恢複資料庫

//3、唯讀
        只能讀取資料，不能進行任何更新或刪除操作，目的是為了保證資料表空間的資料安全

例如：
        //將資料表空間設定為READ ONLY
        ALTER TABLESPACE tablespace_name READ ONLY;

說明：
        將資料表空間設定為READ ONLY之前的注意事項：
        -- 資料表空間必須處於ONLINE狀態
        -- 資料表空間不能包含任何事務的回退段
        -- 資料表空間不能正處於線上Database Backup期間

//4、讀寫
        可以對錶空間進行正常訪問

例如：
        //修改資料表空間為READ WRITE狀態
        ALTER TABLESPACE tablespace_name READ WRITE；

注意：修改資料表空間的狀態為READ WRITE，也需要保證資料表空間處於ONLINE狀態。


        //查看錶空間的狀態
        select tablespace_name, status from dba_tablespaces;

四、修改資料表空間

（1）、修改 資料表空間的大小

        增加資料表空間的大小，具體實現時，可以採用增加資料檔案的大小，也可以增加一個新的資料檔案來增大資料表空間。

1**、修改資料表空間的大小
       基本資料表空間的大小等於所有與其關聯的資料檔案的大小之和。當資料表空間大小不足時，需要增加資料表空間大小，具體實現，可以採用增加資料檔案的大小，也可以增加一個新的資料問價來增大資料表空間。


//修改資料表空間中資料檔案的大小
文法：
ALTER DATABASE DATAFILE file_name RESIZE newsize K|M;

說明：
file_name:資料檔案的名稱和路徑
RESIZE newsize:修改資料檔案的大小為newsize。

例子：
ALTER DATABASE DATAFILE ‘E:\APP\MYSPACMYSPACE.DBF‘ RESIZE 30M;

2**、增加資料表空間的資料檔案
文法：
ALTER TABLESPACE tablespace_name
ADD DATAFILE file_name SIZE number K|M
[   AUTOEXTEND OFF|ON
    [NEXT number K|M MAXSIZE UNLIMITED|number K|M ]
][,...];

例子：
ALTER TABLESPACE myspace ADD DATAFILE ‘E:\app\myspace\myspace02.dbf‘ SIZE 100M;

（2）、重新命名資料表空間

修改資料表空間的名稱，不會影響到資料表空間中的資料，但不能修改系統資料表空間system與sysaux的名稱。
文法：
   ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;
說明：
注意：如果資料表空間的狀態為OFFLINE,則無法重新命名該資料表空間。

（3）、刪除資料表空間

文法：
DROP TABLESPACE tablespace_name
[INCLUDING CONTENTS [AND DATAFILES ]]

說明：
INCLUDING CONTENTS:
表示刪除資料表空間的同時，刪除資料表空間中的所有資料庫物件。如果資料表空間中有資料庫物件，則必須使用此選項。

AND DATAFILES
表示刪除資料表空間的同時，刪除資料表空間所對應的資料檔案。如果不使用此選項，則刪除資料表空間實際上僅是從資料字典和控制檔案中將該資料表空間的有關資訊刪除，而不會刪除作業系統中與該資料表空間對應的資料檔案。

例如：
DROP TABLESPACE USERSPACE
INCLUDING CONTENTS AND DATAFILES;

五、修改資料表空間中資料檔案

（1）、修改資料表空間資料檔案的自動擴充性
文法：
   ALTER DATABASE DATAFILE file_name AUTOEXTEND OFF|ON
[ NEXT number K|M MAXSIZE UNLIMITED|number K|M ]

例如：
ALTER DATABASE DATAFILE ‘E:\APP\MYSPACE\MYSPACE02.DBF‘
AUTOEXTEND ON
NEXT 5M MAXSIZE 50M;

（2）、修改資料表空間中資料檔案的狀態
資料檔案主要有3種狀態：ONLINE、OFFLINE和OFFLINE DROP。
ONLINE:表示聯機狀態，此時資料檔案可以使用
OFFLINE:表示離線狀態，此時資料檔案不可使用，用於資料庫運行在歸檔模式下的情況
OFFLINE DROP:會刪除資料檔案，與OFFLINE一樣用於設定資料檔案不可用，但他用於資料庫運行在非歸檔模式下的情況。

註：將資料檔案設定為OFFLINE狀態時，不會影響到資料表空間的狀態；但是將資料表空間設定為OFFLINE狀態時，屬於該資料表空間的所有資料檔案都被設定為OFFLINE狀態。

文法：
ALTER DATABASE
DATAFILE file_name ONLINE|OFFLINE|OFFLINE DROP

例子：
ALTER DATABASE DATAFILE ‘E:\APP\MYSPACE\MYSPACE02.DBF‘ OFFLINE;

（3）、移動資料表空間中的資料檔案
資料檔案的大小受所在磁碟空間大小的限制，當資料檔案所在的磁碟空間不夠時，需要將資料檔案移動到新的磁碟中儲存。

方法：
   第一步：將相應的資料表空間設定為離線狀態
   第二步：進入磁碟，移動資料檔案到新的位置，也可以修改檔案名稱
   第三步：重新命名資料檔案
例如：ALTER TABLESPACE myspace
      RENAME DATAFILE ‘E:\APP\MYSPACE\MYSPACE02.DBF‘
      TO ‘D:\ORACLEFILE\MYSPACE03.DBF‘
   第四步：將相應的資料表空間設定為線上狀態

六、暫存資料表空間

//建立和修改暫存資料表空間
暫存資料表空間是一個磁碟空間，主要用於儲存使用者在執行ORDER BY等語句進行排序或匯總時產生的臨時資料。預設情況下，所有使用者都使用temp作為預設暫存資料表空間。但是也允許使用其他暫存資料表空間作為預設暫存資料表空間，這需要在建立使用者時指定。
   建立暫存資料表空間時需要使用TEMPORARY關鍵字，並且與暫存資料表空間對應的是臨時檔案，由TEMPFILE關鍵字指定，也就是說暫存資料表空間中不再使用資料檔案，而使用臨時檔案。

例子：
CREATE TEMPORARY TABLESPACE mytemp
TEMPFILE ‘E:\app\myspace\mytemp.dbf‘
SIZE 5M AUTOEXTEND ON NEXT 2M MAXSIZE 20M;

//修改暫存資料表空間
由於臨時檔案中不儲存永久性資料，只儲存排序等操作過程中產生的臨時資料，並且在使用者操作結束後，臨時檔案中儲存的資料由系統刪除，所以一般情況下不需要調整暫存資料表空間，但是當並發使用者特別多，並且操作比價複雜時，可能會發生暫存資料表空間不足。這時，資料庫管理員可以增加臨時檔案來增大暫存資料表空間。
    如果需要增加臨時檔案，可以使用ADD TEMPFILE子句。
    如果需要修改臨時檔案的大小，可以使用RESIZE關鍵字。
    還可以修改臨時檔案的狀態為OFFLINE或ONLINE。

//暫存資料表空間組
是指對多個暫存資料表空間組成的集合，針對集合操作
在Oracle 11g中，使用者可以建立暫存資料表空間組，一個暫存資料表空間組中可以包含一個或多個暫存資料表空間。
暫存資料表空間組主要特徵如下:
** 一個暫存資料表空間組必須由至少一個暫存資料表空間組成，並且無明確地最大數量限制。
** 如果刪除一個暫存資料表空間組的所有成員，該組也自動被刪除。
** 暫存資料表空間的名字不能與暫存資料表空間組的名字相同。
** 在給使用者指派一個暫存資料表空間時可以使用暫存資料表空間組的名字代替實際的暫存資料表空間名；在給資料庫分配預設暫存資料表空間時，也可以使用暫存資料表空間組的名字。

使用暫存資料表空間組的優點：
** 由於SQL查詢可以並發使用幾個暫存資料表空間進行排序操作，因此SQL查詢很少會出現排序空間超出，避免暫存資料表空間不足所引起的磁碟排序問題。
** 可以在資料庫級指定多個預設暫存資料表空間。
** 一個並行操作的並行伺服器將有效地利用多個暫存資料表空間
** 一個使用者在不同會話中可以同時使用多個暫存資料表空間。

//操作暫存資料表空間組
1、建立暫存資料表空間組
只需要在建立暫存資料表空間時，使用TABLESPACE GROUP語句為其制定一個組即可。

例子：
    CREATE TEMPORARY TABLESPACE tempgroup
TEMPFILE ‘E:\app\myspace\tempgroup01.dbf‘ SIZE 5M
TABLESPACE GROUP group01;

2、查看暫存資料表空間組資訊
資料字典dba_tablespace_groups

例子：
select * from dba_tablespace_groups;

3、移動暫存資料表空間
使用ALTER TABLESPACE 語句

例子：
ALTER TABLESPACE tempgroup TABLESPACE GROUP group02;

4、刪除暫存資料表空間組

七、大檔案資料表空間

//大檔案資料表空間
大檔案資料表空間是Oracle 10g引入的一個新資料表空間類型，主要用於解決隱藏檔大小不夠的問題。與普通資料表空間不同的是，大檔案資料表空間只能對應唯一一個資料檔案或臨時檔案，而普通資料表空間則可以最多對應1022個資料檔案或臨時檔案。
    雖然大檔案資料表空間只能對應一個資料檔案或臨時檔案，但其對應的檔案可達4G個資料區塊大小。而普通資料表空間對應的檔案最大可達4M個資料區塊大小。

//建立大檔案資料表空間
使用BIGFILE關鍵字，而且只能為其制定一個資料檔案或臨時檔案
   普通資料表空間一般使用SMALLFILE關鍵字表示，預設省略。
通過資料字典database_properties可以瞭解當前資料庫預設的資料表空間類型。

例如：
CREATE BIGFILE TABLESPACE mybigspace
DATAFILE ‘E:\app\myspace\bigspace.dbf‘
SIZE 10M;

//查看錶空間是否是大檔案資料表空間
select tablespace_name, bigfile from dba_tablespaces;

//查看當前資料庫預設的資料表空間類型
select property_name, property_value, description
from database_properties
where property_name = ‘DEFAULT_TBS_TYPE‘;

八、非標準資料區塊資料表空間
//非標準資料區塊資料表空間
非標準（資料區塊）資料表空間，是指其資料區塊大小不基於標準資料區塊大小的資料表空間。
    在建立資料表空間時，可以使用BLOCKSIZE子句，該子句用來另外設定資料表空間中的資料區塊大小，如果不指定該子句，則預設的資料區塊大小由系統初始化參數db_block_size決定。db_block_size參數指定的資料區塊大小即標準資料區塊大小，在資料庫建立之後無法再修改該參數的值。

//建立非標準資料區塊資料表空間
 Oracle 11g中允許使用者建立非標準資料區塊資料表空間，使用BLOCKSIZE子句指定資料表空間中資料區塊的大小，但是必須有資料緩衝區參數db_nk_cache_size的值與BLOCKSIZE參數的值相匹配，如下：
BLOCKSIZE        db_nk_cache_size
2KB              db_2k_cache_size
4KB              db_4k_cache_size
8KB              db_8k_cache_size
16KB             db_16k_cache_size
32KB             db_32k_cache_size

//查看錶空間的資料區塊大小
select tablespace_name, block_size from dba_tablespaces;

//建立撤銷資料表空間
例子：
//第一步，修改 db_nk_cache_size參數
ALTER SYSTEM SET DB_16K_CACHE_SIZE = 16M;

//第二步，建立非標準資料表空間
CREATE TABLESPACE blockspace
DATAFILE ‘E:\app\myspace\blockspace.dbf‘ SIZE 10M
AUTOEXTEND ON NEXT 5M
BLOCKSIZE 16K;

注意：BLOCKSIZE的值與db_nk_cache_size的參數值要對應。

九、撤銷資料表空間
//撤銷資料表空間
為了實現對資料回退、恢複、交易回復以及撤銷等操作，Oracle資料庫提供了一部分儲存空間，專門儲存撤銷記錄，將修改前的資料儲存到該空間中，所以這部分空間被稱為撤銷資料表空間。多個撤銷資料表空間可以存在於一個資料庫中，但是在任何給定的時間內只有一個撤銷資料表空間是可以獲得的。

//建立撤銷資料表空間
例如：
   create undo tablespace undotbs
   datafile ‘e:\app\myspace\undo01.dbf‘ size 20m
   autoextend on;

//修改撤銷資料表空間的資料檔案
//添加新的資料檔案
alter tablespace undotbs
add datafile ‘e:\app\muspace\undo02.dbf‘ size 10m

//修改撤銷資料表空間的資料檔案大小
alter database datafile ‘e:\app\myspace\undo02.dbf‘ resize 15m;

//設定撤銷資料表空間的資料檔案的狀態為ONLINE或OFFLINE
alter tablespace undotbs offline;

//操作撤銷資料表空間
一個資料庫中可以有多個撤銷資料表空間，但資料庫一次只能使用一個撤銷資料表空間。預設情況下，資料庫使用的是系統自動建立的undotbs1撤銷資料表空間。如果將資料庫使用的撤銷資料表空間切換成其他資料表空間，使用ALTER SYSTEM語句修改參數undo_tablespace 的值即可。切換撤銷資料表空間後，資料庫中新事務的撤銷資料將儲存在新的撤銷資料表空間中。

//切換資料表空間
alter system set undo_tablespace = undoetbs02;

在自動撤銷記錄管理方式中，可以指定撤銷資訊在提交之後需要保留的時間，以防止在長時間的查詢過程中出現snapshot too old錯誤。
   在自動撤銷管理方式下，DBA使用UNDO_RESTENTION參數，指定撤銷記錄的表劉時間。由於UNDO_RETENTION參數是一個動態參數，在Oracle執行個體的運行中，可以通過ALTER SYSTEM SET UNDO_RETENTION語句，來修改撤銷記錄保留的時間。
   撤銷記錄保留時間的單位是秒，預設值為900,即15分鐘。
   例如，將撤銷記錄的保留時間修改為10分鐘，如下：
alter system set undo_retention = 600;
show parameter undo;

//刪除撤銷資料表空間
刪除撤銷資料表空間之前，需要保證該撤銷資料表空間不是系統正在使用的資料表空間。

例如：
drop tablespace undotbs02 including contents and datafiles;

//設定預設資料表空間
在Oracle中，使用者的預設永久性資料表空間為system，預設暫存資料表空間為temp。如果所有使用者都使用預設的資料表空間，無疑會增加system與temp資料表空間的競爭性。
   Oracle允許使用自訂的資料表空間作為預設永久性資料表空間，使用自訂暫存資料表空間作為預設暫存資料表空間。

文法：
    ALTER DATABASE DEFAULT [TEMPORARY] TABLESPACE tablespace_name;

說明：
    使用TEMPORARY關鍵字，則表示設定預設暫存資料表空間；如果不使用該關鍵字，則表示設定預設永久性資料表空間。

//查詢預設資料表空間
select default_tablespace from user_users;

select property_name, property_value
from database_properties 
where property_name IN (‘DEFAULT_PERMANENT_TABLESPACE‘, ‘DEFAULT_TEMP_TABLESPACE‘);

//建立記錄檔
//建立記錄檔組
文法：
ALTER DATAFILE database_name
ADD LOGFILE [GROUP group_number]
(file_name [,file_name[,...]])
[SIZE size] [REUSE];

說明：
*GROUP group_number:為記錄檔組指定組編號
*file_name :為該組建立記錄檔成員
*SIZE number:指定記錄檔成員的大小
*REUSE :如果建立的記錄檔成員已存在，可以使用REUSE關鍵字覆蓋已存在的檔案。但是該檔案不能已經屬於其他記錄檔組。否則無法替換。

//建立記錄檔
一般是指向記錄檔組中添加日誌成員，需要使用ALTER DATABASE ... ADD LOGFILE MEMBER語句

例如：
alter database add logfile member
‘f:\oraclefile\logfile\redo03.log‘
to group 4;

//查看記錄檔資訊
select group#, member from v$logfile;

//建立記錄檔
alter database add logfile group 4
(
   ‘E:\app\myspace\redo01.log‘,
   ‘E:\app\myspace\redo02.log‘
) SIZE 10M;

Oracle 11g 學習3——資料表空間操作