Solutions for moving Oracle tablespace data files

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.