Oracle 10g flashback table 目录名带来的变化
Oracle 10g flashback table 索引名带来的变化
今天在做测试时，无意中发现索引名出现异常：
该索引名类似于表格被删除之后，在回收站中的名字，但是在回收站并没有该索引对象：
情况回收站，也不影响该索引
后来仔细回忆原来是zhoultest这张表格前几天在做测试时，曾经删除过，后来又将其闪回了。原来Oracle在做表格闪回时，默认将其索引也闪回，但是名字还保留回收站里的名字。
于是可以用以下语法将索引名改名：
在很多系统中，索引名字的命名都有其一套规则，闪回表格之后Oracle为什么不顺便把索引名也附带闪回呢？如果采用系统命名，在以后的管理上会带来诸多的不便。
继续研究：
首先将其余索引重命名
再次将表格删除
观察recyclebin$，我们可以得到很多信息：
1、从DROPSCN可以看出，Oracle删除表格时先进行索引删除操作。
2、索引在recyclebin$表格中的flag标记是18，table的flag标记是30。
通过查看sql.bsq(在$ORACLE_HOME/rdbms/admin下)可以查看该表格各字段的定义
可以看到recyclebin$有4条记录，但show recyclebin只有1条表格记录，可见show recyclebin进行了过滤。用10046跟踪show recyclebin过程
打开跟踪文件，可以看到在选择数据时对USER_RECYCLEBIN进行了CAN_UNDROP='YES'过滤。
如果不加过滤条件，默认的话，会将索引和表格全部展示：
再次将表格闪回，可以看到索引名是系统命名的，忍不住想问Oracle，既然都将索引闪回了，闪回时为什么不把索引名也重名命一下？
今天在做测试时，无意中发现索引名出现异常：
引用
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> 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
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.
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.
Index altered.
SQL> alter index "BIN$oeMDlvXFdhjgQBCsowQz5w==$0" rename to indx3;
Index altered.
再次将表格删除
引用
SQL> drop table zhoultest;
Table dropped.
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
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
)
(
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.
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
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
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
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
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