Shulou(Shulou.com)06/01 Report--

What this article shares to you is about how to change the data file location of the table space in Oracle, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Tablespace overview

The table space of Oracle belongs to the storage structure of Oracle. It is a kind of logical space for storing database objects (such as data files). It is the largest logical unit for information storage in Oracle. It also contains logical data types such as segments, regions, data blocks and so on. A tablespace is a space opened up in a database to store objects in the database. A database can be composed of multiple tablespaces. Oracle can be tuned through tablespaces. (unique advanced application of Oracle database)

Classification of table spaces

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

Temporary tablespace: stores the intermediate execution process of a database, such as saving order by database sorting and temporary data generated when grouped. The stored content is automatically released after the operation is completed. Temporary tablespaces are universal, and all users use TEMP as the temporary tablespace. Generally, there is only one temporary tablespace for tmp. If you need other temporary tablespaces, you can create them yourself.

UNDO tablespace: saves a copy of the data before it is modified. Store the old address where the transaction was modified, 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 roll back, restore and undo the data.

Introduction

The location and information of the data file of the Oracle database are recorded in the control file, and the rm or cp command will not and can not change the record of the control file. At this time, we must change and refresh the relevant information of the data file in the database control file through the alter operation, so as to ensure the normal operation of the database.

Operation method

1. Method 1

Main steps:

1. Offline tablespace: alter tablespace tablespace_name offline

2. Copy the data file to the new directory

3. Rename modifies the tablespace and modifies the control file

4. Online tablespace

Offline tablespace

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 modifies tablespace

SQL > alter tablespace cifdb rename datafile'/ u01qapplash oracle.oradataUnigram cifdb.dbf' to'/ u01applicaloradataplexoradataandCIFDBUniqcifdb.dbf'

Online tablespace

SQL > alter tablespace cifdb online

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'

two。 Method 2

Main steps:

1. Close the database

2. Copy the data file to the new location

3. Start the database to mount status

4. Modify the location of data files through SQL

5. Open the database

Shut down 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 location of the data file

SQL > alter database rename file'/ u01qapplash oracle.oradataUnigram cifdb.dbf' to'/ u01applicaloradataplexoradataandCIFDBUniqcifdb.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'; above is how to change the data file location of tablespaces in Oracle. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.