table被drop之后，关联的index和materialized view log是否进入回收站

来源：互联网发布：软件服务指数编辑：程序博客网时间：2024/05/06 14:32

实验：TEST表空间下有一张TT表，OWNER=TEST用户

SQL> create table tt(col1 number primary key,col2 varchar2(10),col3 varchar2(10));

Table created.

SQL> CREATE MATERIALIZED VIEW LOG ON tt;

Materialized view log created.

SQL> insert into tt values(1,'a','a');

1 row created.

SQL> insert into tt values(2,'b','b');

1 row created.

SQL> commit;

Commit complete.

用sys用户查看TEST用户下的所有对象

SQL> select object_name,object_type from dba_objects where owner='TEST';

OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------
TT TABLE
MLOG$_TT TABLE
RUPD$_TT TABLE
SYS_C002977 INDEX

SQL> drop table tt;

Table dropped.

此时查询回收站，发现里面只有刚刚drop的表TT和主键自动生成的B-Tree索引

SQL> select object_name,original_name from recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$tAYBUUwJtwDgQAB/AQAWng==$0 SYS_C002977
BIN$tAYBUUwKtwDgQAB/AQAWng==$0 TT

SQL> flashback table tt to before drop;

Flashback complete.

将表TT闪回之后同时切换sys用户查看TEST所有的对象，发现只剩下表和索引，之前创建的materialized view log已经被删除。

SQL> select object_name,object_type from dba_objects where owner='TEST';

OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------
BIN$tAYBUUwKtwDgQAB/AQAWng==$0 TABLE
BIN$tAYBUUwJtwDgQAB/AQAWng==$0 INDEX

开始创建位图索引进行实验

SQL> create bitmap index idx_bt_tt on tt(col2);

Index created.

SQL> drop table tt;

Table dropped.

同样在表被drop之后查询回收站发现2种索引都位于回收站内，并且被重命名。

SQL> select object_name,original_name from recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$tAYBUUwMtwDgQAB/AQAWng==$0 IDX_BT_TT
BIN$tAYBUUwNtwDgQAB/AQAWng==$1 BIN$tAYBUUwJtwDgQAB/AQAWng==$0 （这一行是主键自动生成的B-Tree索引，之前因为drop过一次所以是系统自动命名）
BIN$tAYBUUwOtwDgQAB/AQAWng==$0 TT

SQL> flashback table tt to before drop;

Flashback complete.

将TT表闪回之后再次切换sys用户查询

由此可以看出在对表进行drop操作时，对应的index同样被逻辑删除到回收站内，所以可以和表一同被flashback，但是表上的materialized view log就被删除了。

下面测试joined index

SQL> create table t1 (id int primary key, ename varchar2(10),deptno int);

Table created.

SQL> create table t2(deptno int primary key, deptname varchar2(10), col_idx varchar2(10)); --col_idx就是即将在上面创建索引的列

Table created.

SQL> create bitmap index idx_test on t1(t2.col_idx) from t1,t2 where t1.deptno=t2.deptno;

Index created.

这时候用sys查看test对象有5个。

SQL> select object_name,object_type from dba_objects where owner='TEST';

OBJECT_NAME OBJECT_TYPE
------------------------------------------------------------------------------------------------------------ -------------------
T1 TABLE
T2 TABLE
SYS_C002978 INDEX
SYS_C002979 INDEX
IDX_TEST INDEX

SQL> drop table t1;

Table dropped.

SQL> select object_name, original_name from recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$tAYBUUwQtwDgQAB/AQAWng==$0 SYS_C002978
BIN$tAYBUUwRtwDgQAB/AQAWng==$0 T1

drop表T1，发现回收站里只有2个对象，表和主键上的索引，而bitmap joined index此时不在回收站里。

SQL> flashback table t1 to before drop;

Flashback complete.

将表T1闪回，然后用sys查看test所有对象

SQL> select object_name,object_type from dba_objects where owner='TEST';

OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------
T1 TABLE
T2 TABLE
SYS_C002979 INDEX
BIN$tAYBUUwQtwDgQAB/AQAWng==$0 INDEX

表和主键上的索引被flashback了，但是bitmap joined index被删除了。

重新创建bitmap joined index 测试T2被删除的时候是否保留

SQL> create bitmap index idx_test on t1(t2.col_idx) from t1,t2 where t1.deptno=t2.deptno;

Index created.

SQL> drop table t2;

Table dropped.

SQL> select object_name,original_name from recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$tAYBUUwTtwDgQAB/AQAWng==$0 SYS_C002979
BIN$tAYBUUwUtwDgQAB/AQAWng==$0 T2

回收站中仍然只有表T2和主键上的索引。

SQL> flashback table t2 to before drop;

Flashback complete.

SQL> select object_name, object_type from dba_objects where owner='TEST';

OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------
T1 TABLE
BIN$tAYBUUwUtwDgQAB/AQAWng==$0 TABLE
BIN$tAYBUUwTtwDgQAB/AQAWng==$0 INDEX
BIN$tAYBUUwQtwDgQAB/AQAWng==$0 INDEX