OracleDBA之路之Tabespace(八)

移动表空间的数据文件
方法一 对tablespace 的操作
首先 先把tablespace offline
然后 把表空间中的dbf文件 移动的新的位置
执行 alter tablespace xxxx rename datafile 'oldDataFile' to 'newDataFile'
最后 把tablespace online
实验1
把 myts02.dbf 文件移动到/u01/oradata/
SQL> alter tablespace mytablespace offline;
 
Tablespace altered.
SQL> !
[oracle@oracle11g ~]$ cd /u01/oradata/houzhh/
[oracle@oracle11g houzhh]$ mv myts02.dbf  ..
[oracle@oracle11g houzhh]$ cd ..
[oracle@oracle11g oradata]$ ls
db01  houzhh  myts02.dbf  redo04a.rdo
SQL> alter tablespace mytablespace rename datafile '/u01/oradata/houzhh/myts02.dbf' to '/u01/oradata/myts02.dbf';

 
Tablespace altered.

SQL> alter tablespace mytablespace online;
 
Tablespace altered.
 
SQL> select file_name,tablespace_name as tname,bytes from dba_data_files;
 
FILE_NAME                      TNAME                               BYTES
------------------------------ ------------------------------ ----------
 
/u01/oradata/houzhh/myts01.dbf MYTABLESPACE                    104857600
/u01/oradata/myts02.dbf        MYTABLESPACE                    209715200
  
SQL>

方法二 使用alter database 方法
首先 把数据文件移动到新的路劲
然后 把数据库启动到mount状态
执行 alter database file rename 'oldDatafile' to 'new Data File'
实验2
把/u01/oradata/myts02.dbf 移动到/u01/oradata/houzhh/myts02.dbf
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@oracle11g ~]$ cd /u01/oradata/
[oracle@oracle11g oradata]$ mv myts02.dbf houzhh/
[oracle@oracle11g oradata]$ cd houzhh/
[oracle@oracle11g houzhh]$ ls
control01.ctl  mytemp01.dbf  myts02.dbf  redo02.log  redo04.rdo    system01.dbf  undotbs01.dbf
example01.dbf  myts01.dbf    redo01.log  redo03.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@oracle11g houzhh]$ exit
exit
 
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             331352448 bytes
Database Buffers           83886080 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL>
SQL> alter database rename file '/u01/oradata/myts02.dbf' to '/u01/oradata/houzhh/myts02.dbf';
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> select file_name,tablespace_name as tname,bytes from dba_data_files;
 
FILE_NAME                      TNAME                               BYTES
------------------------------ ------------------------------ ----------
 
/u01/oradata/houzhh/myts01.dbf MYTABLESPACE                    104857600
/u01/oradata/houzhh/myts02.dbf MYTABLESPACE                    209715200
  
SQL>