环境:表空间t1
1,、将表空间t1置为offline状态
SQL> alter tablespace t1 offline;
Tablespace altered.
2、查询数据文件位置
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/system01.dbf
/u01/app/oracle/oradata/PROD1/sysaux01.dbf
/u01/app/oracle/oradata/PROD1/undotbs01.dbf
/u01/app/oracle/oradata/PROD1/users01.dbf
/u01/app/oracle/oradata/PROD1/t101.dbf
3、复制t1对应的数据文件到更改位置
$ cp t101.dbf /home/oracle/t101.dbf
4、 rename修改表空间t1数据文件为新的位置,并修改控制文件
SQL> alter tablespace t1 rename datafile '/u01/app/oracle/oradata/PROD1/t101.dbf' to '/home/oracle/t101.dbf';
Tablespace altered.
5、将表空间t1置为online状态
SQL> alter tablespace t1 online;
Tablespace altered.
6、查询表空间位置(两个视图都可以查看,效果一样)
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/system01.dbf
/u01/app/oracle/oradata/PROD1/sysaux01.dbf
/u01/app/oracle/oradata/PROD1/undotbs01.dbf
/u01/app/oracle/oradata/PROD1/users01.dbf
/home/oracle/t101.dbf
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/PROD1/system01.dbf
SYSTEM
/u01/app/oracle/oradata/PROD1/sysaux01.dbf
SYSAUX
/u01/app/oracle/oradata/PROD1/undotbs01.dbf
UNDOTBS1
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/PROD1/users01.dbf
USERS
/home/oracle/t101.dbf
T1
7、查询数据文件状态
SQL> select name,status from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/PROD1/system01.dbf
SYSTEM
/u01/app/oracle/oradata/PROD1/sysaux01.dbf
ONLINE
/u01/app/oracle/oradata/PROD1/undotbs01.dbf
ONLINE
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/PROD1/users01.dbf
ONLINE
/home/oracle/t101.dbf
ONLINE