Oracle 10g flashback table 索引名带来的变化
今天在做测试时，无意中发现索引名出现异常：
Index altered.
在很多系统中，索引名字的命名都有其一套规则，闪回表格之后Oracle为什么不顺便把索引名也附带闪回呢？如果采用系统命名，在以后的管理上会带来诸多的不便。
继续研究：
首先将其余索引重命名
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
