How to change the data file location of a tablespace in Oracle

How to change the data file location of a tablespace in Oracle

Table space Overview

Oracle tablespace is a logical space used to store database objects (such as data files) in Oracle. It is the largest logical unit for storing information in Oracle, it also contains logical data types such as segments, zones, and data blocks. A tablespace is a space opened up in a database for storing database objects. A database can consist of multiple tablespaces. You can optimize Oracle using tablespaces. (Unique advanced application of Oracle Database)

Table space Classification

Permanent tablespace: stores objects that require permanent storage in the database, such as 2D tables, views, stored procedures, and indexes.

Temporary tablespace: The intermediate execution process of the database, for example, saving the order by database sorting and temporary data generated during grouping. After the operation is completed, the stored content is automatically released. Temporary tablespaces are common and all users use TEMP as temporary tablespaces. Generally, only tmp is a temporary tablespace. If you need other temporary tablespaces, you can create them by yourself.

UNDO tablespace: copies before data modification are saved. The old address modified by the storage service, that is, the data before the modification. When we modify the data in a table, we will save the information before the modification, in order to perform the rollback, recovery, and revocation operations on the data.

Introduction

The location and information of the data files in the Oracle database are recorded in the control file. The rm or cp command does not and cannot change the control file records, in this case, you must use the alter operation to modify and refresh the data file information in the database control file to ensure that the database runs properly.

Procedure

1. method 1

Main steps:

1. offline tablespace: alter tablespace tablespace_name offline;

2. copy the data file to the new directory;

3. rename: Modify the tablespace and control file;

4. online tablespace;

Offline tablespace

SQL> alter tablespace cifdb offline;

Copy the data file to the new directory.

cp /u01/app/oracle/oradata/cifdb.dbf /u01/app/oracle/oradata/CIFDB/cifdb.dbf

Rename

SQL> alter tablespace cifdb rename datafile '/u01/app/oracle/oradata/cifdb.dbf' to '/u01/app/oracle/oradata/CIFDB/cifdb.dbf';

Online tablespace

SQL> alter tablespace cifdb online;

Check data files

SQL> select name from v$datafile;

Or

SQL> select file_name, tablespace_name from dba_data_files where tablespace_name='cifdb';

2. method 2

Main steps:

1. Shut down the database;

2. copy the data file to a new location;

3. Start the database to the mount status;

4. Modify the data file location through SQL;

5. Open the database;

Close Database

SQL> shutdown immediate;

Copy the data file to a new location

cp /u01/app/oracle/oradata/cifdb.dbf /u01/app/oracle/oradata/CIFDB/cifdb.dbf

Start database to mount status

SQL> startup mount;

Modify data file location

SQL> alter database rename file '/u01/app/oracle/oradata/cifdb.dbf' to '/u01/app/oracle/oradata/CIFDB/cifdb.dbf';

Open Database

SQL> alter database open;

Check data files

SQL> select name from v$datafile;

Or

SQL> select file_name, tablespace_name from dba_data_files where tablespace_name='cifdb';

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.