Two methods of migrating data files to other paths (1)
(1) method 1: offline tablespace (not related to the library)
* * *
* main steps: *
* 1. Offline tablespace: alter tablespace tablespace_name offline;*
* 2. Copy the data file to a new directory; *
* 3. Rename modifies the table space and modifies the control file; *
* 4. Online tablespace; *
* * *
Alter database datafile'/ u01 offline appAcle1 offline oradata
Alter database datafile'/ u01 online appAcle1 online oradata
Select tablespace_name,status from dba_tablespaces
Alter tablespace users read write
Take changing the location of the datafile / opt/oracle/oradata/ZERONE01.DBF of the tablespace zerone as an example
1. Offline tablespace TS_EMR to make tablespace TS_EMR offline
-
SQL > alter tablespace TS_EMR offline; (no drop note: if non-archive mode is followed by Drop)
2. Copy the data file to the new directory
--
Note:
* 1. If you check whether the new directory / home/oracle/oradata/zerone exists, create this path if it does not exist, and change the directory attributes:
* # mkdir-p / home/oracle/oradata/zerone
* # chown-R oracle:oinstall / home/oracle/oradata/zerone
Copy data file
# cp / opt/oracle/oradata/ZERONE01.DBF / home/oracle/oradata/zerone/ZERONE01.DBF
# chown oracle:oinstall / home/oracle/oradata/zerone/ZERONE01.DBF
3. Rename modifies the tablespace data file to the new location, and modifies the control file. If there are multiple data files in a tablespace, execute multiple statements as follows
SQL > alter tablespace TS_EMR rename datafile'E:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ EMR01.DBF' to'E:\ APP\ ADMINISTRATOR\ ORADATA\ EMR01.DBF'
Alter tablespace TS_EMR rename datafile'E:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ EMR02.DBF' to'E:\ APP\ ADMINISTRATOR\ ORADATA\ EMR02.DBF'
4. Online tablespace
-
SQL > alter tablespace TS_EMR online
Check:
SQL > select name from v$datafile
SQL > select file_name,tablespace_name from dba_data_files where tablespace_name='TS_EMR'
(2) method 2: SQL modifies the location of the data file (close the library)
* *
* main steps:
* 1. Close the database
* 2. Copy the data file to a new location
* 3. Start the database to mount status
* 4. Modify the location of data files through SQL
* 5. Open the database
* *
1. Close the database
-
SQL > shutdown immediate
2. Copy the data file to the new location
-
Same method one
3. Start the database to mount status
-
SQL > startup mount
4. Modify the location of data files through SQL
-
SQL > alter database rename file'E:\ APP\ ADMINISTRATOR\ ORADATA\ EMR01.DBF' to'E:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ EMR01.DBF'
Alter database rename file'E:\ APP\ ADMINISTRATOR\ ORADATA\ EMR02.DBF' to'E:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ EMR02.DBF'
5. Open the database
-
SQL > alter database open
Check:
SQL > select name from v$datafile
SQL > select file_name,tablespace_name from dba_data_files where tablespace_name='ZERONE'
How to see which tablespace the data file belongs to
(1) View the tablespace corresponding to the data file of the permanent tablespace
SQL > select TABLESPACE_NAME from dba_data_files where FILE_NAME=' data file full path'
(2) View the temporary tablespace corresponding to the data file of the temporary tablespace
SQL > select TABLESPACE_NAME from dba_temp_files where FILE_NAME=' data file full path'
