oracle修改数据文件路径
方法1.关机修改数据文件路径
1.查看当前数据文件路径 SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/oradb/users01.dbf /u01/app/oracle/oradata/oradb/undotbs01.dbf /u01/app/oracle/oradata/oradb/sysaux01.dbf /u01/app/oracle/oradata/oradb/system01.dbf /u01/app/oracle/oradata/gen_cfg01.dbf /u01/app/oracle/oradata/gen_ers01.dbf /u01/app/oracle/oradata/gen_etl01.dbf /u01/app/oracle/oradata/gen_ods01.dbf /u01/app/oracle/oradata/gen_log01.dbf /u01/app/oracle/oradata/gen_rps01.dbf /u01/app/oracle/oradata/gen_ctiadaptor01.dbf /u01/app/oracle/oradata/gen_cdr.dbf 12 rows selected. 2.停库 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 3.移动数据文件到新路径下 [oracle@node1 ~]$ cd /u01/app/oracle/oradata/ [oracle@node1 oradata]$ ls gen_cdr.dbf gen_ctiadaptor01.dbf gen_etl01.dbf gen_ods01.dbf oradb gen_cfg01.dbf gen_ers01.dbf gen_log01.dbf gen_rps01.dbf [oracle@node1 oradata]$ mv gen_cfg01.dbf /u01/app/oracle/oradata/oradb/ 4.启动数据库到mount状态 [oracle@node1 oradata]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 24 01:28:47 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 759943168 bytes Fixed Size 2257112 bytes Variable Size 499126056 bytes Database Buffers 255852544 bytes Redo Buffers 2707456 bytes Database mounted. 5.修改数据文件路径并启动数据库 SQL> alter database rename file '/u01/app/oracle/oradata/gen_cfg01.dbf' to '/u01/app/oracle/oradata/oradb/gen_cfg01.dbf'; Database altered. SQL> alter database open; Database altered. 6.查看数据文件路径 SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/oradb/users01.dbf /u01/app/oracle/oradata/oradb/undotbs01.dbf /u01/app/oracle/oradata/oradb/sysaux01.dbf /u01/app/oracle/oradata/oradb/system01.dbf /u01/app/oracle/oradata/oradb/gen_cfg01.dbf /u01/app/oracle/oradata/gen_ers01.dbf /u01/app/oracle/oradata/gen_etl01.dbf /u01/app/oracle/oradata/gen_ods01.dbf /u01/app/oracle/oradata/gen_log01.dbf /u01/app/oracle/oradata/gen_rps01.dbf /u01/app/oracle/oradata/gen_ctiadaptor01.dbf
方法2.在线修改数据文件路径
1.查看当前数据文件路径 SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME -------------------------------------------------- ------------------------------ /u01/app/oracle/oradata/oradb/users01.dbf USERS /u01/app/oracle/oradata/oradb/undotbs01.dbf UNDOTBS1 /u01/app/oracle/oradata/oradb/sysaux01.dbf SYSAUX /u01/app/oracle/oradata/oradb/system01.dbf SYSTEM /u01/app/oracle/oradata/oradb/gen_cfg01.dbf GEN_CFG /u01/app/oracle/oradata/gen_ers01.dbf GEN_ERS /u01/app/oracle/oradata/gen_etl01.dbf GEN_ETL /u01/app/oracle/oradata/gen_ods01.dbf GEN_ODS /u01/app/oracle/oradata/gen_log01.dbf GEN_LOG /u01/app/oracle/oradata/gen_rps01.dbf GEN_RPS /u01/app/oracle/oradata/gen_ctiadaptor01.dbf GEN_CTIADAPTOR /u01/app/oracle/oradata/gen_cdr.dbf GEN_CDR 12 rows selected. 2.offline表空间 SQL> alter tablespace GEN_ERS offline; Tablespace altered. 3.移动数据文件到新路径下 SQL> host mv /u01/app/oracle/oradata/gen_ers01.dbf /u01/app/oracle/oradata/oradb/gen_ers01.dbf 4.修改数据文件路径 SQL> alter database rename file '/u01/app/oracle/oradata/gen_ers01.dbf' to '/u01/app/oracle/oradata/oradb/gen_ers01.dbf'; Database altered. 5.online表空间 SQL> alter tablespace GEN_ERS online; Tablespace altered. 6.查看修改后数据文件路径 SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------- /u01/app/oracle/oradata/oradb/users01.dbf /u01/app/oracle/oradata/oradb/undotbs01.dbf /u01/app/oracle/oradata/oradb/sysaux01.dbf /u01/app/oracle/oradata/oradb/system01.dbf /u01/app/oracle/oradata/oradb/gen_cfg01.dbf /u01/app/oracle/oradata/oradb/gen_ers01.dbf /u01/app/oracle/oradata/gen_etl01.dbf /u01/app/oracle/oradata/gen_ods01.dbf /u01/app/oracle/oradata/gen_log01.dbf /u01/app/oracle/oradata/gen_rps01.dbf /u01/app/oracle/oradata/gen_ctiadaptor01.dbf /u01/app/oracle/oradata/gen_cdr.dbf 12 rows selected.
