先移test_tbs 表空间 test_tbs.dbf文件

SYS@PROD1 > col name for a60

SYS@PROD1 > select name,file# from v$datafile;

NAME FILE#

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

/u01/app/oracle/oradata/PROD1/system01.dbf 1

/u01/app/oracle/oradata/PROD1/sysaux01.dbf 2

/u01/app/oracle/oradata/PROD1/undotbs01.dbf 3

/u01/app/oracle/oradata/PROD1/users01.dbf 4

/u01/app/oracle/oradata/PROD1/example01.dbf 5

/u01/app/oracle/oradata/PROD1/indx.dbf 6

/u01/app/oracle/oradata/PROD1/TOOLS.dbf 7

/u01/app/oracle/oradata/PROD1/TEST.dbf 8

/u01/app/oracle/oradata/PROD1/EXAMPLE.dbf 9

/u02/oradata/prod1/test_tbs.dbf 10

10 rows selected.

先把数据文件offline

SYS@PROD1 > alter database datafile 10 offline;

Database altered.

操作系统上拷贝数据文件过去，没问题后再删除原文件

修改控制文件中数据文件信息

SYS@PROD1 > alter tablespace test_tbs rename datafile '/u02/oradata/prod1/test_tbs.dbf' to '/u01/app/oracle/oradata/PROD1/test_tbs.dbf';

Tablespace altered.

查看是否更改完成

SYS@PROD1 > select file_name,file_id,tablespace_name from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME

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

/u01/app/oracle/oradata/PROD1/users01.dbf 4 USERS

/u01/app/oracle/oradata/PROD1/undotbs01.dbf 3 UNDOTBS1

/u01/app/oracle/oradata/PROD1/sysaux01.dbf 2 SYSAUX

/u01/app/oracle/oradata/PROD1/system01.dbf 1 SYSTEM

/u01/app/oracle/oradata/PROD1/example01.dbf 5 EXAMPLE_OLD

/u01/app/oracle/oradata/PROD1/indx.dbf 6 INDX

/u01/app/oracle/oradata/PROD1/TOOLS.dbf 7 TOOLS

/u01/app/oracle/oradata/PROD1/TEST.dbf 8 TEST

/u01/app/oracle/oradata/PROD1/EXAMPLE.dbf 9 EXAMPLE

/u01/app/oracle/oradata/PROD1/test_tbs.dbf 10 TEST_TBS

10 rows selected.

SYS@PROD1 >

ITPub博客

在线迁移表空间数据文件