--查看当前情况

SQL> select count(*) from hr.a;

 COUNT(*)

----------

     1580

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

+DG2/tasm/datafile/xff.256.754902279

6 rows selected.

--创建目录

SQL>  create directory asmsrc as '+DG2/TASM/datafile';

Directory created.

SQL>  create directory osdesc as '/u01/oradata';

Directory created.

--表空间离线

SQL> alter tablespace xff offline;

Tablespace altered.

--数据文件迁移

SQL> begin

 2      dbms_file_transfer.copy_file('ASMSRC',

 3      'xff.256.754902279',

 4      'OSDESC',

 5      'xff_new_2.dbf');

 6  END;

 7  /

PL/SQL procedure successfully completed.

--修改数据库中datafile路径

SQL> alter database rename file

 2             '+DG2/tasm/datafile/xff.256.754902279'

 3             to '/u01/oradata/xff_new_2.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

/u01/oradata/xff_new_2.dbf

6 rows selected.

SQL> select count(*) from hr.a;

 COUNT(*)

----------

     1580

--删除asm中文件

ASMCMD> rm XFF.256.754902279

ORA-15032: not all alterations performed

ORA-15028: ASM file '+dg2/tasm/datafile/XFF.256.754902279' not dropped;

currently being accessed (DBD ERROR: OCIStmtExecute)

--不能直接删除，关闭数据库后可以删除（应该是bug）

oracle视频教程请关注: