下面是一个测试过程
1。首先创建裸设备：
root@ultra66 # cd /opt/app/oradata/test
root@ultra66 # ls
control01.ctl control03.ctl redo04.log system01.dbf tools01.dbf users01.dbf
control02.ctl indx01.dbf redo05.log temp01.dbf undotbs01.dbf
root@ultra66 # ln -s /dev/rdsk/c0t0d0s3 hts.dbf
root@ultra66 # ls -l
total 747634
-rw-r----- 1 oracle oinstall 1613824 May 26 08:46 control01.ctl
-rw-r----- 1 oracle oinstall 1613824 May 26 08:46 control02.ctl
-rw-r----- 1 oracle oinstall 1613824 May 26 08:46 control03.ctl
lrwxrwxrwx 1 root other 18 May 26 08:46 hts.dbf -> /dev/rdsk/c0t0d0s3
-rw-r----- 1 oracle oinstall 15736832 May 26 07:05 indx01.dbf
-rw-r----- 1 oracle oinstall 10486272 May 26 07:05 redo04.log
-rw-r----- 1 oracle oinstall 10486272 May 26 08:45 redo05.log
-rw-r----- 1 oracle oinstall 199237632 May 26 08:14 system01.dbf
-rw-r----- 1 oracle oinstall 10493952 May 26 06:50 temp01.dbf
-rw-r----- 1 oracle oinstall 10493952 May 26 07:05 tools01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 26 08:44 undotbs01.dbf
-rw-r----- 1 oracle oinstall 26222592 May 26 07:05 users01.dbf
2。创建一个表空间作为实验文件
root@ultra66 # su - oracle
Sun Microsystems Inc. SunOS 5.8 Generic Patch February 2004
You have new mail.
ultra66% sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 5月 26 08:47:18 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> create tablespace test datafile '/opt/app/oradata/test/ht1.dbf' size 6M;
Tablespace created.
3。使用alter database create datafile ... as ...来迁移，首先要把datafile offline
SQL> alter database create datafile '/opt/app/oradata/test/ht1.dbf' as '/opt/app/oradata/test/hts.dbf';
alter database create datafile '/opt/app/oradata/test/ht1.dbf' as '/opt/app/oradata/test/hts.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 6 - file is in use or recovery
ORA-01110: data file 6: '/opt/app/oradata/test/ht1.dbf'
SQL> alter database datafile '/opt/app/oradata/test/ht1.dbf' offline;
alter database datafile '/opt/app/oradata/test/ht1.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter tablespace test offline;
Tablespace altered.
SQL> alter database create datafile '/opt/app/oradata/test/ht1.dbf' as '/opt/app/oradata/test/hts.dbf';
Database altered.
4。检查一下
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/app/oradata/test/system01.dbf
/opt/app/oradata/test/undotbs01.dbf
/opt/app/oradata/test/indx01.dbf
/opt/app/oradata/test/tools01.dbf
/opt/app/oradata/test/users01.dbf
/opt/app/oradata/test/hts.dbf
6 rows selected.
5。把表空间online，需要首先recover
SQL> alter tablespace test online;
alter tablespace test online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/opt/app/oradata/test/hts.dbf'
SQL> alter database recover datafile 6;
Database altered.
SQL> alter tablespace test online;
Tablespace altered.
SQL>
6。ok，完成