oracle10g里面有个recyclebin，删除时没加purge参数并非真正删除，而是先通过修改数据字典的方式，并将其改名放入recycle bin中。 从recycle bin 中恢复： 1.创建测试数据 sql conn scott/oracle connected. sql create table temp(id int primary key); tab
oracle10g里面有个recyclebin，删除时没加purge参数并非真正删除，而是先通过修改数据字典的方式，并将其改名放入recycle bin中。
从recycle bin 中恢复：
1.创建测试数据
sql> conn scott/oracle
connected.
sql> create table temp(id int primary key);
table created.
sql> insert into temp values(1);
1 row created.
sql> insert into temp values(2);
1 row created.
sql> insert into temp values(3);
1 row created.
sql> drop table temp;
table dropped.
2.查看recycle bin
sql> desc recyclebin;
name null? type
----------------------------------------- -------- ----------------------------
object_name not null varchar2(30)
original_name varchar2(32)
operation varchar2(9)
type varchar2(25)
ts_name varchar2(30)
createtime varchar2(19)
droptime varchar2(19)
dropscn number
partition_name varchar2(32)
can_undrop varchar2(3)
can_purge varchar2(3)
related not null number
base_object not null number
purge_object not null number
space number
sql> select original_name,object_name from recyclebin;
original_name object_name
-------------------------------- ------------------------------
temp bin$9fpguw34tfvgqab/aqab+w==$0 //temp表
sys_c006287 bin$9fpguw33tfvgqab/aqab+w==$0 //主键
sql> select * from tab;
tname tabtype clusterid
------------------------------ ------- ----------
dept table
emp table
bonus table
salgrade table
expfull table
flash_tbl table
test table
bin$9fpguw34tfvgqab/aqab+w==$0 table
3.恢复表
sql> flashback table temp to before drop;
flashback complete.
sql> select * from tab;
tname tabtype clusterid
------------------------------ ------- ----------
dept table
emp table
bonus table
salgrade table
expfull table
temp table
flash_tbl table
test table
sql> select * from temp;
id
----------
1
2
3
sql> desc user_indexes;
name null? type
----------------------------------------- -------- ----------------------------
index_name not null varchar2(30)
index_type varchar2(27)
table_owner not null varchar2(30)
table_name not null varchar2(30)
table_type varchar2(11)
uniqueness varchar2(9)
compression varchar2(8)
prefix_length number
tablespace_name varchar2(30)
ini_trans number
max_trans number
initial_extent number
next_extent number
min_extents number
max_extents number
pct_increase number
pct_threshold number
include_column number
freelists number
freelist_groups number
pct_free number
logging varchar2(3)
blevel number
leaf_blocks number
distinct_keys number
avg_leaf_blocks_per_key number
avg_data_blocks_per_key number
clustering_factor number
status varchar2(8)
num_rows number
sample_size number
last_analyzed date
degree varchar2(40)
instances varchar2(40)
partitioned varchar2(3)
temporary varchar2(1)
generated varchar2(1)
secondary varchar2(1)
buffer_pool varchar2(7)
user_stats varchar2(3)
duration varchar2(15)
pct_direct_access number
ityp_owner varchar2(30)
ityp_name varchar2(30)
parameters varchar2(1000)
global_stats varchar2(3)
domidx_status varchar2(12)
domidx_opstatus varchar2(6)
funcidx_status varchar2(8)
join_index varchar2(3)
iot_redundant_pkey_elim varchar2(3)
dropped varchar2(3)
sql> select table_name,index_name from user_indexes where table_name='temp';
table_name index_name
------------------------------ ------------------------------
temp bin$9fpguw33tfvgqab/aqab+w==$0
4.手动修改索引
sql> alter index bin$9fpguw33tfvgqab/aqab+w==$0 rename to sys_c006287;
index altered.
复杂点的表恢复：
sql> select * from tab;
tname tabtype clusterid
------------------------------ ------- ----------
dept table
emp table
bonus table
salgrade table
expfull table
temp table
flash_tbl table
test table
8 rows selected.
sql> drop table temp;
table dropped.
sql> create table temp as select * from flash_tbl;
table created.
sql> flashback table temp to before drop;
flashback table temp to before drop
*
error at line 1:
ora-38312: original name is used by an existing object
sql> flashback table temp to before drop rename to old_temp; 命名冲突，重命名……
flashback complete.
sql> select * from tab;
tname tabtype clusterid
------------------------------ ------- ----------
dept table
emp table
bonus table
salgrade table
expfull table
old_temp table
flash_tbl table
test table
temp table
9 rows selected.
sql> select * from old_temp;
id
----------
1
2
3
从多次删除中恢复：
sql> create table temp_old as select * from temp;
table created.
sql> drop table temp;
table dropped.
sql> alter table temp_old rename to temp;
table altered.
sql> drop table temp;
table dropped.
sql> create table temp (id int);
table created.
sql> drop table temp;
table dropped.
sql> select droptime,original_name,object_name from recyclebin;
droptime original_name
------------------- --------------------------------
object_name
------------------------------
2014-04-01:04:48:58 temp
bin$9fm3y2lf6ohgqab/aqaemq==$0
2014-04-01:04:49:26 temp
bin$9fm3y2lg6ohgqab/aqaemq==$0
2014-04-01:04:46:26 temp
bin$9fm3y2le6ohgqab/aqaemq==$0
sql> flashback table temp to before drop;
flashback complete.
sql> select droptime,original_name,object_name from recyclebin;
droptime original_name
------------------- --------------------------------
object_name
------------------------------
2014-04-01:04:48:58 temp
bin$9fm3y2lf6ohgqab/aqaemq==$0
2014-04-01:04:46:26 temp
bin$9fm3y2le6ohgqab/aqaemq==$0
从时间可以看出越后删除的越先被恢复，即倒着恢复。
sql> select droptime,original_name,object_name from recyclebin;
droptime original_name
------------------- --------------------------------
object_name
------------------------------
2014-04-01:05:04:31 temp3
bin$9fm3y2li6ohgqab/aqaemq==$0
2014-04-01:05:04:27 temp2
bin$9fm3y2lh6ohgqab/aqaemq==$0
当然可以直接指定对象名称来恢复：
sql> flashback table bin$9fm3y2li6ohgqab/aqaemq==$0 to before drop;
flashback complete.
sql> select * from tab;
tname tabtype clusterid
------------------------------ ------- ----------
dept table
emp table
bonus table
salgrade table
expfull table
bin$9fm3y2lh6ohgqab/aqaemq==$0 table
flash_tbl table
test table
temp3 table
temp table
10 rows selected.
从undo表空间恢复：
sql> select * from flash_tbl;
id v
---------- -
10 i
11 j
12 k
13 l
14 m
15 n
117 p
118 q
119 r
120 s
6 e
id v
---------- -
7 f
8 g
9 h
300 r
500 t
16 rows selected.
sql> select dbms_flashback.get_system_change_number from dual;
get_system_change_number
------------------------
1227868
sql> delete flash_tbl where id=7;
1 row deleted.
sql> insert into flash_tbl values(25,'r');
1 row created.
sql> commit;
commit complete.
sql> select * from flash_tbl;
id v
---------- -
10 i
11 j
12 k
13 l
14 m
15 n
117 p
118 q
119 r
120 s
25 r
id v
---------- -
6 e
8 g
9 h
300 r
500 t
16 rows selected.
基于scn的查询：
sql> select * from flash_tbl as of scn 1227868;
id v
---------- -
10 i
11 j
12 k
13 l
14 m
15 n
117 p
118 q
119 r
120 s
6 e
id v
---------- -
7 f
8 g
9 h
300 r
500 t
16 rows selected.
sql> flashback table flash_tbl to scn 1227868;
flashback table flash_tbl to scn 1227868
*
error at line 1:
ora-08189: cannot flashback the table because row movement is not enabled
sql> oerr ora 08189
sp2-0734: unknown command beginning oerr ora 0... - rest of line ignored.
sql> host oerr ora 08189
08189, 00000, cannot flashback the table because row movement is not enabled
// *cause: an attempt was made to perform flashback table operation on a table for
// which row movement has not been enabled. because the flashback table
// does not preserve the rowids, it is necessary that row
// movement be enabled on the table.
// *action: enable row movement on the table
未开启行移动
sql> alter table flash_tbl enable row movement;
table altered.
sql> flashback table flash_tbl to scn 1227868;
flashback complete.
sql> select * from flash_tbl;
id v
---------- -
10 i
11 j
12 k
13 l
14 m
15 n
117 p
118 q
119 r
120 s
6 e
id v
---------- -
7 f
8 g
9 h
300 r
500 t
16 rows selected.
恢复成功……
scn：在10g中，系统平均每3秒产生一次系统时间与scn的匹配并存入sys.smon_scn_time表，若使用as of timestamp 查询undo中的数据，实际获取的数据是以指定的时间对应的scn时的数据为基准。
例如：scn time
123 2013-04-01 20:25:00
125 2013-04-01 20:30:00
当通过as of timestamp查询2013-04-01 20:25:00或2013-04-01 20:29:59这段时间内任何时间，oracle都会将其匹配为scn：123到undo表空间中查找。就是说在这个时间段内，查询返回的数据都是2013-04-01 20:25:00这个时间对应的scn的数据。