[20130425]删除分区与recycle bin.txt

[20130425]删除分区与recycle bin.txt

http://mwidlake.wordpress.com/2012/01/24/dropped-partitions-do-not-go-in-the-recycle-bin/

昨天别人删除一个分区，想恢复里面的信息。我想删除就是一个段，使用flashback drop应该可以恢复。
对方讲不行，自己感觉奇怪，做一个测试看看。

1. 建立测试环境:

SQL> @ver

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t 

partition by range (id)

(partition p1 values less than (10)

,partition p2 values less than (20)

,partition p3 values less than (30)

,partition pm values less than (maxvalue)

) as select rownum id, cast ('test' as varchar2(10)) name from dual connect by level

SQL> show recyclebin

SQL> select * from user_recyclebin;

no rows selected

SQL> select table_name,composite,partition_name from dba_tab_partitions where table_owner=user and table_name='T';

TABLE_NAME COM PARTITION_NAME

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

T          NO  P1

T          NO  P2

T          NO  P3

T          NO  PM

SQL> select segment_name,partition_name,segment_type,blocks from dba_segments where wner=user and segment_name='T';

SEGMENT_NAME  PARTITION_NAME  SEGMENT_TYPE           BLOCKS

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

T             P1              TABLE PARTITION          1024

T             P2              TABLE PARTITION          1024

T             P3              TABLE PARTITION          1024

T             PM              TABLE PARTITION          1024

--可以发现4个段，另外注意占用块大小，1024块，占用8M(我写一个blog说明这种情况,链接如下)：
http://space.itpub.net/267265/viewspace-757670
http://space.itpub.net/267265/viewspace-757871

2.删除分区：

SQL> alter table t drop partition p3;

Table altered.

SQL> select segment_name,partition_name,segment_type,blocks from dba_segments where wner=user and segment_name='T';

SEGMENT_NAME  PARTITION_NAME  SEGMENT_TYPE           BLOCKS

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

T             P1              TABLE PARTITION          1024

T             P2              TABLE PARTITION          1024

T             PM              TABLE PARTITION          1024

SQL> show recyclebin

SQL> select * from user_recyclebin;

no rows selected

--确实这样，不能使用flashback drop恢复单独一个分区。

3.如果删除这个表呢？

SQL> drop table t;

Table dropped.

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

T                BIN$2yYsUQ4EewDgQyhkqMClqg==$0 TABLE        2013-04-25:09:16:50

SQL> select * from user_recyclebin;

OBJECT_NAME          ORIGINAL_N OPERATION TYPE       TS_NAME    CREATETIME          DROPTIME               DROPSCN PARTITION_ CAN CAN    RELATED BASE_OBJECT PURGE_OBJECT      SPACE

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

BIN$2yYsUQ4EewDgQyhk T          DROP      TABLE                 2013-04-25:08:54:20 2013-04-25:09:16:50 3228526967            YES YES     273931      273931       273931

qMClqg==$0

BIN$2yYsUQ4EewDgQyhk T          DROP      Table Part USERS      2013-04-25:08:54:20 2013-04-25:09:16:50 3228526967            NO  NO      273931      273931       273931       1024

qMClqg==$0                                ition

BIN$2yYsUQ4EewDgQyhk T          DROP      Table Part USERS      2013-04-25:08:54:20 2013-04-25:09:16:50 3228526967            NO  NO      273931      273931       273931       1024

qMClqg==$0                                ition

BIN$2yYsUQ4EewDgQyhk T          DROP      Table Part USERS      2013-04-25:08:54:20 2013-04-25:09:16:50 3228526967            NO  NO      273931      273931       273931       1024

qMClqg==$0                                ition

SQL> flashback table t to before drop ;

Flashback complete.

SQL> select segment_name,partition_name,segment_type,blocks from dba_segments where wner=user and segment_name='T';

SEGMENT_NAME         PARTITION_ SEGMENT_TYPE           BLOCKS

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

T                    P1         TABLE PARTITION          1024

T                    P2         TABLE PARTITION          1024

T                    PM         TABLE PARTITION          1024

SQL> select count(*) from t;

  COUNT(*)

----------

        30

--无法恢复删除的分区。看来11G的flashback drop还是存在一些缺陷。

4.删除整个表，恢复单个分区看看？

SQL> drop table t;

Table dropped.

SQL> select * from user_recyclebin;

OBJECT_NAME          ORIGINAL_N OPERATION TYPE       TS_NAME    CREATETIME          DROPTIME               DROPSCN PARTITION_ CAN CAN    RELATED BASE_OBJECT PURGE_OBJECT      SPACE

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

BIN$2yYsUQ4FewDgQyhk T          DROP      TABLE                 2013-04-25:08:54:20 2013-04-25:09:31:05 3228527472            YES YES     273931      273931       273931

qMClqg==$0

BIN$2yYsUQ4FewDgQyhk T          DROP      Table Part USERS      2013-04-25:08:54:20 2013-04-25:09:31:05 3228527472            NO  NO      273931      273931       273931       1024

qMClqg==$0                                ition

BIN$2yYsUQ4FewDgQyhk T          DROP      Table Part USERS      2013-04-25:08:54:20 2013-04-25:09:31:05 3228527472            NO  NO      273931      273931       273931       1024

qMClqg==$0                                ition

BIN$2yYsUQ4FewDgQyhk T          DROP      Table Part USERS      2013-04-25:08:54:20 2013-04-25:09:31:05 3228527472            NO  NO      273931      273931       273931       1024

qMClqg==$0                                ition

--仔细查看手册，无法单独flashback某个分区。

SQL> select * from "BIN$2yYsUQ4FewDgQyhkqMClqg==$0" partition (p1);

        ID NAME

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

         1 test

         2 test

         3 test

         4 test

         5 test

         6 test

         7 test

         8 test

         9 test

9 rows selected.

--select倒是可以。