--添加测试表空间SQL> create tablespace xff datafile '/u01/oradata/xifenfei.dbf' size 10m autoextend on maxsize 100m;
Tablespace created.--查看数据文件位置SQL> select name from v$datafile;
NAME--------------------------------------------------------------------------------+DATA/tasm/system01.dbf+DATA/tasm/undotbs01.dbf+DATA/tasm/sysaux01.dbf+DATA/tasm/users01.dbf+DATA/tasm/example01.dbf/u01/oradata/xifenfei.dbf6 rows selected.
--创建测试表SQL> create table hr.a tablespace xff
2 as
3 select * from dba_tables;
Table created.
SQL> select count(*) from hr.a;
COUNT(*)
---------- 1580
--转移数据文件位置[oracle@localhost oradata]$ rman target /Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 04:30:22 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TASM (DBID=3032096031)
RMAN> sql 'alter tablespace xff offline';
using target database control file instead of recovery catalog
sql statement: alter tablespace xff offline
RMAN> backup as copy tablespace xff format '+DATA';
Starting backup at 27-JUN-11
allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=132 devtype=DISKchannel ORA_DISK_1: starting datafile copyinput datafile fno=00006 name=/u01/oradata/xifenfei.dbf
output filename=+DATA/tasm/datafile/xff.269.754893121 tag=TAG20110627T043200 recid=2 stamp=754893123
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-JUN-11
RMAN> switch tablespace xff to copy;
datafile 6 switched to datafile copy "+DATA/tasm/datafile/xff.269.754893121"
RMAN> sql 'alter tablespace xff online';
sql statement: alter tablespace xff online
--查看转移后的数据文件位置SQL> select name from v$datafile;
NAME--------------------------------------------------------------------------------+DATA/tasm/system01.dbf+DATA/tasm/undotbs01.dbf+DATA/tasm/sysaux01.dbf+DATA/tasm/users01.dbf+DATA/tasm/example01.dbf+DATA/tasm/datafile/xff.269.7548931216 rows selected.
--测试其中数据是否存在SQL> select count(*) from hr.a;
COUNT(*)
---------- 1580
--创建asm中文件别名ASMCMD> mkalias +DATA/tasm/datafile/xff.269.754893121 +DATA/tasm/xff01.dbf--文件重命名SQL> alter tablespace xff offline;
Tablespace altered.SQL> alter database rename file '+DATA/tasm/datafile/xff.269.754893121' to '+DATA/tasm/xff01.dbf';
Database altered.
SQL> alter tablespace xff online;
Tablespace altered.SQL> select name from v$datafile;
NAME--------------------------------------------------------------------------------+DATA/tasm/system01.dbf+DATA/tasm/undotbs01.dbf+DATA/tasm/sysaux01.dbf+DATA/tasm/users01.dbf+DATA/tasm/example01.dbf+DATA/tasm/xff01.dbf6 rows selected.
--手工删除原来数据[oracle@localhost oradata]$ rm xifenfei.dbf |
说明：可以在rman移植数据文件位置的时候，同时处理好别名，然后对表空间重命名，实现只需要表空间离线一次
转:http://www.xifenfei.com/2011/08/%e9%9d%9easm%e6%95%b0%e6%8d%ae%e6%96%87%e4%bb%b6%e7%a7%bb%e6%a4%8d%e5%88%b0asm.html