Movement of Oracle Tablespace data files

Requirement: To move a data file in a table space of a user to a path from one path to another for some reason
Reason:
(1) The Table space files and system tablespace files in the current instance are all on one hard disk, which causes IO to increase. The database table space is removed. Reduce IO.
(2) disk space is limited, this disk space is too small, as the data grows, need to change a large disk
Feasibility: Linux files are mounted, to a directory where the disk movement is just a change in the file path.

There are two ways of implementing this method
1.alter tablespace
(1) View the data file information for the table space to be changed
Sql> Select Tablespace_name,file_name from dba_data_files where tablespace_name= ' MAIL ';

Tablespace_name file_name
------------------------------ --------------------------------------------------------------------------------
Mail/u01/app/oracle/oradata/orcl/mail.dbf

(2) Take the table space offline

Sql> alter tablespace mail offline;

Table space has changed.

Sql> Select Tablespace_name,file_name,online_status from dba_data_files where tablespace_name= ' MAIL ';

Tablespace_name file_name Online_sta TUS
------------------------------ -------------------------------------------------------------------------------- - ------------
MAIL/U01/APP/ORACLE/ORADATA/ORCL/MAIL.DBF OFFLINE

(3) Copy the original data file to the new path
Sql> host CP '/U01/APP/ORACLE/ORADATA/ORCL/MAIL.DBF '/HOME/ORACLE/MAIL01.DBFDBF '

(4) Modify the data file name, path. (Note: The actual is to modify the control file, tell the control file this table space data file changed)
Sql> alter tablespace mail rename datafile '/u01/app/oracle/oradata/orcl/mail.dbf ' to '/home/oracle/mail01.dbfdbf ';

Table space has changed.
(5) Table space Online
Sql> alter tablespace Mail Online;

(6) View data file status for table space

Sql> Select Tablespace_name,file_name,online_status from dba_data_files where tablespace_name= ' MAIL ';

Tablespace_name file_name Online_sta TUS
------------------------------ -------------------------------------------------------------------------------- - ------------
MAIL/HOME/ORACLE/MAIL01.DBFDBF ONLINE

Note: This method is only available for table spaces that can be offline, only for normal table spaces, and not for system tablespace.
The Sysaux table space can be implemented with this method

Sql> Select Tablespace_name,file_name from dba_data_files where tablespace_name= ' Sysaux ';

Tablespace_name
------------------------------
file_name
--------------------------------------------------------------------------------
Sysaux
/u01/app/oracle/oradata/orcl/sysaux01.dbf

sql> alter tablespace sysaux offline;

Table space has changed.

Sql> host CP '/U01/APP/ORACLE/ORADATA/ORCL/SYSAUX01.DBF '/home/oracle/test.dbf ';

sql> alter tablespace sysaux rename datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf ' to '/home/oracle/test.dbf ' ;

Table space has changed.

sql> alter tablespace Sysaux online;

Table space has changed.

Sql> Select Tablespace_name,file_name from dba_data_files where tablespace_name= ' Sysaux ';

Tablespace_name
------------------------------
file_name
--------------------------------------------------------------------------------
Sysaux
/home/oracle/test.dbf

2.alter Database for system table space, System,temp,undo
(1) Consistency close database and boot to mount state
sql> shutdown Immediate
Sql> Startup Mount
(2) Copying data files
Sql> host CP '/U01/APP/ORACLE/ORADATA/ORCL/SYSTEM01.DBF '/home/oracle/test.dbf ';
(3) Modify the control file. Note that the concept of tablespace is only available if the database is open, and all of the file in this case is operated at this time.
sql> ALTER DATABASE rename file '/u01/app/oracle/oradata/orcl/system01.dbf ' to '/home/oracle/test.dbf ';

The database has changed.
(4) Open the database
sql> ALTER DATABASE open;
(5) View system Tablespace data file status
Sql> Select Tablespace_name,file_name,online_status from dba_data_files where tablespace_name= ' SYSTEM ';

Tablespace_name file_name Online_sta TUS
------------------------------ -------------------------------------------------------------------------------- - ------------
SYSTEM/HOME/ORACLE/TEST.DBF SYSTEM

Movement of Oracle Tablespace data files