管理表空间（九）--迁移表空间数据文件

 

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';

Tablespace altered.

SQL> alter tablespace text online;

Tablespace altered.

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 /disk1/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.

2、mount 状态

（正常关库——启动到mount状态——执行改名语句——起库到open状态）

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

SQL> !

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

[oracle@solaris10 ~]$exit

exit

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;

Database altered.

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.