DB:Oracle 11.2.0.4.0
方法一：datafile offline
(1) 修改路径前offline数据文件，影响被offline数据文件中数据的读取和修改，不影响同一表空间下其他online数据文件内数据的读取和修改。 (2) online datafile前，需要执行recover datafile操作,确保数据库处于归档模式下。
方法二：tablepace offline
(1) 修改路径前tablespace offline,影响offline tablespace下所有数据文件的读取和使用。 (2) online tablespace前，不需要执行recover tablespace操作。
方法三：restart instance
(1) 重启实例，影响所有操作。
方法四：ALTER DATABASE MOVE DATAFILE '原路径' TO '新路径';
(1) 12C开始支持在线修改数据文件路径，迁移过程中不影响数据使用。
方法一：datafile offline
查看数据库版本
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production
创建表空间cjctbs
SQL> create tablespace cjctbs datafile '/u01/app/oracle11/oradata/chendb/cjctbs01.dbf' size 1m; Tablespace created.
创建用户cjc并授权
SQL> create user cjc identified by cjc default tablespace cjctbs; User created. SQL> grant connect,resource,dba to cjc; Grant succeeded.
查看数据文件信息
col file_name for a50 col tablespace_name for a20 set line 200 select tablespace_name,file_name from dba_data_files; TABLESPACE_NAME FILE_NAME -------------------- -------------------------------------------------- USERS /u01/app/oracle11/oradata/chendb/users01.dbf UNDOTBS1 /u01/app/oracle11/oradata/chendb/undotbs01.dbf SYSAUX /u01/app/oracle11/oradata/chendb/sysaux01.dbf SYSTEM /u01/app/oracle11/oradata/chendb/system01.dbf EXAMPLE /u01/app/oracle11/oradata/chendb/example01.dbf CJCTBS /u01/app/oracle11/oradata/chendb/cjctbs01.dbf 6 rows selected.
创建测试数据
conn cjc/cjc create table t1(id number,itime varchar2(200)); CREATE OR REPLACE PROCEDURE insert_pro is begin for i in 1 .. 1000000 loop insert into t1 values (i, to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss')); commit; end loop; end; / SQL> exec insert_pro(); BEGIN insert_pro(); END; * ERROR at line 1: ORA-01653: unable to extend table CJC.T1 by 8 in tablespace CJCTBS ORA-06512: at "CJC.INSERT_PRO", line 4 ORA-06512: at line 1 SQL> select count(*) from t1; COUNT(*) ---------- 26943
添加数据文件
模拟添加错路径
SQL> alter tablespace cjctbs add datafile '/home/oracle/cjctbs02.dbf' size 1m; Tablespace altered.
查看数据文件信息
col file_name for a50 col tablespace_name for a20 set line 200 select tablespace_name,file_id,file_name from dba_data_files order by 2; TABLESPACE_NAME FILE_ID FILE_NAME -------------------- ---------- -------------------------------------------------- SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf CJCTBS 7 /home/oracle/cjctbs02.dbf 7 rows selected.
插入数据
SQL> exec insert_pro(); BEGIN insert_pro(); END; * ERROR at line 1: ORA-01653: unable to extend table CJC.T1 by 128 in tablespace CJCTBS ORA-06512: at "CJC.INSERT_PRO", line 4 ORA-06512: at line 1
移动数据文件/home/oracle/cjctbs02.dbf到/u01/app/oracle11/oradata/chendb/cjctbs02.dbf
SQL> show user USER is "CJC" SQL> select count(*) from t1; COUNT(*) ---------- 28946
离线cjctbs02.dbf 数据文件
alter database datafile 7 offline;
查看数据文件状态
select file_name, status, online_status from dba_data_files where tablespace_name='CJCTBS'; FILE_NAME STATUS ONLINE_ -------------------------------------------------- --------- ------- /u01/app/oracle11/oradata/chendb/cjctbs01.dbf AVAILABLE ONLINE /home/oracle/cjctbs02.dbf AVAILABLE RECOVER
查看控制文件中记录的数据文件检查点信息
SQL> select file#, CHECKPOINT_CHANGE#, OFFLINE_CHANGE# from v$datafile; FILE# CHECKPOINT_CHANGE# OFFLINE_CHANGE# ---------- ------------------ --------------- 1 2325667 925701 2 2325667 925701 3 2325667 925701 4 2325667 925701 5 2325667 953748 6 2326617 0 7 2364389 0 7 rows selected.
查看数据文件头检查点信息
select file#, status, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header; FILE# STATUS REC FUZ CHECKPOINT_CHANGE# ---------- ------- --- --- ------------------ 1 ONLINE NO YES 2325667 2 ONLINE NO YES 2325667 3 ONLINE NO YES 2325667 4 ONLINE NO YES 2325667 5 ONLINE NO YES 2325667 6 ONLINE NO YES 2326617 7 OFFLINE YES YES 2364389 7 rows selected.
表空间状态
SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS -------------------- --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE CJCTBS ONLINE 7 rows selected.
查看v$recover_file
SQL> select *from v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- ----------------------------------------------------------------- ---------- --------- 7 OFFLINE OFFLINE 2364389 29-NOV-20
生成检查点
SQL> alter system checkpoint; System altered.
查看数据文件头检查点信息
SQL> select file#, status, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header; FILE# STATUS REC FUZ CHECKPOINT_CHANGE# ---------- ------- --- --- ------------------ 1 ONLINE NO YES 2366991 2 ONLINE NO YES 2366991 3 ONLINE NO YES 2366991 4 ONLINE NO YES 2366991 5 ONLINE NO YES 2366991 6 ONLINE NO YES 2366991 7 OFFLINE YES YES 2364389 7 rows selected. SQL> select file#, CHECKPOINT_CHANGE#, OFFLINE_CHANGE# from v$datafile; FILE# CHECKPOINT_CHANGE# OFFLINE_CHANGE# ---------- ------------------ --------------- 1 2366991 925701 2 2366991 925701 3 2366991 925701 4 2366991 925701 5 2366991 953748 6 2366991 0 7 2364389 0 7 rows selected.
将数据文件移动到新路径
使用copy或rman都可以
RMAN> copy datafile '/home/oracle/cjctbs02.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf'; Starting backup at 29-NOV-20 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/home/oracle/cjctbs02.dbf output file name=/u01/app/oracle11/oradata/chendb/cjctbs02.dbf tag=TAG20201129T213347 RECID=33 STAMP=1057786427 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 29-NOV-20
查看文件已经拷贝
[oracle@cjcos01 arch]$ cd /u01/app/oracle11/oradata/chendb [oracle@cjcos01 chendb]$ ll -rth total 2.0G -rw-r----- 1 oracle oinstall 51M Nov 29 17:04 redo01.log -rw-r----- 1 oracle oinstall 51M Nov 29 17:04 redo02.log -rw-r----- 1 oracle oinstall 21M Nov 29 18:04 temp01.dbf -rw-r----- 1 oracle oinstall 751M Nov 29 21:31 system01.dbf -rw-r----- 1 oracle oinstall 561M Nov 29 21:31 sysaux01.dbf -rw-r----- 1 oracle oinstall 5.1M Nov 29 21:31 users01.dbf -rw-r----- 1 oracle oinstall 201M Nov 29 21:31 undotbs01.dbf -rw-r----- 1 oracle oinstall 314M Nov 29 21:31 example01.dbf -rw-r----- 1 oracle oinstall 1.1M Nov 29 21:31 cjctbs01.dbf -rw-r----- 1 oracle oinstall 1.1M Nov 29 21:33 cjctbs02.dbf -rw-r----- 1 oracle oinstall 51M Nov 29 21:34 redo03.log -rw-r----- 1 oracle oinstall 9.3M Nov 29 21:34 control01.ctl -rw-r----- 1 oracle oinstall 9.3M Nov 29 21:34 control02.ctl
修改控制文件中记录的位置
SQL> alter database rename file '/home/oracle/cjctbs02.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf'; Database altered.
需要确保数据文件已经在新的路径下了，否则如下报错：
SQL> alter database rename file '/home/oracle/cjctbs02.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf'; alter database rename file '/home/oracle/cjctbs02.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf' * ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01141: error renaming data file 7 - new file '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf' not found ORA-01110: data file 7: '/home/oracle/cjctbs02.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3
查看数据文件路径
SQL> col file_name for a50 col tablespace_name for a20 set line 200 select tablespace_name,file_id,file_name from dba_data_files order by 2;SQL> SQL> SQL> TABLESPACE_NAME FILE_ID FILE_NAME -------------------- ---------- -------------------------------------------------- SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf CJCTBS 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf 7 rows selected.
datafile位offline状态下无法查询数据
SQL> select count(*) from t1; select count(*) from t1 * ERROR at line 1: ORA-00376: file 7 cannot be read at this time ORA-01110: data file 7: '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf'
可以查询同一表空间下其他online状态的数据文件数据
SQL> select count(*) from t1 where rownum<=10; COUNT(*) ---------- 10 SQL> select count(*) from t1 where rownum<=100000000; select count(*) from t1 where rownum<=100000000 * ERROR at line 1: ORA-00376: file 7 cannot be read at this time ORA-01110: data file 7: '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf'
数据库处于归档模式
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /arch Oldest online log sequence 40 Next log sequence to archive 42 Current log sequence 42
不能直接online数据文件
SQL> alter database datafile 7 online; alter database datafile 7 online * ERROR at line 1: ORA-01113: file 7 needs media recovery ORA-01110: data file 7: '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf'
恢复datafile
SQL> recover datafile 7; Media recovery complete.
执行onlne datafile
SQL> alter database datafile 7 online; Database altered.
查询测试数据
SQL> conn cjc/cjc Connected. SQL> select count(*) from t1; COUNT(*) ---------- 28946
查看数据文件信息
SQL> col file_name for a50 col tablespace_name for a20 set line 200 select tablespace_name,file_id,file_name from dba_data_files order by 2;SQL> SQL> SQL> TABLESPACE_NAME FILE_ID FILE_NAME -------------------- ---------- -------------------------------------------------- SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf CJCTBS 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf 7 rows selected. SQL> select file#, CHECKPOINT_CHANGE#, OFFLINE_CHANGE# from v$datafile; FILE# CHECKPOINT_CHANGE# OFFLINE_CHANGE# ---------- ------------------ --------------- 1 2367288 925701 2 2367288 925701 3 2367288 925701 4 2367288 925701 5 2367288 953748 6 2367288 0 7 2367288 0 7 rows selected. SQL> select file#, status, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header; FILE# STATUS REC FUZ CHECKPOINT_CHANGE# ---------- ------- --- --- ------------------ 1 ONLINE NO YES 2367288 2 ONLINE NO YES 2367288 3 ONLINE NO YES 2367288 4 ONLINE NO YES 2367288 5 ONLINE NO YES 2367288 6 ONLINE NO YES 2367288 7 ONLINE NO YES 2367288 7 rows selected. [oracle@cjcos01 ~]$ mv cjctbs02.dbf cjctbs02.dbf.bak
方法二：tablepace offline
添加数据文件
模拟添加错误位置
SQL> conn cjc/cjc Connected. SQL> alter tablespace cjctbs add datafile '/home/oracle/cjctbs03.dbf' size 1m; Tablespace altered.
查看数据文件信息
SQL> col file_name for a50 col tablespace_name for a20 set line 200 select tablespace_name,file_id,file_name from dba_data_files order by 2;SQL> SQL> SQL> TABLESPACE_NAME FILE_ID FILE_NAME -------------------- ---------- -------------------------------------------------- SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf CJCTBS 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf CJCTBS 8 /home/oracle/cjctbs03.dbf 8 rows selected.
执行表空间offline
SQL> alter tablespace cjctbs offline; Tablespace altered.
查看表空间状态
SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS -------------------- --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE CJCTBS OFFLINE 7 rows selected.
查看v$recover_file
SQL> select *from v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- ----------------------------------------------------------------- ---------- --------- 6 OFFLINE OFFLINE OFFLINE NORMAL 0 7 OFFLINE OFFLINE OFFLINE NORMAL 0 8 OFFLINE OFFLINE OFFLINE NORMAL 0
查看数据文件头信息
SQL> select file#, status, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header; FILE# STATUS REC FUZ CHECKPOINT_CHANGE# ---------- ------- --- --- ------------------ 1 ONLINE NO YES 2367431 2 ONLINE NO YES 2367431 3 ONLINE NO YES 2367431 4 ONLINE NO YES 2367431 5 ONLINE NO YES 2367431 6 OFFLINE 0 7 OFFLINE 0 8 OFFLINE 0 8 rows selected. SQL> select file#, CHECKPOINT_CHANGE#, OFFLINE_CHANGE# from v$datafile; FILE# CHECKPOINT_CHANGE# OFFLINE_CHANGE# ---------- ------------------ --------------- 1 2367431 925701 2 2367431 925701 3 2367431 925701 4 2367431 925701 5 2367431 953748 6 2367562 0 7 2367562 0 8 2367562 0 8 rows selected.
无法查询offline tablespace下所有数据
SQL> select count(*) from t1; select count(*) from t1 * ERROR at line 1: ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: '/u01/app/oracle11/oradata/chendb/cjctbs01.dbf' SQL> select count(*) from t1 where rownum<=10; select count(*) from t1 where rownum<=10 * ERROR at line 1: ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: '/u01/app/oracle11/oradata/chendb/cjctbs01.dbf' SQL> select * from t1; select * from t1 * ERROR at line 1: ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: '/u01/app/oracle11/oradata/chendb/cjctbs01.dbf'
拷贝数据文件到新位置
[oracle@cjcos01 ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sun Nov 29 21:47:38 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: CHENDB (DBID=1831901477) RMAN> copy datafile '/home/oracle/cjctbs03.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs03.dbf'; Starting backup at 29-NOV-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=58 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=/home/oracle/cjctbs03.dbf output file name=/u01/app/oracle11/oradata/chendb/cjctbs03.dbf tag=TAG20201129T214814 RECID=34 STAMP=1057787294 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 29-NOV-20
修改控制文件记录的数据文件位置
SQL> alter database rename file '/home/oracle/cjctbs03.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs03.dbf'; Database altered.
online表空间
不需要执行recover操作
SQL> alter tablespace cjctbs online; Tablespace altered.
查询数据
SQL> select count(*) from t1; COUNT(*) ---------- 28946
查看下数据文件信息
SQL> col file_name for a50 col tablespace_name for a20 set line 200 select tablespace_name,file_id,file_name from dba_data_files order by 2;SQL> SQL> SQL> TABLESPACE_NAME FILE_ID FILE_NAME -------------------- ---------- -------------------------------------------------- SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf CJCTBS 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf CJCTBS 8 /u01/app/oracle11/oradata/chendb/cjctbs03.dbf 8 rows selected.
查询表空间信息
SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS -------------------- --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE CJCTBS ONLINE 7 rows selected. SQL> select *from v$recover_file; no rows selected SQL> select file#, status, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header; FILE# STATUS REC FUZ CHECKPOINT_CHANGE# ---------- ------- --- --- ------------------ 1 ONLINE NO YES 2367431 2 ONLINE NO YES 2367431 3 ONLINE NO YES 2367431 4 ONLINE NO YES 2367431 5 ONLINE NO YES 2367431 6 ONLINE NO YES 2367730 7 ONLINE NO YES 2367730 8 ONLINE NO YES 2367730 8 rows selected. SQL> select file#, CHECKPOINT_CHANGE#, OFFLINE_CHANGE# from v$datafile; FILE# CHECKPOINT_CHANGE# OFFLINE_CHANGE# ---------- ------------------ --------------- 1 2367431 925701 2 2367431 925701 3 2367431 925701 4 2367431 925701 5 2367431 953748 6 2367730 2367562 7 2367730 2367562 8 2367730 2367562 8 rows selected.
方法三：restart instance
添加数据文件
模拟添加错误位置
SQL> alter tablespace cjctbs add datafile '/home/oracle/cjctbs05.dbf' size 1m; Tablespace altered.
查看数据文件信息
SQL> col file_name for a50 col tablespace_name for a20 set line 200 select tablespace_name,file_id,file_name from dba_data_files order by 2;SQL> SQL> SQL> TABLESPACE_NAME FILE_ID FILE_NAME -------------------- ---------- -------------------------------------------------- SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf CJCTBS 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf CJCTBS 8 /u01/app/oracle11/oradata/chendb/cjctbs03.dbf CJCTBS 9 /home/oracle/cjctbs05.dbf 9 rows selected.
停止实例
SQL> conn / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
启动数据库到mout
SQL> startup mount ORACLE instance started. Total System Global Area 726540288 bytes Fixed Size 2256792 bytes Variable Size 448790632 bytes Database Buffers 272629760 bytes Redo Buffers 2863104 bytes Database mounted.
拷贝数据文件到新位置
[oracle@cjcos01 ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sun Nov 29 22:22:23 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: CHENDB (DBID=1831901477, not open) RMAN> copy datafile '/home/oracle/cjctbs05.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs05.dbf'; Starting backup at 29-NOV-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00009 name=/home/oracle/cjctbs05.dbf output file name=/u01/app/oracle11/oradata/chendb/cjctbs05.dbf tag=TAG20201129T222227 RECID=35 STAMP=1057789348 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 29-NOV-20
修改控制文件记录的数据文件位置
SQL> alter database rename file '/home/oracle/cjctbs05.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs05.dbf'; Database altered.
启动数据库
SQL> alter database open; Database altered.
查看数据文件信息
SQL> col file_name for a50 col tablespace_name for a20 set line 200 select tablespace_name,file_id,file_name from dba_data_files order by 2;SQL> SQL> SQL> TABLESPACE_NAME FILE_ID FILE_NAME -------------------- ---------- -------------------------------------------------- SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf CJCTBS 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf CJCTBS 8 /u01/app/oracle11/oradata/chendb/cjctbs03.dbf CJCTBS 9 /u01/app/oracle11/oradata/chendb/cjctbs05.dbf 9 rows selected.
方法四：ALTER DATABASE MOVE DATAFILE '原路径' TO '新路径';
12C开始，简化了修改数据文件路径的方法，减少了修改路径过程中造成的数据不可用问题。
http://blog.itpub.net/29785807/viewspace-2565008/
例如：
ALTER DATABASE MOVE DATAFILE '/home/oracle/cjctbs06.dbf' TO '/u01/app/oracle11/oradata/chendb/cjctbs06.dbf';
2020-12-07 12:53 chenjuchao