之前数据恢复方法:
第一步:找到删除数据的操作时间从( v$sql 或者 v$sqlarea 视图里面查询 )
select r.FIRST_LOAD_TIME,r.SQL_TEXT,r.optimizer_mode,r.module,r.action,r.LAST_ACTIVE_TIME
from v$sqlarea r
order by r.FIRST_LOAD_TIME desc ;
第二步:
create table t_table_recove
as
select * from t_table
as of timestamp to_timestamp('2010-06-02 11:36:53','yyyy-mm-dd hh24:mi:ss');
to_timestamp('2010-06-02 11:36:53.000000','yyyy-mm-dd hh24:mi:ss.ff')
再将恢复后的数据放到原来表就可以了.
--------------------------------------------------------------------------------------------------
查询drop的表
select * from DBA_RECYCLEBIN;
Sql> purge dba_recyclebin; 用户进行Drop操作的对象并没有被数据库删除，仍然会占用空间。用户手工进行Purge才会释放空间
Alter system set recyclebin = off/on; --禁用系统回收站功能,建议启用;
删除表后,不放入回收站,语法为: drop table table_name purge ;
恢复drop后的表---只要知道删除数据的时间点就可以恢复到当时的状态
FLASHBACK TABLE MODIFY_TEST TO BEFORE DROP;
FLASHBACK TABLE REMP
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);
FLASHBACK TABLE REMP
TO TIMESTAMP (scn_to_timestamp(707075))--scn值
闪回表
使用闪回表，可将一个或多个表恢复到特定的时间点，
而不需要还原备份。
•从还原表空间检索数据以执行闪回表操作。
•可以授予FLASHBACK 对象权限和FLASHBACK ANY TABLE 系统权限，以允许表所有者以外的人员
闪回此表。
•必须对要执行闪回操作的表启用行移动。
1.对表启用行移动
必须先对表启用行移动，才能对表执行闪回操作。启用了行移动之后，Oracle 服务器便
可移动表中的行。
语法: ALTER TABLE flash_recove ENABLE ROW MOVEMENT;
-----------------------------------------------------------------------------------------------------------------------------------------------------
存储过程（procedure）,方法（Function） 恢复方法
以下是关于恢复意外删除存储过程以及function的方法
只要传入删除对象的时间（timestamp）
传入日期格式为'YYYY-MM-DD HH:MI:SS'和对象的名称即可恢复 。注意：使用一下方法进行恢复必须在数据库未重启前.
-----------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION RECOVE_PROCE(del_Time in varchar2,
proc_Name in varchar2)
RETURN VARCHAR2 IS
/**
*
* function Name :存储过程或function 删除后恢复方法
*
* del_time 对象被删除时间
*
* proc_Name :被删除对象名称
*
* return :返回重建对象的语句
*
*/
OBJ_NUM NUMBER;
str_Proc varchar2(2000);
str_End varchar2(2000) := '';
str_Sql varchar2(2000);
BEGIN
SELECT obj#
INTO OBJ_NUM
FROM obj$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
WHERE NAME = upper(proc_Name);
for i in (SELECT rowid rid, source
FROM source$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
where obj# = OBJ_NUM
order by line) loop
select source
into str_Proc
from source$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
where obj# = OBJ_NUM
AND ROWID = I.RID
order by line;
str_End := str_End || str_Proc;
end loop;
str_Sql := 'CREATE OR REPLACE ' || str_End;
return str_Sql;
exception
when others then
dbms_output.put_line(sqlcode || sqlerrm);
return null;
END RECOVE_PROCE;
------------------Procedure -----------------------------
CREATE OR REPLACE PROCEDURE ROLLBACK_PROC(del_Time in varchar2,
proc_Name IN VARCHAR2,
sql_Str OUT VARCHAR2) is
OBJ_NUM NUMBER;
str_Proc varchar2(2000);
str_End varchar2(2000):='';
BEGIN
SELECT obj#
INTO OBJ_NUM
FROM obj$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
WHERE NAME = upper(proc_Name);
for i in (SELECT rowid rid, source
FROM source$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
where obj# = OBJ_NUM
order by line) loop
select source
into str_Proc
from source$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
where obj# = OBJ_NUM
AND ROWID = I.RID
order by line;
str_End:=str_End||str_Proc ;
end loop;
sql_Str:='CREATE OR REPLACE '||str_End;
exception
when others then
dbms_output.put_line(sqlcode || sqlerrm);
END ROLLBACK_PROC;
declare
prc_str varchar2(2000);
begin
ROLLBACK_PROC('2011-8-19 08:33:42','getEname',prc_str);
dbms_output.put_line(prc_str);
end;
---PACKAGE---------------------------------------------------------------------------------------------
create or replace package dbms_recover is
/**
* del_time The Object deleted time
*
* proc_Name :The deleted Object Name
*
* return :Return The Rebuild Sentence
*/
function F_RECOVER_FUNC_PROC(del_Time in varchar2, proc_Name in varchar2)
RETURN VARCHAR2;
procedure P_RECOVER_PROC_FUNC(del_Time in varchar2,
proc_Name in varchar2,
sql_Str out varchar2);
end dbms_recover;
/
create or replace package body dbms_recover is
FUNCTION F_RECOVER_FUNC_PROC(del_Time in varchar2,
proc_Name IN VARCHAR2)
RETURN VARCHAR2 IS
OBJ_NUM NUMBER;
str_Proc varchar2(2000);
str_End varchar2(2000) := '';
str_Sql varchar2(2000);
BEGIN
SELECT obj#
INTO OBJ_NUM
FROM obj$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
WHERE NAME = upper(proc_Name);
for i in (SELECT rowid rid, source
FROM source$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
where obj# = OBJ_NUM
order by line) loop
select source
into str_Proc
from source$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
where obj# = OBJ_NUM
AND ROWID = I.RID
order by line;
str_End := str_End || str_Proc;
end loop;
str_Sql := 'CREATE OR REPLACE ' || str_End;
return str_Sql;
exception
when others then
dbms_output.put_line(sqlcode || sqlerrm);
return null;
END F_RECOVER_FUNC_PROC;
------------------------
PROCEDURE P_RECOVER_PROC_FUNC(del_Time in varchar2,
proc_Name IN VARCHAR2,
sql_Str OUT VARCHAR2) is
OBJ_NUM NUMBER;
str_Proc varchar2(2000);
str_End varchar2(2000):='';
BEGIN
SELECT obj#
INTO OBJ_NUM
FROM obj$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
WHERE NAME = upper(proc_Name);
for i in (SELECT rowid rid, source
FROM source$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
where obj# = OBJ_NUM
order by line) loop
select source
into str_Proc
from source$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
where obj# = OBJ_NUM
AND ROWID = I.RID
order by line;
str_End:=str_End||str_Proc ;
end loop;
sql_Str:='CREATE OR REPLACE '||str_End;
exception
when others then
dbms_output.put_line(sqlcode || sqlerrm);
END P_RECOVER_PROC_FUNC;
end dbms_recover;
/
扫码加好友，拉您进群
全部版块
我的主页
收藏
