删用户删表空间的操作还能flashback回来吗？

今日2017-6-27   坐标西安    天气晴朗，很热

今天中午在某微信群里，有人提问以下两条操作还能恢复吗？而且是在没有开归档，而后有人提问是否开了闪回？

SQL> drop user aaa cascade;
SQL> drop tablespace asd including contents and datafiles;

他这里没有指明闪回是闪回查询？闪回表？闪回数据库？那我们就一一看（我这里假设就算是这个用户下就一张表）

首先看看闪回查询：

SQL> SQL> create table aaa.a1(id number);

Table created.

SQL> insert into aaa.a1 values(3);

1 row created.
SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
3575965

SQL> drop user aaa cascade;

User dropped.

SQL> select * from aaa.a1 as of scn 3575965;
select * from aaa.a1 as of scn 3575965
                  *
ERROR at line 1:
ORA-00942: table or view does not exist
可以看出闪回查询是无效的，其实你要是懂一点闪回查询首先可以排除掉，因为闪回查询是基于undo的，而且undo受ddl影响的
再看看闪回表（flashback table）
SQL> flashback table aaa.a1 to before drop;
flashback table aaa.a1 to before drop
*
ERROR at line 1:
ORA-01435: user does not exist

drop user cascade是不会吧表放入回收站的，那么也可以说明flashback也是不行的

最后在看看闪回数据库，其实闪回数据库的开启的基本要求就是开启归档，那么就呵呵了，所以闪回这条路是不通的

就算是开了闪回数据库（flashback database，当然包括开启归档），那么就有用了吗？

SQL> select dbms_flashback.get_system_change_number from dual;


GET_SYSTEM_CHANGE_NUMBER
------------------------
3574600

SQL> drop user aaa cascade;

User dropped.

SQL> drop tablespace asd including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  889389056 bytes
Fixed Size    2258360 bytes
Variable Size  574622280 bytes
Database Buffers  306184192 bytes
Redo Buffers    6324224 bytes
Database mounted.
SQL> flashback database to scn 3574600;                        //闪回到误操作前

Flashback complete.

SQL> alter database open read only;                                 //以read only模式打开

Database altered.

SQL> select * from aaa.a1;
select * from aaa.a1
                  *
ERROR at line 1:
ORA-00376: file 10 cannot be read at this time
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00010'

这里不得不提一下，闪回数据库的本质了，flashback database用来将数据库中的数据恢复到之前的某个时间点，而非介质恢复。这里的 drop tablespace  including contents and datafiles;（加不加including datafile一样）已经把数据文件都删除了。所以就算是开启了闪回数据库也是没用的。

那么要是在上面的情况下，没有drop tablespace  including datafile（在闪回数据库模式下，只跑了drop user cascade）

啥都不说先操作吧：

SQL> SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
3581891

SQL> drop user db1 cascade;

User dropped.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  889389056 bytes
Fixed Size    2258360 bytes
Variable Size  574622280 bytes
Database Buffers  306184192 bytes
Redo Buffers    6324224 bytes
Database mounted.
SQL> flashback database to scn 3581891;

Flashback complete.

SQL> alter database open read  only;

Database altered.

SQL> select * from db1.milktwo;

ID NAME
---------- ----------------------
33 kk
2 hh

再来句废话，在闪回数据库（flashback database）开启的情况下，只是drop user cascade，没有drop tablespace  including datafile操作是可以通过闪回数据库恢复的数据的。