FLASHBACK DATABASE(2) 闪回误删除的表空间

SQL> create tablespace test01
  2  datafile 'd:\oradata\orcl\test01a.dbf' size 10m;
create tablespace test01
*
第 1 行出现错误:
ORA-01119: 创建数据库文件 'd:\oradata\orcl\test01a.dbf' 时出错
ORA-27038: 所创建的文件已存在
OSD-04010: 指定了 <create> 选项, 但文件已经存在

SQL> create tablespace test01
  2  datafile 'd:\oradata\orcl\test01a.dbf' size 10m reuse;

表空间已创建。

SQL> set time on ;
16:21:26 SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME                                                                
------------------------------                                                 
SYSTEM                                                                         
UNDOTBS1                                                                       
SYSAUX                                                                         
TEMP                                                                           
USERS                                                                          
EXAMPLE                                                                        
TEST01                                                                         

已选择7行。

16:21:37 SQL> create table test01
16:22:24   2  (
16:22:25   3  c1 number
16:22:29   4  )
16:22:30   5  tablespace test01;

表已创建。

16:22:36 SQL> insert into test01 values (1);

已创建 1 行。

16:22:47 SQL> insert into test01 values (2);

已创建 1 行。

16:22:49 SQL> insert into test01 values (3);

已创建 1 行。

16:22:51 SQL> commit;

提交完成。

16:22:53 SQL> select * from test01;

        C1                                                                     
----------                                                                     
         1                                                                     
         2                                                                     
         3                                                                     

16:22:59 SQL> drop tablespace test01 including contents and datafiles
16:23:26   2  ;

表空间已删除。

16:23:31 SQL> select * from test01;
select * from test01
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在

16:23:40 SQL> shutdown abort;
ORACLE 例程已经关闭。
16:23:53 SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。

Total System Global Area  167772160 bytes                                      
Fixed Size                  1247900 bytes                                      
Variable Size             100664676 bytes                                      
Database Buffers           62914560 bytes                                      
Redo Buffers                2945024 bytes                                      
数据库装载完毕。
16:24:12 SQL> flashback database to timestamp to_timestamp('2011-12-28 16:22:58','yyyy-mm-dd hh24:mi:ss');
flashback database to timestamp to_timestamp('2011-12-28 16:22:58','yyyy-mm-dd hh24:mi:ss')
*
第 1 行出现错误:
ORA-38795: 警告: FLASHBACK 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01245: RESETLOGS 完成时脱机文件 7 将丢失
ORA-01111: 数据文件 7 名称未知 - 请重命名以更正文件
ORA-01110: 数据文件 7: 'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00007'

16:25:37 SQL> alter database create datafile 7 as 'd:\oradata\orcl\test01a.dbf';

数据库已更改。

16:27:35 SQL> alter database datafile 7 online;

数据库已更改。

16:27:53 SQL> recover database until time  '2011-12-28:16:22:58';
完成介质恢复。
16:29:37 SQL> alter database open read only;

数据库已更改。

16:30:08 SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME                                                                
------------------------------                                                 
SYSTEM                                                                         
UNDOTBS1                                                                       
SYSAUX                                                                         
TEMP                                                                           
USERS                                                                          
EXAMPLE                                                                        
TEST01                                                                         

已选择7行。

16:30:25 SQL> select name from v$datafile;

NAME                                                                           
--------------------------------------------------------------------------------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF                             
F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF                            
F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF                             
F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF                              
D:\ORADATA\ORCL\EXAMPLE01.DBF                                                  
D:\ORADATA\ORCL\USER02.DBF                                                     
D:\ORADATA\ORCL\TEST01A.DBF                                                    
F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS02.DBF                            

已选择8行。

16:30:39 SQL> select * from test01;

        C1                                                                     
----------                                                                     
         1                                                                     
         2                                                                     
         3                                                                     

16:30:55 SQL> startup force
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。

Total System Global Area  167772160 bytes                                      
Fixed Size                  1247900 bytes                                      
Variable Size             100664676 bytes                                      
Database Buffers           62914560 bytes                                      
Redo Buffers                2945024 bytes                                      
数据库装载完毕。
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

16:31:20 SQL> alter database open resetlogs;

数据库已更改。

16:31:52 SQL> select * from test01;

        C1                                                                     
----------                                                                     
         1                                                                     
         2                                                                     
         3                                                                     

16:32:03 SQL> drop table test01;

表已删除。

16:32:15 SQL> drop tablespace test01 including contents and datafiles;

表空间已删除。