Oracle 10g flashback table 索引名带来的变化

今天在做测试时，无意中发现索引名出现异常：

引用

SQL> set autot traceonly exp stat
SQL> select * from zhoultest where obj#=1023;

56 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2999462806

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

| Id  | Operation                   | Name                           | Rows  | B
ytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                                |    54 |
4266 |    55   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| ZHOULTEST                      |    54 |
4266 |    55   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | BIN$oeMDlvXEdhjgQBCsowQz5w==$0 |    54 |
     |     3   (0)| 00:00:01 |

该索引名类似于表格被删除之后，在回收站中的名字，但是在回收站并没有该索引对象：

引用

SQL> show recyclebin

情况回收站，也不影响该索引

引用

SQL> purge recyclebin;

Recyclebin purged.

SQL> select INDEX_NAME,INDEX_TYPE from user_indexes where table_name='ZHOULTEST';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
BIN$oeMDlvXDdhjgQBCsowQz5w==$0 NORMAL
BIN$oeMDlvXEdhjgQBCsowQz5w==$0 NORMAL
BIN$oeMDlvXFdhjgQBCsowQz5w==$0 NORMAL

后来仔细回忆原来是zhoultest这张表格前几天在做测试时，曾经删除过，后来又将其闪回了。原来Oracle在做表格闪回时，默认将其索引也闪回，但是名字还保留回收站里的名字。
于是可以用以下语法将索引名改名：

引用

SQL> alter index "BIN$oeMDlvXDdhjgQBCsowQz5w==$0" rename to indx1;

Index altered.

在很多系统中，索引名字的命名都有其一套规则，闪回表格之后Oracle为什么不顺便把索引名也附带闪回呢？如果采用系统命名，在以后的管理上会带来诸多的不便。
继续研究：
首先将其余索引重命名

引用

SQL> alter index "BIN$oeMDlvXEdhjgQBCsowQz5w==$0" rename to indx2;

Index altered.

SQL> alter index "BIN$oeMDlvXFdhjgQBCsowQz5w==$0" rename to indx3;

Index altered.

再次将表格删除

引用

SQL> drop table zhoultest;

Table dropped.

观察recyclebin$，我们可以得到很多信息：

引用

SQL> col ORIGINAL_NAME for a30
SQL> col dropscn for 999999999999999
SQL> select OBJ#,OWNER#,ORIGINAL_NAME,DROPSCN,FLAGS from recyclebin$;

      OBJ#     OWNER# ORIGINAL_NAME                           DROPSCN      FLAGS
---------- ---------- ------------------------------ ---------------- ----------
     72817         60 INDX1                            10995361920804         18
     73071         60 INDX2                            10995361920806         18
    246364         60 INDX3                            10995361920808         18
     72659         60 ZHOULTEST                        10995361920813         30

1、从DROPSCN可以看出，Oracle删除表格时先进行索引删除操作。
2、索引在recyclebin$表格中的flag标记是18，table的flag标记是30。
通过查看sql.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
)

可以看到recyclebin$有4条记录，但show recyclebin只有1条表格记录，可见show recyclebin进行了过滤。用10046跟踪show recyclebin过程

引用

SQL> conn zhoul/zhoul
Connected.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Session altered.

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
ZHOULTEST        BIN$ogYyR9j5/3/gQBCsowRnLQ==$0 TABLE        2011-04-29:10:11:48
SQL>  ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

打开跟踪文件，可以看到在选择数据时对USER_RECYCLEBIN进行了CAN_UNDROP='YES'过滤。

引用

PARSING IN CURSOR #2 len=240 dep=0 uid=60 oct=3 lid=60 tim=1273480412774274 hv=2786599706 ad='25dfe610'
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
END OF STMT

引用

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;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
ZHOULTEST        BIN$ogYyR9j5/3/gQBCsowRnLQ==$0 TABLE        2011-04-29:10:11:48

如果不加过滤条件，默认的话，会将索引和表格全部展示：

引用

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 ;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
INDX1            BIN$ogYyR9j2/3/gQBCsowRnLQ==$0 INDEX        2011-04-29:10:11:48
INDX2            BIN$ogYyR9j3/3/gQBCsowRnLQ==$0 INDEX        2011-04-29:10:11:48
INDX3            BIN$ogYyR9j4/3/gQBCsowRnLQ==$0 INDEX        2011-04-29:10:11:48
ZHOULTEST        BIN$ogYyR9j5/3/gQBCsowRnLQ==$0 TABLE        2011-04-29:10:11:48

再次将表格闪回，可以看到索引名是系统命名的，忍不住想问Oracle，既然都将索引闪回了，闪回时为什么不把索引名也重名命一下？

引用

SQL> flashback table zhoultest to before drop;

Flashback complete.

SQL> select INDEX_NAME,INDEX_TYPE from user_indexes where table_name='ZHOULTEST';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
BIN$ogYyR9j2/3/gQBCsowRnLQ==$0 NORMAL
BIN$ogYyR9j3/3/gQBCsowRnLQ==$0 NORMAL
BIN$ogYyR9j4/3/gQBCsowRnLQ==$0 NORMAL