--添加测试表空间 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移植数据文件位置的时候,同时处理好别名,然后对表空间重命名,实现只需要表空间离线一次
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31448824/viewspace-2139466/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31448824/viewspace-2139466/