--添加测试表空间 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.dbf 6 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_1 channel ORA_DISK_1: sid=132 devtype=DISK channel ORA_DISK_1: starting datafile copy input 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.754893121 6 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.dbf 6 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