【Oracle 筆記 】表格空間 Tablespace 的設計與維護

2008.3.26

學習目標：

學會如何建立與管理Oracle資料庫的表格空間。學習範圍如下：

1. 表格資料: 可分為 
    (1) 字元/文數字資料 (Character / Alphanumeric Data)
    (2) 數值資料 (Numeric Data)
    (3) 日期 / 時間資料 (Date / Time Data)
    (4) 大型物件 (Large Object, LOB)

2. 建立表格空間 (Tablespace):

CREATE [ UNDO | TEMPORARY ] TABLESPACE 表格空間名稱
DATEFILE | TEMPFILE 'file_spec'
                          [ MINIMUM EXTENT n [ K | M ] ]
                          [ AUTOEXTEND
                                 OFF
                                 ON NEXT n KIM MAXSIZE UNLIMITED | n [ K | M ] ]
                          [ LOGGING | NOLOGGING ]
                          [ ONLINE | OFFLINE ]
                          [ DEFAULT ( storage_clause) ] ( N/A IfTEMPORARY above)
                          [ EXTENT MANAGEMENT
                                                         DICTIONARY (Must be LOVAL for TEMPFILE)
                                                         LOCAL
                                                                AUTOALLOCATE
                                                                UNIFORM [SIZE n [ K | M] ]
                           [ SEGMENT SPACE MANAGEMENT MANAUAL | AUTO ]


3. 更改表格空間屬性

ALTER TABLESPACE 表格空間名稱 表格空間屬性

4. 刪除表格空間

DROP TABLESPACE 表格空間名稱;

5. 刪除表格空間與其相關資料檔案

DROP TABLESPACE 表格空間名稱  INCLUDING CONTENTS AND DATAFILES;

---

1. 表格資料

1-1. 字元/文數字資料 (Character / Alphanumeric Data)
     ● VARCHAR2 : 儲存變動長度的資料, 可設定值為1-4000位元組 :
     ● CHAR: 儲存固定長度的資料, 可設定值為1-2000位元組
     ● NCHAR與NVARCHAR2: 存Unicode的字元

1-2. 數值資料 (Numeric Data)

     ● NUMBER
          數值範圍:  1.0 x 10 ^-130  到  1.0 x 10 ¹²⁶ (不含)
          格式: NUMBER (Precision, Scale)
          範例: 當 NUMBER(5, 2) 時, 567.3654 的儲存內容為 567.37

     ● BINARY_FLOAT: 以32 Bit 儲存資料
          數值範圍:  最小正數為1.17549E-38F, 最大正數為 3.40282E+38F

     ● BINARY_DOUBLE : 以64 Bit 儲存資料
          數值範圍:  最小正數為 2.22507485850720 E-308, 最大正數為 1.79769313486231E+308


1-3. 日期 / 時間資料 (Date / Time Data)

Oracle 10g 支援四種日期/時間資料型態:

資料型別	範圍
DATE	04-MAR-05
TIMESTAMP	04-MAR-05 02.01.55.000000 PM
TIMESTAMP WITH TIME ZONE	04-MAR-05 02.01.55.000000 PM -08:00
TIMESTAMP WITH LOCAL TIME ZONE	04-MAR-05 02.01.55.000000 PM

1-4. 大型物件 (Large Object, LOB)

2. 建立表格空間

SQL> CREATE TABLESPACE TEST01 DATAFILE
  2  'D:\oracle\product\10.2.0\oradata\TTI\DATAFILE\TEST_DB01.dbf' SIZE 30M
  3  AUTOEXTEND ON NEXT 10m MAXSIZE 1024M
  4  DEFAULT STORAGE (INITIAL 10M NEXT 1M )
  5  PERMANENT
  6  ONLINE
  7  LOGGING;
已建立表格空間.

在此, 我們來驗證此 Tablespace 是否已經被建立:

在檔案目錄中 D:\oracle\product\10.2.0\oradata\TTI\DATAFILE, 系統會產生一個 TEST_DB01.dbf 檔案:

3. 更改表格空間

SQL> ALTER TABLESPACE TEST01
  2  NOLOGGING;
已更改表格空間.

4. 更改表格空間為 ONLINE / OFFLINE

SQL> ALTER TABLESPACE TEST01
  2  OFFLINE;
已更改表格空間.

SQL> ALTER TABLESPACE TEST01
  2  ONLINE;
已更改表格空間.

5. 增加資料檔案到表格空間

SQL> ALTER TABLESPACE TEST01 ADD DATAFILE
  2  'D:\oracle\product\10.2.0\oradata\TEST_DB02.dbf' SIZE 20M REUSE
  3  AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
已更改表格空間

在此, 我們來驗證在同一個Tablespace中, 是否已經新增了另一個資料檔案 TEST_DB02.dbf:

6. 刪除表格空間

SQL> DROP TABLESPACE TEST01;

已刪除表格空間.

注意: 單用此指令時, 不能刪除當初新增的資料檔案 TEST_DB01.DBF 與 TEST_DB02.DBF.

這時就必須手動刪除此兩個資料檔案。

7. 刪除表格空間, 同時刪除資料檔案

先建立一個新的表格空間 TEST02 如下:

SQL> CREATE TABLESPACE TEST02 DATAFILE
  2  'D:\oracle\product\10.2.0\oradata\TEST_DB03.dbf' SIZE 30M
  3  AUTOEXTEND ON NEXT 20M MAXSIZE 1024M
  4  DEFAULT STORAGE (INITIAL 10M NEXT 1M)
  5  PERMANENT
  6  ONLINE
  7  LOGGING;
已建立表格空間.

接下來, 刪除表格與其相關之資料檔案:

SQL> DROP TABLESPACE TEST02 INCLUDING CONTENTS AND DATAFILES;
已刪除表格空間.

8. 建立表格 (Table)

SQL> CREATE TABLESPACE TEST03 DATAFILE
  2  'D:\oracle\product\10.2.0\oradata\TEST_DB004.dbf' SIZE 30M
  3  AUTOEXTEND ON NEXT 20M MAXSIZE 1024M
  4  DEFAULT STORAGE (INITIAL 10M NEXT 1M)
  5  PERMANENT
  6  ONLINE
  7  LOGGING;
已建立表格空間.

SQL> CREATE TABLE Employee
  2  (EmpID VARCHAR2(5),
  3   EmpName VARCHAR2(20))
  4  TABLESPACE TEST03;
已建立表格.


P.S. 如果表格空間以Oracle所提供的組態工具來設定, 那可用下列來產生新的Tablespace:

CREATE SMALLFILE TABLESPACE "TS_TRSOPTIX" LOGGING DATAFILE SIZE 128M AUTOEXTEND ON NEXT  131072K MAXSIZE UNLIMITED BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;