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