一、停库修改数据文件目录、文件名
1、当前数据文件目录
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
PAR /u01/app/oracle/oradata/orcl/par01.dbf
TEST /u01/app/oracle/oradata/orcl/test01.dbf
USERS /u01/app/oracle/oradata/orcl/users01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
TT /u01/app/oracle/tt.dbf
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、拷贝数据文件
cp /u01/app/oracle/tt.dbf /u01/app/oracle/oradata/orcl/tt01.dbf
5、修改数据文件目录，文件名
SQL> alter database rename file '/u01/app/oracle/tt.dbf' to '/u01/app/oracle/oradata/orcl/tt01.dbf';
Database altered.
6、启动数据库到open状态
SQL> alter database open;
Database altered.
7、再次查看数据文件目录
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
PAR /u01/app/oracle/oradata/orcl/par01.dbf
TEST /u01/app/oracle/oradata/orcl/test01.dbf
USERS /u01/app/oracle/oradata/orcl/users01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
TT /u01/app/oracle/oradata/orcl/tt01.dbf
二、在线修改数据文件目录、文件名
1、创建测试数据文件，测试表
SQL> create table tttt (id int) tablespace tt;
Table created.
SQL> insert into tttt values (1);
1 row created.
SQL> select * from tttt;
ID
----------
1
2、查看当前数据文件目录、文件名
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
PAR /u01/app/oracle/oradata/orcl/par01.dbf
TEST /u01/app/oracle/oradata/orcl/test01.dbf
USERS /u01/app/oracle/oradata/orcl/users01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
TT /u01/app/oracle/oradata/orcl/tt01.dbf
3、表空间offline
SQL> alter tablespace tt offline;
Tablespace altered.
4、移动数据文件目录，修改文件名
SQL> ho mv /u01/app/oracle/oradata/orcl/tt01.dbf /u01/app/oracle/tt.dbf
5、修改数据文件目录，文件名
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/tt01.dbf' to '/u01/app/oracle/tt.dbf';
Database altered.
6、表空间online
SQL> alter tablespace tttt online;
Tablespace altered.
7、验证数据
SQL> select * from tttt;
ID
----------
1
8、验证数据文件目录，文件名
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
PAR /u01/app/oracle/oradata/orcl/par01.dbf
TEST /u01/app/oracle/oradata/orcl/test01.dbf
USERS /u01/app/oracle/oradata/orcl/users01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
TT /u01/app/oracle/tt.dbf
7、验证数据
SQL> select * from tttt;
ID
----------
1
8、验证数据文件目录，文件名
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
PAR /u01/app/oracle/oradata/orcl/par01.dbf
TEST /u01/app/oracle/oradata/orcl/test01.dbf
USERS /u01/app/oracle/oradata/orcl/users01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
TT /u01/app/oracle/tt.dbf