oracle更改数据文件位置

来源：互联网发布：工业4.0软件编辑：程序博客网时间：2024/05/01 01:12

环境：表空间t1

1,、将表空间t1置为offline状态

SQL> alter tablespace t1 offline;Tablespace altered.

2、查询数据文件位置

SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/PROD1/system01.dbf/u01/app/oracle/oradata/PROD1/sysaux01.dbf/u01/app/oracle/oradata/PROD1/undotbs01.dbf/u01/app/oracle/oradata/PROD1/users01.dbf/u01/app/oracle/oradata/PROD1/t101.dbf

3、复制t1对应的数据文件到更改位置

$ cp t101.dbf /home/oracle/t101.dbf

4、 rename修改表空间t1数据文件为新的位置，并修改控制文件

SQL> alter tablespace t1 rename datafile '/u01/app/oracle/oradata/PROD1/t101.dbf' to '/home/oracle/t101.dbf';Tablespace altered.

5、将表空间t1置为online状态

SQL> alter tablespace t1 online;Tablespace altered.

6、查询表空间位置（两个视图都可以查看，效果一样）

SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/PROD1/system01.dbf/u01/app/oracle/oradata/PROD1/sysaux01.dbf/u01/app/oracle/oradata/PROD1/undotbs01.dbf/u01/app/oracle/oradata/PROD1/users01.dbf/home/oracle/t101.dbf

SQL> select file_name,tablespace_name from dba_data_files;FILE_NAME------------------------------------------------------------------------------------------------------------------------------------------------------TABLESPACE_NAME------------------------------/u01/app/oracle/oradata/PROD1/system01.dbfSYSTEM/u01/app/oracle/oradata/PROD1/sysaux01.dbfSYSAUX/u01/app/oracle/oradata/PROD1/undotbs01.dbfUNDOTBS1FILE_NAME------------------------------------------------------------------------------------------------------------------------------------------------------TABLESPACE_NAME------------------------------/u01/app/oracle/oradata/PROD1/users01.dbfUSERS/home/oracle/t101.dbfT1

7、查询数据文件状态

SQL> select name,status from v$datafile;NAME------------------------------------------------------------------------------------------------------------------------------------------------------STATUS-------/u01/app/oracle/oradata/PROD1/system01.dbfSYSTEM/u01/app/oracle/oradata/PROD1/sysaux01.dbfONLINE/u01/app/oracle/oradata/PROD1/undotbs01.dbfONLINENAME------------------------------------------------------------------------------------------------------------------------------------------------------STATUS-------/u01/app/oracle/oradata/PROD1/users01.dbfONLINE/home/oracle/t101.dbfONLINE

0 0