Oracle技术之flashback drop与索引

9AMC

　　今天在跟朋友讨论了如下话题：
　　《有一张表上建立索引，DROP该表，又闪回删除回来，请问，这张表上的索引还能用吗？如果有变化那是什么？》
　　我进行了如下测试：
　　sys@MAA> select * from v$version;
　　BANNER
　　----------------------------------------------------------------------------------------------------

　　Oracle Database 11g Enterprise Edition>
　　PL/SQL>　　CORE    11.2.0.3.0      Production
　　TNS for Linux: Version 11.2.0.3.0 - Production
　　NLSRTL Version 11.2.0.3.0 - Production
　　-- 我的TEST2表上有两个索引
　　luocs@MAA> select index_name, status from user_indexes where table_name='TEST2';
　　INDEX_NAME                                                   STATUS
　　------------------------------------------------------------ ----------------
　　INX_TEST2_ZH                                                 VALID
　　INX_TEST2                                                    VALID
　　luocs@MAA> set autot trace exp stat

　　luocs@MAA> select count(*) from test2 where>　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 3565898414
　　----------------------------------------------------------------------------------

　　|>　　----------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT  |              |     1 |     5 |     2   (0)| 00:00:01 |
　　|   1 |  SORT AGGREGATE   |              |     1 |     5 |            |          |
　　|*  2 |   INDEX RANGE SCAN| INX_TEST2_ZH |     2 |    10 |     2   (0)| 00:00:01 |
　　----------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - access("ID"=1)
　　Statistics
　　----------------------------------------------------------
　　1  recursive calls
　　0  db block gets
　　2  consistent gets
　　0  physical reads

　　0  redo>　　525  bytes sent via SQL*Net to client
　　523  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　-- DROP 表TEST2
　　luocs@MAA> drop table test2;
　　Table dropped.
　　-- 索引也被删除
　　luocs@MAA> select index_name, status from user_indexes where table_name='TEST2';
　　no rows selected
　　-- 这时候查看回收站，我们看到表的删除记录，却没有看到索引的
　　luocs@MAA> show recyclebin
　　ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
　　---------------- ------------------------------ ------------ -------------------
　　TEST2            BIN$048hXdZTf0HgQwEAAH/4UQ==$0 TABLE        2013-01-18:19:20:06
　　-- 进行闪回删除
　　luocs@MAA> flashback table test2 to before drop;
　　Flashback complete.
　　-- 这时候又能看到索引信息，也是可用状态，却索引名称改变，依然使用回收站里的名字
　　luocs@MAA> select index_name, status from user_indexes where table_name='TEST2';
　　INDEX_NAME                                                   STATUS
　　------------------------------------------------------------ ----------------
　　BIN$048hXdZSf0HgQwEAAH/4UQ==$0                               VALID
　　BIN$048hXdZRf0HgQwEAAH/4UQ==$0                               VALID
　　-- 我们也看到该索引能够正常使用
　　luocs@MAA> set autot trace exp

　　luocs@MAA> select count(*) from test2 where>　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 3980542661
　　----------------------------------------------------------------------------------------------------

　　|>　　----------------------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT  |                                |     1 |     5 |     2   (0)| 00:00:01 |
　　|   1 |  SORT AGGREGATE   |                                |     1 |     5 |            |          |
　　|*  2 |   INDEX RANGE SCAN| BIN$048hXdZSf0HgQwEAAH/4UQ==$0 |     2 |    10 |     2   (0)| 00:00:01 |
　　----------------------------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - access("ID"=1)
　　-- 我们可以RENAME索引名字以恢复

　　luocs@MAA>>
　　Index>
　　luocs@MAA>>
　　Index>　　luocs@MAA> select index_name, status from user_indexes where table_name='TEST2';
　　INDEX_NAME                                                   STATUS
　　------------------------------------------------------------ ----------------
　　INX_TEST2_ZH                                                 VALID
　　INX_TEST2                                                    VALID
　　因此，在表被DROP掉后，索引也被放到回收站里，再我们flashback drop之后，索引也被还原，也能正常使用，但索引名却没有还原过来。
　　那这里有个问题存在，既然索引页被放到回收站里，为什么执行show recyclebin的时候没有看到索引信息？
　　OK，我通过如下方式解释：
　　-- 通过10046事件分析下show recyclebin到底干了什么

　　luocs@MAA>>
　　Session>　　luocs@MAA> show recyclebin

　　luocs@MAA>>
　　Session>　　luocs@MAA> select value from v$diag_info where name = 'Default Trace File';
　　VALUE
　　----------------------------------------------------------------------------------------------------
　　/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_32580.trc
　　-- 我在TRACE内容里找到如下语句，这里重点看WHERE子句CAN_UNDROP='YES'
　　SELECT ORIGINAL_NAME ORIGNAME_PLUS_SHOW_RECYC,OBJECT_NAME
　　OBJECTNAME_PLUS_SHOW_RECYC,TYPE OBJTYPE_PLUS_SHOW_RECYC,DROPTIME
　　DROPTIME_PLUS_SHOW_RECYC
　　FROM
　　USER_RECYCLEBIN WHERE CAN_UNDROP='YES' ORDER BY ORIGINAL_NAME,DROPTIME DESC,
　　OBJECT_NAME
　　-- 这里CAN_UNDROP='YES' 起到作用了
　　-- 我们看一下recyclebin$表创建语法与注释信息(Oracle 11g开始recyclebin$表创建语句在dsqlddl.bsq脚本里，位于$ORACLE_HOME/rdbms/admin)
　　create table recyclebin$
　　(
　　obj#                  number not null,           /* original object number */
　　owner#                number not null,                /* owner user number */
　　original_name         varchar2(32),                /* Original Object Name */
　　operation             number not null,            /* Operation carried out */
　　/* 0 -> DROP */
　　/* 1 -> TRUNCATE (not supported) */
　　type#                 number not null,          /* object type (see KQD.H) */
　　ts#                   number,                         /* tablespace number */
　　file#                 number,                /* segment header file number */
　　block#                number,               /* segment header block number */
　　droptime              date,                /* time when object was dropped */
　　dropscn               number,           /* SCN of Tx which caused the drop */
　　partition_name        varchar2(32),       /* Name of the partition dropped */
　　/* NULL otherwise */
　　flags                 number,               /* flags for undrop processing */
　　related               number not null,    /* obj one level up in heirarchy */
　　bo                    number not null,                      /* base object */
　　purgeobj              number not null,   /* obj to purge when purging this */
　　base_ts#              number,            /* Base objects Tablespace number */
　　base_owner#           number,                 /* Base objects owner number */
　　space                 number,       /* number of blocks used by the object */
　　con#                  number,       /* con#, if index is due to constraint */
　　spare1                number,
　　spare2                number,
　　spare3                number
　　)
　　/
　　-- 其中flags的注释内容是flags for undrop processing，我们可以查这张表，然后查看表和索引对应的flags分别多少
　　-- 再drop 表test2，然后查看recyclebin$表
　　sys@MAA> select USER_ID, USERNAME from dba_users where USERNAME='LUOCS';
　　USER_ID USERNAME
　　---------- ------------------------------------------------------------
　　51 LUOCS
　　sys@MAA> select OBJ#, OWNER#, TYPE#, OPERATION, DROPTIME, DROPSCN, FLAGS from recyclebin$ where OWNER#=51;
　　OBJ#     OWNER#      TYPE#  OPERATION DROPTIME                   DROPSCN      FLAGS
　　---------- ---------- ---------- ---------- ----------------------- ---------- ----------
　　24248         51          2          0 18-JAN-2013 21:02:24       1134044         18
　　24352         51          2          0 18-JAN-2013 21:02:24       1134047         18
　　24247         51          1          0 18-JAN-2013 21:02:24       1134051         30
　　-- 可知表对应的flags为30，索引对应的flags为18。另外，OBJ#为DROP对象前的对象编号，这个在下面闪回删除之后再证明
　　OK 有点跑偏了。
　　-- 我们通过10046分析出show recyclebin其实是执行下面一段SQL语句：
　　SELECT ORIGINAL_NAME ORIGNAME_PLUS_SHOW_RECYC,OBJECT_NAME
　　OBJECTNAME_PLUS_SHOW_RECYC,TYPE OBJTYPE_PLUS_SHOW_RECYC,DROPTIME
　　DROPTIME_PLUS_SHOW_RECYC
　　FROM
　　USER_RECYCLEBIN WHERE CAN_UNDROP='YES' ORDER BY ORIGINAL_NAME,DROPTIME DESC,
　　OBJECT_NAME
　　-- 我们将CAN_UNDROP='YES'去掉，然后执行：
　　luocs@MAA> SELECT ORIGINAL_NAME ORIGNAME_PLUS_SHOW_RECYC,OBJECT_NAME
　　2    OBJECTNAME_PLUS_SHOW_RECYC,TYPE OBJTYPE_PLUS_SHOW_RECYC,DROPTIME
　　3    DROPTIME_PLUS_SHOW_RECYC
　　4  FROM
　　5   USER_RECYCLEBIN ORDER BY ORIGINAL_NAME,DROPTIME DESC,
　　6    OBJECT_NAME
　　7  /
　　ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
　　---------------- ------------------------------ ------------ -------------------
　　INX_TEST2        BIN$05C5BoYUf0TgQwEAAH+5aw==$0 INDEX        2013-01-18:21:02:24
　　INX_TEST2_ZH     BIN$05C5BoYVf0TgQwEAAH+5aw==$0 INDEX        2013-01-18:21:02:24
　　TEST2            BIN$05C5BoYWf0TgQwEAAH+5aw==$0 TABLE        2013-01-18:21:02:24
　　-- 从这里我们也获得了原对象名以及回收站里的对象名，因此RENAME的时候可以找对对象。
　　-- 将CAN_UNDROP 设置为 'NO'再执行：
　　luocs@MAA> SELECT ORIGINAL_NAME ORIGNAME_PLUS_SHOW_RECYC,OBJECT_NAME
　　2    OBJECTNAME_PLUS_SHOW_RECYC,TYPE OBJTYPE_PLUS_SHOW_RECYC,DROPTIME
　　3    DROPTIME_PLUS_SHOW_RECYC
　　4  FROM
　　5   USER_RECYCLEBIN WHERE CAN_UNDROP='NO' ORDER BY ORIGINAL_NAME,DROPTIME DESC,
　　6    OBJECT_NAME
　　7  /
　　ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
　　---------------- ------------------------------ ------------ -------------------
　　INX_TEST2        BIN$05C5BoYUf0TgQwEAAH+5aw==$0 INDEX        2013-01-18:21:02:24
　　INX_TEST2_ZH     BIN$05C5BoYVf0TgQwEAAH+5aw==$0 INDEX        2013-01-18:21:02:24
　　-- 进行flashback drop
　　luocs@MAA> flashback table test2 to before drop;
　　Flashback complete.
　　-- 再查询已经看不到内容了
　　luocs@MAA> SELECT ORIGINAL_NAME ORIGNAME_PLUS_SHOW_RECYC,OBJECT_NAME
　　2    OBJECTNAME_PLUS_SHOW_RECYC,TYPE OBJTYPE_PLUS_SHOW_RECYC,DROPTIME
　　3    DROPTIME_PLUS_SHOW_RECYC
　　4  FROM
　　5   USER_RECYCLEBIN ORDER BY ORIGINAL_NAME,DROPTIME DESC,
　　6    OBJECT_NAME
　　7  /
　　no rows selected
　　-- 根据上面获得的对象编号来查看对象名字
　　sys@MAA> col OBJECT_NAME for a30
　　sys@MAA> col OWNER for a30
　　sys@MAA> select OBJECT_NAME, OWNER from dba_objects where OBJECT_ID IN ('24248','24352','24247');
　　OBJECT_NAME                    OWNER
　　------------------------------ ------------------------------
　　BIN$048hXdZVf0HgQwEAAH/4UQ==$1 LUOCS
　　BIN$048hXdZUf0HgQwEAAH/4UQ==$1 LUOCS
　　TEST2                          LUOCS
　　oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html