当误删除表空间,且没有备份,开启归档和闪回,可以使用flashback database恢复表空间
1.数据库版本
sys@TEST> select * from v$version;
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
2.要恢复的数据
sys@TEST> select count(*) from test.a;
COUNT(*)
13238
3.当前的scn
sys@TEST> select current_scn,to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from v$database;
CURRENT_SCN TO_CHAR(SYSDATE,’YY
543532 2015-03-05 16:17:32
4.模拟误删除表空间
sys@TEST> drop tablespace test including contents and datafiles;
Tablespace dropped.
sys@TEST> select count(*) from test.a; –已经查找不到表了
select count(*) from test.a
*
ERROR at line 1:
ORA-00942: table or view does not exist
5.关闭数据库并启动到mount;
sys@TEST> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST> startup mount;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 645924024 bytes
Database Buffers 415236096 bytes
Redo Buffers 5541888 bytes
Database mounted.
6.闪回数据库
sys@TEST> flashback database to scn ‘543532’;
Flashback complete.