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

This article mainly introduces to you how Oracle changes the data file location of the table space. The article introduces it in detail through the example code. It has certain reference learning value for everyone's study or work. Friends who need it follow the small Let's learn together, I hope it can help everyone.

Table space overview

Oracle's table space is a storage structure in Oracle. It is a logical space used to store database objects (such as data files) and is the information storage in Oracle. The largest logical unit, which also contains logical data types such as segments, areas, and data blocks. Table space is a space opened in the database for storing database objects. A database can be composed of multiple table spaces. Tuning Oracle can be achieved through table spaces. (A unique advanced application of Oracle database)

Classification of table spaces

Permanent table space: stores objects that need to be permanently stored in the database, such as two-dimensional tables, views, stored procedures, and indexes.

Temporary table space: stores the intermediate execution process of the database, such as saving order by database sorting and temporary data generated during grouping. The stored content will be automatically released after the operation is completed. The temporary table space is universal, and all users use TEMP as the temporary table space. Generally, there is only one temporary table space, tmp. If you need other temporary table spaces, you can create it yourself.

UNDO table space: Save a copy of the data before modification. Stores the old address modified by the transaction, that is, the data before it was modified. When we modify the data in a table, we will save the information before the modification in order to perform rollback, recovery, and undo operations on the data.

Introduction

The location and information of the data files of the Oracle database are recorded in the control file. The rm or cp commands will not and cannot change the control file records. This The alter operation must be used to change and refresh the relevant information of the data files in the database control file to ensure that the database can operate normally.

Operation method

1. Method 1

Main steps:

1. Offline tablespace: alter tablespace tablespace_name offline;

2. Copy the data files to the new directory;

3. Rename the table space and modify the control file;

  4. Online table space;

offline table space

SQL> alter tablespace cifdb offline;

Copy data files to a new directory

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

rename modify table space

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

online table space

SQL> alter tablespace cifdb online;

Check data file

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. Close the database;

2. Copy the data file to a new location ;

3. Start the database to mount state;

4. Modify the data file location through SQL;

5. Open the database;

Close the 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 the database to mount state

SQL> startup mount;

Modify the data file location

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

Open the database

SQL> alter database open;

Check the data file

SQL> select name from v$datafile;

or

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

Related recommendations:

How to modify the order of fields in Oracle database tables

About oracle Summary of methods used for expansion

Oracle program development tips

The above is the detailed content of How to change the data file location of a tablespace in Oracle. For more information, please follow other related articles on the PHP Chinese website!