Oracle教程之管理表空間（九）--遷移表空間資料檔案

1、在open狀态下

（1）先将表空間offline

（2）alter tablespace 表空間名 rename datafile ‘’to ‘’

（3）将表空間online

SQL> select file_id,file_name,tablespace_name,autoextensible,bytes/1024/1024 "size" from dba_data_files;

FILE_ID FILE_NAME                                          TABLESPACE_NAME AUTOEXTEN       size

---------- -------------------------------------------------- --------------- --------- ----------

5 /u01/app/oracle/oradata/anny/text01.dbf            TEXT            NO               100

4 /u01/app/oracle/oradata/anny/user01.dbf            USERS           NO               200

3 /u01/app/oracle/oradata/anny/sysaux01.dbf          SYSAUX          NO               325

2 /u01/app/oracle/oradata/anny/rtbs01.dbf            RTBS            YES              200

1 /u01/app/oracle/oradata/anny/system01.dbf          SYSTEM          NO               325

6 /u01/app/oracle/oradata/anny/lx01.dbf              LX01            NO                10

6 rows selected.

SQL> alter tablespace text offline;

Tablespace altered.

SQL> !

[oracle@solaris10 ~]$cp /u01/app/oracle/oradata/anny/text01.dbf /disk1/oradata/anny

[oracle@solaris10 ~]$exit

exit

SQL> alter tablespace text rename

2  datafile '/u01/app/oracle/oradata/anny/text01.dbf' to '/disk1/oradata/anny/text01.dbf';

SQL> alter tablespace text online;

SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;

5 /disk1/oradata/anny/text01.dbf                     TEXT            NO               100

2、mount 狀态

（正常關庫——啟動到mount狀态——執行改名語句——起庫到open狀态）

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

[oracle@solaris10 ~]$cp /disk1/oradata/anny/text01.dbf /u01/app/oracle/oradata/anny/text01.dbf

SQL> alter database rename file

2  '/disk1/oradata/anny/text01.dbf'

3  to '/u01/app/oracle/oradata/anny/text01.dbf';

Database altered.

SQL> alter database open;

SQL>  select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;

CUUG

更多oracle視訊教程請點選：http://crm2.qq.com/page/portalpage/wpa.php?uin=800060152&f=1&ty=1&aty=0&a=&from=6