oracle数据文件位置迁移状态

SELECT tablespace_name,status FROM dba_tablespaces

数据迁移时会将数据文件改成READ ONLY
文件状态改成offline

修改成online之前，需要先赋予权限read write;
alter tablespace tablespace_name read write;

alter tablespace SALES_S online;

select file#, name, status from v$datafile;
《11g Concept》

《11g Administrator’s Guide》

2.修改表空间为Offline：
SQL> alter tablespace users offline;

3.拷贝表空间文件
cp users01.dbf /u01/oracle/oradata/yoondata/
拷贝 C:\oracle\product\10.2.0\oradata\orcl\USERS01.DBF 到 D:\oracledata\orcl\USERS01.DBF

4.修改oracle表空间指向地址

alter database rename file ‘原路径\USERS01.DBF' to '文件新路径\USERS01.DBF'; 
SQL> alter database rename file 'C:\oracle\product\10.2.0\oradata\orcl\USERS01.DBF' to 'D:\oracledata\orcl\USERS01.DBF'

5. 手动删除表空间物理文件
   删除c:下的USERS01.DBF文件，并且以后数据全部会放在D:\oracledata

5.修改表空间为Online
SQL> alter tablespace users online;

---

alter tablespace SALES_S offline;

cp sales_s46.dbf /oracle/oradata3/

alter database move datafile ‘/oracle/oradata2/SDH/data/sales_s46.dbf’ to ‘/oracle/oradata3/sales_s46.dbf’;

rm -rf /oracle/oradata2/SDH/data/sales_s46.dbf

alter tablespace SALES_S online;

alter database datafile ‘/oracle/oradata2/SDH/data/sales_s28.dbf’ offline;

alter database move datafile ‘/oracle/oradata2/SDH/data/sales_s46.dbf’ to ‘/oracle/oradata3/sales_s46.dbf’;

alter database datafile ‘/oracle/oradata2/SDH/data/sales_s28.dbf’ online;