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

(1) method 1: offline tablespace (not related to the library)

* * *

* main steps: *

* 1. Offline tablespace: alter tablespace tablespace_name offline;*

* 2. Copy the data file to a new directory; *

* 3. Rename modifies the table space and modifies the control file; *

* 4. Online tablespace; *

* * *

Alter database datafile'/ u01 offline appAcle1 offline oradata

Alter database datafile'/ u01 online appAcle1 online oradata

Select tablespace_name,status from dba_tablespaces

Alter tablespace users read write

Take changing the location of the datafile / opt/oracle/oradata/ZERONE01.DBF of the tablespace zerone as an example

1. Offline tablespace TS_EMR to make tablespace TS_EMR offline

-

SQL > alter tablespace TS_EMR offline; (no drop note: if non-archive mode is followed by Drop)

2. Copy the data file to the new directory

--

Note:

* 1. If you check whether the new directory / home/oracle/oradata/zerone exists, create this path if it does not exist, and change the directory attributes:

* # mkdir-p / home/oracle/oradata/zerone

* # chown-R oracle:oinstall / home/oracle/oradata/zerone

Copy data file

# cp / opt/oracle/oradata/ZERONE01.DBF / home/oracle/oradata/zerone/ZERONE01.DBF

# chown oracle:oinstall / home/oracle/oradata/zerone/ZERONE01.DBF

3. Rename modifies the tablespace data file to the new location, and modifies the control file. If there are multiple data files in a tablespace, execute multiple statements as follows

SQL > alter tablespace TS_EMR rename datafile'E:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ EMR01.DBF' to'E:\ APP\ ADMINISTRATOR\ ORADATA\ EMR01.DBF'

Alter tablespace TS_EMR rename datafile'E:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ EMR02.DBF' to'E:\ APP\ ADMINISTRATOR\ ORADATA\ EMR02.DBF'

4. Online tablespace

-

SQL > alter tablespace TS_EMR online

Check:

SQL > select name from v$datafile

SQL > select file_name,tablespace_name from dba_data_files where tablespace_name='TS_EMR'

(2) method 2: SQL modifies the location of the data file (close the library)

* *

* main steps:

* 1. Close the database

* 2. Copy the data file to a new location

* 3. Start the database to mount status

* 4. Modify the location of data files through SQL

* 5. Open the database

* *

1. Close the database

-

SQL > shutdown immediate

2. Copy the data file to the new location

-

Same method one

3. Start the database to mount status

-

SQL > startup mount

4. Modify the location of data files through SQL

-

SQL > alter database rename file'E:\ APP\ ADMINISTRATOR\ ORADATA\ EMR01.DBF' to'E:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ EMR01.DBF'

Alter database rename file'E:\ APP\ ADMINISTRATOR\ ORADATA\ EMR02.DBF' to'E:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ EMR02.DBF'

5. Open the database

-

SQL > alter database open

Check:

SQL > select name from v$datafile

SQL > select file_name,tablespace_name from dba_data_files where tablespace_name='ZERONE'

How to see which tablespace the data file belongs to

(1) View the tablespace corresponding to the data file of the permanent tablespace

SQL > select TABLESPACE_NAME from dba_data_files where FILE_NAME=' data file full path'

(2) View the temporary tablespace corresponding to the data file of the temporary tablespace

SQL > select TABLESPACE_NAME from dba_temp_files where FILE_NAME=' data file full path'

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.