[20130425]删除分区与recycle bin.txt
http://mwidlake.wordpress.com/2012/01/24/dropped-partitions-do-not-go-in-the-recycle-bin/
昨天别人删除一个分区,想恢复里面的信息。我想删除就是一个段,使用flashback drop应该可以恢复。
对方讲不行,自己感觉奇怪,做一个测试看看。
1. 建立测试环境:
--可以发现4个段,另外注意占用块大小,1024块,占用8M(我写一个blog说明这种情况,链接如下):
http://space.itpub.net/267265/viewspace-757670
http://space.itpub.net/267265/viewspace-757871
2.删除分区:
--确实这样,不能使用flashback drop恢复单独一个分区。
3.如果删除这个表呢?
--无法恢复删除的分区。看来11G的flashback drop还是存在一些缺陷。
4.删除整个表,恢复单个分区看看?
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<=40 ;
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
--select倒是可以。
9 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-759222/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-759222/