Oracle教程：移動所有資料檔案

如題，最近在一個開發庫上存在硬碟空間緊張的問題，新添加了一塊盤，準備把所有的資料檔案挪到新盤上。
首先列出需要移動的資料檔案，資料檔案隸屬於資料表空間，我們從資料表空間用途可以如下分門別類：
控制檔案
System資料表空間
undo資料表空間
temporary資料表空間
redo記錄檔
user_data資料表空間

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
GTLIONS
GTLIONSTMP

SQL> select file_name,file_id,tablespace_name from dba_data_Files;
FILE_NAME                          FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ------------------------------
/u01/Oracle/10g/oradata/gt10g/users01.dbf            4 USERS
/u01/oracle/10g/oradata/gt10g/sysaux01.dbf            3 SYSAUX
/u01/oracle/10g/oradata/gt10g/undotbs01.dbf            2 UNDOTBS1
/u01/oracle/10g/oradata/gt10g/system01.dbf            1 SYSTEM
/u01/oracle/10g/oradata/gt10g/gtlions01.ora            5 GTLIONS

SQL> select file_name,file_id,tablespace_name from dba_temp_Files;

FILE_NAME                          FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ------------------------------
/u01/oracle/10g/oradata/gt10g/temp01.dbf            1 TEMP
/u01/oracle/10g/oradata/gt10g/gtlionstmp01.ora            2 GTLIONSTMP

SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/oracle/10g/oradata/gt10g/control01.ctl
/u01/oracle/10g/oradata/gt10g/control02.ctl
/u01/oracle/10g/oradata/gt10g/control03.ctl

SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/oracle/10g/oradata/gt10g/redo03.log
/u01/oracle/10g/oradata/gt10g/redo02.log
/u01/oracle/10g/oradata/gt10g/redo01.log

針對undo資料表空間，我們可以在開啟資料的狀態下直接操作：
SQL> create undo tablespace undotbs2 datafile '/u01/oracle/10g/oradata/gt10gnew/undotbs01.dbf' size 20m autoextend on;

Tablespace created.

SQL> show parameter undo_tablespace;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace              string     UNDOTBS1
SQL> alter system set undo_tablespace='undotbs2';

System altered.

SQL> show parameter undo_tablespace;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace              string     undotbs2
SQL> drop tablespace undotbs1;

Tablespace dropped.

1 2 3 4 5 下一頁