实验: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用户查询
SQL> select object_name,object_type from dba_objects where owner='TEST';
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------
TT TABLE
BIN$tAYBUUwNtwDgQAB/AQAWng==$1 INDEX
BIN$tAYBUUwMtwDgQAB/AQAWng==$0 INDEX
由此可以看出在对表进行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