和普通表包含单个数据段不同，如果表包含多个段，那么在进行FLASHBACK TABLE TO BEFORE DROP操作后，会导致一些段无法恢复正常的名称。

首先看看包含LOB的情况：

SQL> CREATE TABLE T_LOB
2 (ID NUMBER,
3 CONTENTS CLOB)
4 LOB(CONTENTS) STORE AS THECLOB;

表已创建。

SQL> INSERT INTO T_LOB
2 VALUES (1, LPAD('A', 4000, 'A'));

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT TABLE_NAME, COLUMN_NAME, SEGMENT_NAME
2 FROM USER_LOBS
3 WHERE TABLE_NAME = 'T_LOB';

TABLE_NAME COLUMN_NAME SEGMENT_NAME
------------------------------ ------------------------------ ------------------------------
T_LOB CONTENTS THECLOB

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T_LOB TABLE

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;

SEGMENT_NAME SEGMENT_TYPE
--------------------------------------------------- ------------------
T_LOB TABLE
SYS_IL0000088439C00002$$ LOBINDEX
THECLOB LOBSEGMENT

SQL> DROP TABLE T_LOB;

表已删除。

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$ggie/7lBpHLgQAB/AQBxdQ==$0 TABLE

SQL> SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
2 FROM USER_RECYCLEBIN;

OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ -------------------------------- -------------------------
BIN$ggie/7lBpHLgQAB/AQBxdQ==$0 T_LOB TABLE
BIN$ggie/7lApHLgQAB/AQBxdQ==$0 THECLOB LOB
SYS_IL0000088439C00002$$ SYS_IL0000088439C00002$$ LOB INDEX

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;

SEGMENT_NAME SEGMENT_TYPE
--------------------------------------------------- ------------------
BIN$ggie/7lBpHLgQAB/AQBxdQ==$0 TABLE
SYS_IL0000088439C00002$$ LOBINDEX
BIN$ggie/7lApHLgQAB/AQBxdQ==$0 LOBSEGMENT

SQL> FLASHBACK TABLE T_LOB TO BEFORE DROP;

闪回完成。

SQL> SELECT TABLE_NAME, COLUMN_NAME, SEGMENT_NAME
2 FROM USER_LOBS
3 WHERE TABLE_NAME = 'T_LOB';

TABLE_NAME COLUMN_NAME SEGMENT_NAME
------------------------------ ------------------------------ ------------------------------
T_LOB CONTENTS BIN$ggie/7lApHLgQAB/AQBxdQ==$0

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;

SEGMENT_NAME SEGMENT_TYPE
--------------------------------------------------- ------------------
T_LOB TABLE
SYS_IL0000088439C00002$$ LOBINDEX
BIN$ggie/7lApHLgQAB/AQBxdQ==$0 LOBSEGMENT

下面再看看包含嵌套表的情况：

SQL> DROP TABLE T_LOB PURGE;

表已删除。

SQL> CREATE OR REPLACE TYPE T_ADDRESS IS TABLE OF VARCHAR2(100);
2 /

类型已创建。

SQL> CREATE TABLE T_NESTED_TABLE
2 (ID NUMBER,
3 ADDRESS T_ADDRESS)
4 NESTED TABLE ADDRESS STORE AS ADDRESS_TAB;

表已创建。

SQL> COL SEGMENT_NAME FORMAT A30
SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ADDRESS_TAB TABLE
T_NESTED_TABLE TABLE

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;

SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------
ADDRESS_TAB NESTED TABLE
T_NESTED_TABLE TABLE
SYS_C0011204 INDEX
SYS_FK0000088442N00002$ INDEX

SQL> DROP TABLE T_NESTED_TABLE;

表已删除。

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$ggie/7lFpHLgQAB/AQBxdQ==$0 TABLE
BIN$ggie/7lGpHLgQAB/AQBxdQ==$0 TABLE

SQL> SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
2 FROM USER_RECYCLEBIN;

OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ -------------------------------- -------------------------
BIN$ggie/7lGpHLgQAB/AQBxdQ==$0 T_NESTED_TABLE TABLE
BIN$ggie/7lFpHLgQAB/AQBxdQ==$0 ADDRESS_TAB NESTED TABLE
BIN$ggie/7lEpHLgQAB/AQBxdQ==$0 SYS_FK0000088442N00002$ INDEX
BIN$ggie/7lDpHLgQAB/AQBxdQ==$0 SYS_C0011204 INDEX

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;

SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------
BIN$ggie/7lFpHLgQAB/AQBxdQ==$0 NESTED TABLE
BIN$ggie/7lGpHLgQAB/AQBxdQ==$0 TABLE
BIN$ggie/7lDpHLgQAB/AQBxdQ==$0 INDEX
BIN$ggie/7lEpHLgQAB/AQBxdQ==$0 INDEX

SQL> FLASHBACK TABLE T_NESTED_TABLE TO BEFORE DROP;

闪回完成。

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$ggie/7lFpHLgQAB/AQBxdQ==$0 TABLE
T_NESTED_TABLE TABLE

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;

SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------
BIN$ggie/7lFpHLgQAB/AQBxdQ==$0 NESTED TABLE
T_NESTED_TABLE TABLE
BIN$ggie/7lDpHLgQAB/AQBxdQ==$0 INDEX
BIN$ggie/7lEpHLgQAB/AQBxdQ==$0 INDEX

这次不仅嵌套表的名称没有恢复，包括嵌套表的两个索引在内的三个对象的名称都没有恢复成删除之前的名称。

类型情况也发生在索引组织表上：

SQL> DROP TABLE T_NESTED_TABLE PURGE;

表已删除。

SQL> CREATE TABLE T_INDEX_ORG
2 (ID NUMBER PRIMARY KEY,
3 NAME VARCHAR2(30),
4 OTHERS VARCHAR2(30))
5 ORGANIZATION INDEX
6 INCLUDING NAME
7 OVERFLOW;

表已创建。

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SYS_IOT_OVER_88446 TABLE
T_INDEX_ORG TABLE

SQL> SELECT TABLE_NAME, IOT_NAME
2 FROM USER_TABLES;

TABLE_NAME IOT_NAME
------------------------------ ------------------------------------------------------------
T_INDEX_ORG
SYS_IOT_OVER_88446 T_INDEX_ORG

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;

SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------
SYS_IOT_OVER_88446 TABLE
SYS_IOT_TOP_88446 INDEX

SQL> DROP TABLE T_INDEX_ORG;

表已删除。

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$ggie/7lJpHLgQAB/AQBxdQ==$0 TABLE
SYS_IOT_OVER_88446 TABLE

SQL> SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
2 FROM USER_RECYCLEBIN;

OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ -------------------------------- -------------------------
BIN$ggie/7lJpHLgQAB/AQBxdQ==$0 T_INDEX_ORG TABLE
SYS_IOT_OVER_88446 SYS_IOT_OVER_88446 IOT OVERFLOW SEGMENT
BIN$ggie/7lIpHLgQAB/AQBxdQ==$0 SYS_IOT_TOP_88446 IOT TOP INDEX

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;

SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------
SYS_IOT_OVER_88446 TABLE
BIN$ggie/7lIpHLgQAB/AQBxdQ==$0 INDEX

SQL> FLASHBACK TABLE T_INDEX_ORG TO BEFORE DROP;

闪回完成。

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SYS_IOT_OVER_88446 TABLE
T_INDEX_ORG TABLE

SQL> SELECT TABLE_NAME, IOT_NAME
2 FROM USER_TABLES;

TABLE_NAME IOT_NAME
------------------------------ ------------------------------------------------------------
T_INDEX_ORG
SYS_IOT_OVER_88446 T_INDEX_ORG

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;

SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------
SYS_IOT_OVER_88446 TABLE
BIN$ggie/7lIpHLgQAB/AQBxdQ==$0 INDEX

可以看到，索引段的名称没有恢复。

最后看看分区表的情况：

SQL> DROP TABLE T_INDEX_ORG PURGE;

表已删除。

SQL> CREATE TABLE T_PART
2 (ID NUMBER,
3 CREATE_DATE DATE)
4 PARTITION BY RANGE (CREATE_DATE)
5 SUBPARTITION BY HASH(ID)
6 SUBPARTITIONS 2
7 (PARTITION P1 VALUES LESS THAN (TO_DATE('2010-1', 'YYYY-MM')),
8 PARTITION P2 VALUES LESS THAN(TO_DATE('2011-1', 'YYYY-MM')));

表已创建。

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T_PART TABLE

SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME
2 FROM USER_TAB_SUBPARTITIONS;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------
T_PART P1 SYS_SUBP108
T_PART P1 SYS_SUBP109
T_PART P2 SYS_SUBP110
T_PART P2 SYS_SUBP111

SQL> SELECT SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
T_PART SYS_SUBP108 TABLE SUBPARTITION
T_PART SYS_SUBP109 TABLE SUBPARTITION
T_PART SYS_SUBP110 TABLE SUBPARTITION
T_PART SYS_SUBP111 TABLE SUBPARTITION

SQL> DROP TABLE T_PART;

表已删除。

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 TABLE

SQL> SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
2 FROM USER_RECYCLEBIN;

OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ -------------------------------- -------------------------
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 T_PART TABLE
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 T_PART Table Composite Partition
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 T_PART Table Composite Partition
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 T_PART Table Partition
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 T_PART Table Composite Partition
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 T_PART Table Composite Partition
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 T_PART Table Partition

已选择7行。

SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME
2 FROM USER_TAB_SUBPARTITIONS;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 P1 SYS_SUBP108
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 P1 SYS_SUBP109
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 P2 SYS_SUBP110
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 P2 SYS_SUBP111

SQL> SELECT SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 SYS_SUBP108 TABLE SUBPARTITION
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 SYS_SUBP109 TABLE SUBPARTITION
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 SYS_SUBP110 TABLE SUBPARTITION
BIN$ggie/7lKpHLgQAB/AQBxdQ==$0 SYS_SUBP111 TABLE SUBPARTITION

SQL> FLASHBACK TABLE T_PART TO BEFORE DROP;

闪回完成。

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T_PART TABLE

SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME
2 FROM USER_TAB_SUBPARTITIONS;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------
T_PART P1 SYS_SUBP108
T_PART P1 SYS_SUBP109
T_PART P2 SYS_SUBP110
T_PART P2 SYS_SUBP111

SQL> SELECT SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
T_PART SYS_SUBP108 TABLE SUBPARTITION
T_PART SYS_SUBP109 TABLE SUBPARTITION
T_PART SYS_SUBP110 TABLE SUBPARTITION
T_PART SYS_SUBP111 TABLE SUBPARTITION

分区表的处理不存在问题，这并不是说Oracle在处理分区表的时候注意到多段的情况，而是分区表被删除时，只修改了表名对应的信息，而并没有涉及到分区或子分区信息的修改，因此还原的时候也就不会出现关联段名称没有恢复的情况了。

总的来说，Oracle在处理多段对象的FLASHBACK时，还存在一些小的瑕疵。