oracle drop index pk,oracle 学习笔记 Flashback drop 与index、view、trigger

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

---闪回成功，触发器名称乱码，但不影响使用。