今天在跟朋友讨论了如下话题：


《有一张表上建立索引，DROP该表，又闪回删除回来，请问，这张表上的索引还能用吗？如果有变化那是什么？》


我进行了如下测试：


sys@MAA> select * from v$version;


BANNER

----------------------------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

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 id=1;


Execution Plan

----------------------------------------------------------

Plan hash value: 3565898414


----------------------------------------------------------------------------------

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   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 id):

---------------------------------------------------


2 - access("ID"=1)


Statistics

----------------------------------------------------------

         1  recursive calls

         0  db block gets

         2  consistent gets

         0  physical reads

         0  redo size

       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 id=1;


Execution Plan

----------------------------------------------------------

Plan hash value: 3980542661


----------------------------------------------------------------------------------------------------

| Id  | Operation         | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   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 id):

---------------------------------------------------


2 - access("ID"=1)


-- 我们可以RENAME索引名字以恢复

luocs@MAA> alter index "BIN$048hXdZSf0HgQwEAAH/4UQ==$0" rename to inx_test2_zh;


Index altered.


luocs@MAA> alter index "BIN$048hXdZRf0HgQwEAAH/4UQ==$0" rename to inx_test2;


Index altered.


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> alter session set events '10046 trace name context forever, level 8';


Session altered.


luocs@MAA> show recyclebin


luocs@MAA> alter session set events '10046 trace name context off';


Session altered.


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