Oracle Tutorial: Move all data files

Oracle Tutorial: Move all data files. Recently, there is a shortage of hard disk space in a development library. A new disk is added to move all the data files to the new disk.

Oracle Tutorial: Move all data files. Recently, there is a shortage of hard disk space in a development library. A new disk is added to move all the data files to the new disk.

For example, there is a shortage of hard disk space in a development library recently. A new disk is added to prepare to move all the data files to the new disk.
First, list the data files to be moved. The data files belong to the tablespace. The following table spaces can be used in different categories:
Control File
System tablespace
Undo tablespace
Temporary tablespace
Redo log file
User_data tablespace

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
GTLIONS
GTLIONSTMP

SQL> select file_name, file_id, tablespace_name from dba_data_Files;
FILE_NAME FILE_ID TABLESPACE_NAME
------------------------------------------------------------------------------------------
/U01/Oracle/10g/oradata/gt10g/users01.dbf 4 USERS
/U01/oracle/10g/oradata/gt10g/sysaux01.dbf 3 SYSAUX
/U01/oracle/10g/oradata/gt10g/undotbs01.dbf 2 UNDOTBS1
/U01/oracle/10g/oradata/gt10g/system01.dbf 1 SYSTEM
/U01/oracle/10g/oradata/gt10g/gtlions01.ora 5 GTLIONS

SQL> select file_name, file_id, tablespace_name from dba_temp_Files;

FILE_NAME FILE_ID TABLESPACE_NAME
------------------------------------------------------------------------------------------
/U01/oracle/10g/oradata/gt10g/temp01.dbf 1 TEMP
/U01/oracle/10g/oradata/gt10g/gtlionstmp01.ora 2 GTLIONSTMP

SQL> select name from v $ controlfile;

NAME
Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------
/U01/oracle/10g/oradata/gt10g/control01.ctl
/U01/oracle/10g/oradata/gt10g/control02.ctl
/U01/oracle/10g/oradata/gt10g/control03.ctl

SQL> select member from v $ logfile;

MEMBER
Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------
/U01/oracle/10g/oradata/gt10g/redo03.log
/U01/oracle/10g/oradata/gt10g/redo02.log
/U01/oracle/10g/oradata/gt10g/redo01.log

For the undo tablespace, you can directly operate it when the data is opened:
SQL> create undo tablespace undotbs2 datafile '/u01/oracle/10g/oradata/gt10gnew/undotbs01.dbf' size 20 m autoextend on;

Tablespace created.

SQL> show parameter undo_tablespace;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace = 'undotbs2 ';

System altered.

SQL> show parameter undo_tablespace;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_tablespace string undotbs2
SQL> drop tablespace undotbs1;

Tablespace dropped.