oracle 修改資料表空間儲存路徑

標籤：

[[email protected] ~]# more /etc/oracle-release
Oracle Linux Server release 5.7

 

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

 

1.查看資料檔案的儲存路徑：

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/yoon/system01.dbf
/u01/oracle/oradata/yoon/sysaux01.dbf
/u01/oracle/oradata/yoon/undotbs01.dbf
/u01/oracle/oradata/yoon/users01.dbf

 

2.修改資料表空間為offline：

SQL> alter tablespace users offline;

Tablespace altered.

 

3.拷貝資料檔案

[[email protected] yoon]$ cp users01.dbf /u01/oracle/oradata/yoondata/

 

4.修改資料表空間儲存路徑

SQL> alter tablespace users rename datafile ‘/u01/oracle/oradata/yoon/users01.dbf‘ to ‘/u01/oracle/oradata/yoondata/users01.dbf‘;

Tablespace altered.

 

5.修改資料表空間為online

SQL> alter tablespace users online;

Tablespace altered.

 

6.重新查看資料檔案路徑

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/yoon/system01.dbf
/u01/oracle/oradata/yoon/sysaux01.dbf
/u01/oracle/oradata/yoon/undotbs01.dbf
/u01/oracle/oradata/yoondata/users01.dbf

oracle 修改資料表空間儲存路徑