环境准备:
1.连接数据库
SQL> conn /as sysdba
Connected.
2.创建一个新的test表空间
SQL> create tablespace test datafile 'D:\test.dbf' size 20M;
3.创建一个新的tempTable表指定为test表空间
SQL> create table tempTable (id int) tablespace test;
4.插入两条测试数据
SQL> insert into tempTable values (1);
1 row created.
SQL> insert into tempTable values (2);
1 row created.
SQL> select * from tempTable;
ID
----------
1
2
模拟环境,手动将D:\test.dbf文件删除
1.需要先将表空间test置于offline状态,否则无法删除
SQL> alter tablespace test offline;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name =
'TEST';
TABLESPACE_NAME STATUS
------------------------------ ---------
TEST OFFLINE
2.手动删除D:\test.dbf文件,此时再对tempTable表查询会报如下错误
SQL> select * from tempTable;
select * from tempTable
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: 'D:\TEST.DBF'
开始进行数据恢复
1.重新创建删除的数据文件
SQL> alter database create datafile 9;
Database altered.
2.进行recover数据恢复
SQL> recover datafile 9;
Media recovery complete.
3.置换表空间状态,恢复完成
SQL> alter tablespace test online;
Tablespace altered.
SQL> select * from tempTable;
ID
----------
1
2