闪回表

flashback table 时：

Notes on Flashing Back Dropped Tables The following notes apply to flashing back dropped tables:

对于删除的表闪回时 注意

 1 Oracle Database retrieves all indexes defined on the table retrieved from the recycle bin except for bitmap join indexes. (Bitmap join indexes are not put in the recycle bin during a DROP TABLE operation, so cannot be retrieved.)

恢复在回收站中能找到的相关索引，除了位图连接索引（因为位图连接索引不放在回收站中）

2 The database also retrieves all triggers and constraints defined on the table except for referential integrity constraints that reference other tables.

也恢复触发器和约束（不包括参照别的表的完整性约束）

The retrieved indexes, triggers, and constraints have recycle bin names. Therefore it is advisable to query the USER_RECYCLEBIN view before issuing a FLASHBACK TABLE ... TO BEFORE DROP statement so that you can rename the retrieved triggers and constraints to more usable names.

恢复的索引、触发器和约束还是回收站中的名字。可以重命名回来

3 When you drop a table, all materialized view logs defined on the table are also dropped but are not placed in the recycle bin. Therefore, the materialized view logs cannot be flashed back along with the table.

删除表的时候 ，物化视图的日志也删除掉了，但是不会放在回收站中，所以不能恢复

4 When you drop a table, any indexes on the table are dropped and put into the recycle bin along with the table. If subsequent space pressures arise, then the database reclaims space from the recycle bin by first purging indexes. In this case, when you flash back the table, you may not get back all of the indexes that were defined on the table.

表空间不足时，需清空回收站，先清空index

5 You cannot flash back a table if it has been purged, either by a user or by Oracle Database as a result of some space reclamation operation.

清除的就不能恢复了

关于index的例子

create table t_test as select * from dba_objects;

create index idx_t_test on t_test (object_name);

SQL> set autot trace

SQL> 

SQL> select * from t_test where object_name='T_TABLE';

Execution Plan

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

Plan hash value: 3273242040

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

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time |

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

|   0 | SELECT STATEMENT            |            |     1 |   207 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST     |     1 |   207 |     4   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_TEST |     1 |       |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_NAME"='T_TABLE')

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

         27  recursive calls

          0  db block gets

         92  consistent gets

        153  physical reads

          0  redo size

       1615  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> drop table t_test;

Table dropped.

SQL> set autot off

SQL> select object_name,original_name from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME

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

BIN$5Xoc34wqImHgQwEAAH9e3Q==$0 IDX_T_TEST

BIN$5Xoc34wrImHgQwEAAH9e3Q==$0 T_TEST

SQL> flashback table t_test to before drop;

Flashback complete.

SQL> set autot trace

SQL> select * from t_test where object_name='T_TABLE';

Execution Plan

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

Plan hash value: 1003436322

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

| Id  | Operation                   | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                                |     1 |   207 |   4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST                         |     1 |   207 |   4   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | BIN$5Xoc34wqImHgQwEAAH9e3Q==$0 |     1 |       |   3   (0)| 00:00:01 |     --是回收站中的名字

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_NAME"='T_TABLE')

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

         34  recursive calls

          0  db block gets

         87  consistent gets

        157  physical reads

          0  redo size

       1615  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> set autot off

SQL> select object_name,original_name from user_recyclebin;

no rows selected

SQL> alter index  "BIN$5Xoc34wqImHgQwEAAH9e3Q==$0"   rename to IDX_T_TEST;

Index altered.

by song

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25099483/viewspace-772113/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25099483/viewspace-772113/