Move Oracle tablespace Data file Scenario

Scenario One: Move regular tablespace (non-system tablespace) data files

/************** move regular tablespace data files when a database server is low on one disk space *****************/

1. Scope of Use: This operation is performed under the condition of normal operation of the database (open state), but it cannot move the data files in the system table space

2. Using the ALTER TABLESPACE command

1> Preparatory work:

C \ Sqlplus/nolog

Sql>connect Sys/[email Protected] Instance name as SYSDBA;

2> to take the tablespace offline:

Sql>alter tablespace TABS offline normal;

3> Copy the tablespace data file to a disk that is relatively free:

Example: Copy D:\YAG_DB\ABS.ora to E:\NEW\ABS.ora

4> execute the ALTER tablespace rename datafile command:

Sql>alter tablespace ABS rename datafile ' D:\YAG_DB\ABS.ora ' to ' E:\NEW\ABS.ora ';

After execution, the tablespace has changed (this command is actually a pointer to the Tablespace data file in the control file)

5> Bring the tablespace online:

Sql>alter tablespace ABS Online;

6> can now delete D:\YAG_DB\ABS.ora

7> Restart the database:

Sql>shutdown Immediate

Sql>startup

Scenario Two: Moving common system tablespace data files

/**************** The database server moves the system tablespace data file when there is insufficient disk space *****************/

1, scope of use: the operation in the database is in the Mount State to be able to move the system table space data files

2. Steps (Operation in Linux system):

1># su-oracle--Switch to Oracle user

2># Sqlplus/nolog

3>sql>conn Sys/pwd as Sysdba

4>sql>shutdown Immediate--When the database is in the startup state, first close

5>sql>startup mount-Starts the database as Mount State

6> the system tablespace data file that you want to move to a relatively free partition directory

eg:# MV/HOME/APP/ORACLE/ORADATA/ORACLE_SID/SYSTEM01.DBF/DATA/ZJPLUS3_DB/ZJPLUS3

7> using the ALTER DATABASE rename file command

Sql>alter database Rename file '/home/app/oracle/oradata/oracle_sid/system01.dbf ' to '/data/yag_db/oracle_sid/ SYSTEM01.DBF ';

8> Opening a database

Sql>alter Databse Open

9> Check that the file path of the system tablespace is correct

Sql>select file#,name,status from V$datafile; --system the file path for the system tablespace name should be the path you just changed

This article from "Qytag (upspringing)" blog, declined reprint!