Flashback drop 与index、view、trigger
----INDEX：
SQL> select * from t;
X Y
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
SQL> select index_name from user_indexes where table_name='T';
INDEX_NAME
------------------------------
IDX_T
SQL> select tablespace_name from user_segments where segment_name='T';
TABLESPACE_NAME
------------------------------
TBS_HUNT
SQL> show recyclebin;
SQL> drop table t;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$eOwVTG5NSLq2OaPn25/tmw==$0 TABLE 2012-06-02:22:48:23
SQL> flashback table t to before drop;
Flashback complete.
SQL> select index_name from user_indexes where table_name='T';
INDEX_NAME
------------------------------
BIN$7I46AOBuT6+vmx+RSeCU/g==$0
----闪回后的索引名称。不影响使用。
--- materialized
SQL> create table t3(id int,name varchar2(10)) tablespace tbs_hunt;
Table created.
SQL> insert into t3 values(1,'a');
1 row created.
SQL> insert into t3 values(2,'b');
1 row created.
SQL> insert into t3 values(3,'c');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t3 add constraint pk_t3 primary key(id);
Table altered.
SQL> select table_name from user_tables where table_name like '%T3%';
TABLE_NAME
------------------------------
T3
SQL> create materialized view log on t3;
Materialized view log created.
SQL> select table_name from user_tables where table_name like '%T3%';
TABLE_NAME
------------------------------
T3
MLOG$_T3
RUPD$_T3
SQL> drop table t3;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ --------------
T3 BIN$tSPTu5r1SHSlPBAtT2RwlA==$0 TABLE 2012-06-02:23:
SQL> select table_name from user_tables where table_name like '%T3%';
no rows selected
SQL> flashback table t3 to before drop;
Flashback complete.
SQL> select table_name from user_tables where table_name like '%T3%';
TABLE_NAME
------------------------------
T3
----闪回完成，物化视图并未闪回。
---- trigger
SQL> create or replace trigger trg_dml
2 after insert or update or delete on t3
3 begin
4 insert into t4 values('ddl',sysdate);
5 end;
6 /
Trigger created.
SQL> select * from t4;
TSCN TTIME
------------------------ -------------------
ddl 2012-06-02 23:28:43
ddl 2012-06-02 23:28:44
ddl 2012-06-02 23:28:45
SQL> show recyclebin;
SQL> drop table t3;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T3 BIN$Ks2gvzORT2CKLlk47PnMXQ==$0 TABLE 2012-06-02:23:29:10
SQL> select trigger_name from user_triggers;
TRIGGER_NAME
------------------------------
BIN$V8ooVXJwREa2TJX85y86ug==$0
SQL> flashback table t3 to before drop;
Flashback complete.
SQL> select trigger_name from user_triggers;
TRIGGER_NAME
------------------------------
BIN$V8ooVXJwREa2TJX85y86ug==$0
SQL> select trigger_name,status from user_triggers;
TRIGGER_NAME STATUS
------------------------------ --------
BIN$V8ooVXJwREa2TJX85y86ug==$0 ENABLED
---闪回成功，触发器名称乱码，但不影响使用。
