一、停库修改数据文件目录、文件名

1、当前数据文件目录

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/ORCL/users01.dbf

/u01/app/oracle/oradata/ORCL/undotbs01.dbf

/u01/app/oracle/oradata/ORCL/sysaux01.dbf

/u01/app/oracle/oradata/ORCL/system01.dbf

/u01/app/oracle/oradata/ORCL/test01.dbf

/u01/app/oracle/tt.dbf

/u01/app/oracle/ttt

2、停库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

3、启动到mount状态

SQL> startup mount

ORACLE instance started.

Total System Global Area  939495424 bytes

Fixed Size                  2258840 bytes

Variable Size             301992040 bytes

Database Buffers          629145600 bytes

Redo Buffers                6098944 bytes

Database mounted.

4、拷贝数据文件

[oracle@node01 ~]$ cp /u01/app/oracle/ttt /u01/ap/u01/app/oracle/tt.dbf

[oracle@node01 ~]$ cp /u01/app/oracle/tt.dbf /u01/app/oracle/oradata/ORCL/tt.dbf

5、修改数据文件目录

SQL> alter database rename file '/u01/app/oracle/tt.dbf' to '/u01/app/oracle/oradata/ORCL/tt.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/ORCL/tt.dbf' to '/u01/app/oracle/oradata/ORCL/tt01.dbf';

Database altered.

6、启动数据库到open状态

SQL> alter database open;

Database altered.

7、再次查看数据文件目录

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/ORCL/users01.dbf

/u01/app/oracle/oradata/ORCL/undotbs01.dbf

/u01/app/oracle/oradata/ORCL/sysaux01.dbf

/u01/app/oracle/oradata/ORCL/system01.dbf

/u01/app/oracle/oradata/ORCL/test01.dbf

/u01/app/oracle/oradata/ORCL/tt01.dbf

/u01/app/oracle/oradata/ORCL/ttt.dbf

二、在线修改数据文件目录、文件名

1、创建测试数据文件，测试表

SQL> create tablespace tttt datafile '/u01/app/oracle/tttt.dbf' size 100M;

Tablespace created.

SQL> create table tttt (id int) tablespace tttt;

Table created.

SQL> insert into tttt values (1);

1 row created.

SQL> select * from tttt;

        ID

----------

         1

2、查看当前数据文件目录、文件名

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/ORCL/users01.dbf

/u01/app/oracle/oradata/ORCL/undotbs01.dbf

/u01/app/oracle/oradata/ORCL/sysaux01.dbf

/u01/app/oracle/oradata/ORCL/system01.dbf

/u01/app/oracle/oradata/ORCL/test01.dbf

/u01/app/oracle/oradata/ORCL/tt01.dbf

/u01/app/oracle/oradata/ORCL/ttt.dbf

/u01/app/oracle/oradata/ORCL/tttt.dbf

3、表空间offline

SQL> alter tablespace tttt offline;

Tablespace altered.

4、移动数据文件目录，修改文件名

SQL> host mv /u01/app/oracle/tttt.dbf /u01/app/oracle/oradata/ORCL/tttt.dbf

5、修改数据文件目录，文件名

SQL> alter database rename file '/u01/app/oracle/tttt.dbf' to '/u01/app/oracle/oradata/ORCL/tttt.dbf';

Database altered.

6、表空间online

SQL> alter tablespace tttt online;

Tablespace altered.

7、验证数据

SQL> select * from tttt;

        ID

----------

         1

8、验证数据文件目录，文件名

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/ORCL/users01.dbf

/u01/app/oracle/oradata/ORCL/undotbs01.dbf

/u01/app/oracle/oradata/ORCL/sysaux01.dbf

/u01/app/oracle/oradata/ORCL/system01.dbf

/u01/app/oracle/oradata/ORCL/test01.dbf

/u01/app/oracle/oradata/ORCL/tt01.dbf

/u01/app/oracle/oradata/ORCL/ttt.dbf

/u01/app/oracle/oradata/ORCL/tttt.dbf