SQL> create table emp1 as select * from scott.emp;
Table created.
SQL> select count(*) from emp1;
COUNT(*)
----------
14
SQL> delete from emp1 where rownum<=2;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp1;
COUNT(*)
----------
12
SQL> alter table emp1 enable row movement;
Table altered.
SQL> flashback table emp1 to timestamp(sysdate-2/1440);
Flashback complete.
SQL> alter table emp1 disable row movement;
Table altered.
SQL> select count(*) from emp1;
COUNT(*)
----------
14
SQL> drop table emp1;
Table dropped.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$5L5/jOdznrXgQKjABgEkyw==$0 TABLE
SQL> select * from user_objects where object_name='BIN$5L5/jOdznrXgQKjABgEkyw==$0';
no rows selected
SQL> select owner,segment_name from dba_segments where segment_name='BIN$5L5/jOdznrXgQKjABgEkyw==$0';
OWNER SEGMENT_NAME
------------------------------ --------------------
TEST BIN$5L5/jOdznrXgQKjA
BgEkyw==$0
--创建分区表
1 create table emp2
2 partition by range(deptno)
3 (partition dept10 values less than(20),
4 partition dept20 values less than(30),
5 partition other values less than(maxvalue))
6* as select * from scott.emp
SQL> /
Table created.
SQL> select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID from user_objects where object_name='EMP2';
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
-------------------- ------------------------------ ----------
EMP2 OTHER 84368
EMP2 DEPT20 84367
EMP2 DEPT10 84366
EMP2 84365
SQL> SELECT segment_name,partition_name FROM USER_SEGMENTS WHERE SEGMENT_NAME='EMP2';
SEGMENT_NAME PARTITION_NAME
-------------------- ------------------------------
EMP2 DEPT10
EMP2 DEPT20
EMP2 OTHER
SQL> alter table emp2 drop partition other;
Table altered.
SQL> select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID from user_objects where object_name='EMP2';
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
-------------------- ------------------------------ ----------
EMP2 DEPT20 84367
EMP2 DEPT10 84366
EMP2 84365
SQL> SELECT segment_name,partition_name FROM USER_SEGMENTS WHERE SEGMENT_NAME='EMP2';
SEGMENT_NAME PARTITION_NAME
-------------------- ------------------------------
EMP2 DEPT10
EMP2 DEPT20
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP2 TABLE
LEO3 TABLE
LEO4 TABLE
SYS_TEMP_FBT TABLE
T TABLE
TESTEMP TABLE
TESTEMP1 TABLE
T_EMP TABLE
T_TEST1 TABLE
T_TEST2 TABLE
T_TEST4 TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T_TEST5 TABLE
T_TEST6 TABLE
V_EMP VIEW
V_EMP1 VIEW
15 rows selected.
SQL> alter table emp2 enable row movement;
Table altered.
SQL> flashback table emp1 to timestamp(sysdate-2/1440);
flashback table emp1 to timestamp(sysdate-2/1440)
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> flashback table emp2 to timestamp(sysdate-1/1440);
Flashback complete.
SQL> alter table emp2 disable row movement;
Table altered.
SQL> select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID from user_objects where object_name='EMP2';
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
-------------------- ------------------------------ ----------
EMP2 DEPT20 84367
EMP2 DEPT10 84366
EMP2 84365
SQL> SELECT segment_name,partition_name FROM USER_SEGMENTS WHERE SEGMENT_NAME='EMP2';
SEGMENT_NAME PARTITION_NAME
-------------------- ------------------------------
EMP2 DEPT10
EMP2 DEPT20
结论:
drop table 开了purge会保存回收站数据,可以利用flashback table恢复,delete是DML操作,可以利用flashback table按时间点来恢复。
而drop partition是DDL操作,并且不会为drop partition保留recyclebin信息,所以上边两个flashback table的特性都不能使用