对于误删除并且已经提交的数据,在UNDO表空间中仍保存着这些信息。可以通过oracle的闪回查询功能恢复到某一时间点,或某一SCN号。以下是一个小测试(恢复到某一时间点):
数据库测试版本:
SQL> select *from v$version where rownum <2;
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
(一) 对表进行了误操作
SQL> select * from test1;
A B D
---------- ---------- -
1 1 1
1 1 1
3 3 3
4 4 4
5 5 5
SQL> delete from test1;
5 rows deleted
SQL> commit;
Commit complete
SQL> select * from test1 as of timestamp to_timestamp('2009-05-21 10:10:10', 'yyyy-mm-dd hh24:mi:ss');
A B D
---------- ---------- -
1 1 1
1 1 1
3 3 3
4 4 4
5 5 5
A B D
---------- ---------- -
1 1 1
1 1 1
3 3 3
4 4 4
5 5 5
SQL> insert into test1 select * from test1 as of timestamp to_timestamp('2009-05-21 10:10:10', 'yyyy-mm-dd hh24:mi:ss');
5 rows inserted
5 rows inserted
SQL> select * from test1;
A B D
---------- ---------- -
1 1 1
1 1 1
3 3 3
4 4 4
5 5 5
SQL>
A B D
---------- ---------- -
1 1 1
1 1 1
3 3 3
4 4 4
5 5 5
SQL>
这样就进行了恢复表的数据。
(二) 存储过程进行了误操作
SQL> create package xxxxxx is
2 PROCEDURE prc_test;
3 end xxxxxx;
4 /
程序包已创建。
SQL> select object_id from dba_objects where object_name like 'XXX%';
OBJECT_ID
----------
30802
SQL> select *from source$ where obj#=30802;
OBJ# LINE SOURCE
---------- ---------- ---------------------------------------------------------------------------------------------------
30802 1 package xxxxxx is
30802 2 PROCEDURE prc_test;
30802 3 end xxxxxx;
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2007-12-03 09:21:38
SQL> drop package xxxxxx;
程序包已丢弃。
SQL> select *from source$ where obj#=30802;
未选定行
SQL> select *From source$ as of timestamp to_timestamp('2007-12-03 09:21:38', 'yyyy-mm-dd hh24:mi:ss') where obj#=30802;
OBJ# LINE SOURCE
---------- ---------- ---------------------------------------------------------------------------------------------------
30802 1 package xxxxxx is
30802 2 PROCEDURE prc_test;
30802 3 end xxxxxx;
2 PROCEDURE prc_test;
3 end xxxxxx;
4 /
程序包已创建。
SQL> select object_id from dba_objects where object_name like 'XXX%';
OBJECT_ID
----------
30802
SQL> select *from source$ where obj#=30802;
OBJ# LINE SOURCE
---------- ---------- ---------------------------------------------------------------------------------------------------
30802 1 package xxxxxx is
30802 2 PROCEDURE prc_test;
30802 3 end xxxxxx;
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2007-12-03 09:21:38
SQL> drop package xxxxxx;
程序包已丢弃。
SQL> select *from source$ where obj#=30802;
未选定行
SQL> select *From source$ as of timestamp to_timestamp('2007-12-03 09:21:38', 'yyyy-mm-dd hh24:mi:ss') where obj#=30802;
OBJ# LINE SOURCE
---------- ---------- ---------------------------------------------------------------------------------------------------
30802 1 package xxxxxx is
30802 2 PROCEDURE prc_test;
30802 3 end xxxxxx;
(三)另外,如果代码程度比较长,可以通过以下方式导成text文件。
SQL> set pagesize 0
SQL> column text format a4000
SQL> spool c:\1.text
Started spooling to c:\1.text
Started spooling to c:\1.text
SQL> select *From source$ as of timestamp to_timestamp('2007-12-03 09:21:38', 'yyyy-mm-dd hh24:mi:ss') where obj#=30802;
SQL> spool off
Stopped spooling to c:\1.text
Stopped spooling to c:\1.text
这样所有代码就保存成了文件,靠过来即可以进行恢复。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7204674/viewspace-600824/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7204674/viewspace-600824/