[Oracle學習筆記] 基本資料表空間的建立，修改和刪除

一，建立資料表空間文法：

CREATE [TEMPORARY | UNDO] TABLESPACE tablespace_name

[DATAFILE | TEMPFILE file_name SIZE K | M [REUSE] [AUTOEXTENT OFF｜ON [NEXT number K | M  MAXSIZE UNLIMITED | 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]

 

1. TEMPORARY | UNDO 表明資料表空間用途，暫存資料表空間/撤銷資料表空間，不指定則標識基本資料表空間。

2. REUSE 檔案存在則清除該檔案，並重新建立，如果未使用，則當檔案存在時將報錯

3. BLOCKSIZE  建立非標準資料區塊資料表空間

4. DEFAULT STORAGE 只適用於資料字典管理的資料表空間，一般不用

5. EXTENT MANAGEMENT  盤區管理方式，一般選用LOCAL，字典管理方式儲存效率不高，難以管理以及易造成磁碟片段

6. AUTOALLOCATE，UNIFORM SIZE，盤區管理方式為LOCAL時適用， AUTOALLOCATE表示盤區大小由系統自動分配， UNIFORM設定盤區大小，所有盤區大小相同

7. SEGMENT SPACE MANAGEMENT 段管理方式，一般選用AUTO，這時Oracle採用位元影像來管理段中已用塊和空閑塊，如果選用MANUAL，Oracle使用可用列表來管理段中已用塊和空閑塊 

樣本：

SQL> create tablespace tls01
  2  datafile 'f:\tls01.dbf' size 20m autoextend on next 10m maxsize unlimited;

通過dba_tablespaces查詢資料表空間屬性：

SQL> desc dba_tablespaces;
 名稱                                      是否為空白? 類型
 ----------------------------------------- -------- ---------------------------

 TABLESPACE_NAME                      NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                           NUMBER
 NEXT_EXTENT                              NUMBER
 MIN_EXTENTS                               NOT NULL NUMBER
 MAX_EXTENTS                              NUMBER
 PCT_INCREASE                             NUMBER
 MIN_EXTLEN                                 NUMBER
 STATUS                                        VARCHAR2(9)
 CONTENTS                                    VARCHAR2(9)
 LOGGING                                      VARCHAR2(9)
 FORCE_LOGGING                           VARCHAR2(3)
 EXTENT_MANAGEMENT                   VARCHAR2(10)
 ALLOCATION_TYPE                         VARCHAR2(9)
 PLUGGED_IN                                 VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT     VARCHAR2(6)
 DEF_TAB_COMPRESSION                VARCHAR2(8)
 RETENTION                                    VARCHAR2(11)
 BIGFILE                                         VARCHAR2(3)

樣本：

SQL> select tablespace_name, extent_management, segment_space_management
  2  from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM                         LOCAL      MANUAL
UNDOTBS1                     LOCAL      MANUAL
SYSAUX                         LOCAL      AUTO
TEMP                             LOCAL      MANUAL
USERS                           LOCAL      AUTO
TLS01                           LOCAL      AUTO

已選擇6行。

 

二，修改資料表空間

1. 添加資料檔案

ALTER TABLESPACE tablespace_name

ADD DATAFILE datafile_specification

樣本：

SQL> alter tablespace tls01
  2  add datafile 'f:\tls02.dbf' size 20m;

 

2. 修改資料檔案大小

首先查詢dba_data_files瞭解資料檔案的位置，大小等資訊

SQL> desc dba_data_files;
 名稱                                      是否為空白? 類型
 ----------------------------------------- -------- -----------------------

 FILE_NAME                                       VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                           VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                            NUMBER
 STATUS                                            VARCHAR2(9)
 RELATIVE_FNO                                  NUMBER
 AUTOEXTENSIBLE                             VARCHAR2(3)
 MAXBYTES                                        NUMBER
 MAXBLOCKS                                     NUMBER
 INCREMENT_BY                                 NUMBER
 USER_BYTES                                    NUMBER
 USER_BLOCKS                                  NUMBER
 ONLINE_STATUS                               VARCHAR2(7)

SQL> select file_name, file_id, bytes from dba_data_files
  2  where tablespace_name='TLS01';

FILE_NAME        FILE_ID          BYTES
---------- ---------------------------------
F:\TLS01.DBF         5             20971520

F:\TLS02.DBF         6              20971520

注意：資料字典裡資料表空間名是大寫

修改資料檔案大小：

ALTER DATABASE DATAFILE file_name RESIZE size

樣本：

SQL> alter database datafile 'f:\tls01.dbf' resize 50m;

3. 修改資料檔案自動擴充

SQL> alter database datafile 'f:\tls01.dbf'  autoextend on;

同樣適用於ALTER TABLESPACE ADD DATAFILE

4. 修改資料表空間狀態

資料表空間狀態：OFFLINE, ONLINE, READ ONLY, READ WRITE

SQL> alter tablespace tls01 offline;

5. 移動資料檔案

如果資料檔案所在磁碟空間不足，則需要將資料檔案移到另一個磁碟，方法是：

1. 修改資料表空間狀態offline

2. 複製資料檔案到另一個磁碟

3. 使用ALTER TABLESPACE RENAME語句

4. 恢複資料表空間狀態online

樣本：

1) SQL> alter tablespace tls01 offline;

2) 通過作業系統命令複製或剪貼資料檔案f:\tls01.dbf到e:\tls01.dbf

3) SQL> alter tablespace tls01
  2  rename datafile 'f:\tls01.dbf' to 'e:\tls01.dbf';

4) SQL> alter tablespace tls01 online;

重新查詢dba_data_files，資料檔案路徑發生了變化

SQL> column file_name format a50

SQL> select file_id, file_name from dba_data_files
  2  where tablespace_name='TLS01';

   FILE_ID FILE_NAME
---------- --------------------------------------------------
         5 E:\TLS01.DBF
         6 F:\TLS02.DBF

 

三、刪除資料表空間

DROP TABLESPACE tablespace_name

| [INCLUDING CONTENTS]

| [INCLUDING CONTENTS AND DATAFILES]

樣本：

SQL> drop tablespace tls01
  2  including contents and datafiles;

資料表空間已刪除。