Solutions for moving Oracle tablespace data files
Solution 1: Move regular tablespace (non-system tablespace) data files
/************** When the disk space of a database server is insufficient, move the regular tablespace data file ************* ****/
1. Scope of use: this operation is performed under the normal operation (open state) of the database, but the data files in the system tablespace cannot be moved.
2. Steps (in windows ):
1> preparations:
C: \ sqlplus/nolog
SQL> connect sys/qazwsxedc @ Instance name as sysdba;
2> take the tablespace offline:
SQL> alter tablespace TABS offline normal;
3> copy the tablespace data file to a relatively idle Disk:
For example, Copy D: \ YAG_DB \ ABS. ora to E: \ NEW \ ABS. ora.
4> run 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 been changed (this command is used to modify the pointer of the tablespace data file in the control file)
5> bring the tablespace online:
SQL> alter tablespace ABS online;
6> delete D: \ YAG_DB \ ABS. ora.
7> restart the database:
SQL> shutdown immediate
SQL> startup
Solution 2: Move tablespace data files in a common system
/*************** When the disk space of a database server is insufficient, move the system tablespace data file *********** ******/
1. Scope of use: this operation can be performed only when the database is in the mount state, and the data files in the system tablespace can be moved.
2. Steps (Operations in Linux ):
1> # su-Oracle -- switch to the oracle user
2> # sqlplus/nolog
3> SQL> conn sys/pwd as sysdba
4> SQL> shutdown immediate -- when the database is started, it is disabled first.
5> SQL> startup mount -- start the database as mount
6> copy the system tablespace data file to the idle partition directory.
Eg: # mv/home/app/oracle/oradata/ORACLE_SID/system01.dbf/data/YAG_DB/ORACLE_SID
7> run 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> open the database
SQL> alter databse open
9> check whether the file path of the system tablespace is correct
SQL> select file #, name, status From v $ datafile; -- the file path name of the system tablespace should be the path just changed
Installing Oracle 12C in Linux-6-64
RHEL6.4 _ 64 install a single instance Oracle 12cR1
New Features of Oracle 12C: Paging Query
12 new features of Oracle 12C