请教大哥，急！

haowen

数据库非归档模式，drop掉的表如何恢复！！急！！！谢谢！！！

IT中不算精英的

使用dbms_flashback也许可以恢复过来，如果你的UNDO没有被覆盖的话。

IT中不算精英的

使用闪回查询：
1、        在SPFILE里必须设置下列参数：
UNDO_MANAGEMENT=AUTO（9i以后支持）
UNDO_RETENTION=86400 保留一天的UNDA DATA
UNDO表空间最好设置为RETENTION GUARANTEE：
                alter tablespace undotbs1 retention guarantee;
不允许未到期的UNDO DATA被覆盖
Undo Space = (UNDO_RETENTION * (Undo Blocks Per Second * DB_BLOCK_SIZE) ) +DB_BLOCK_SIZE

SELECT (RD * (UPS * OVERHEAD) + OVERHEAD) AS "Bytes“
FROM (SELECT value AS RD FROM v$parameter
WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks) / SUM( ((end_time - begin_time) * 86400)))
AS UPS FROM v$undostat),
(SELECT value AS Overhead FROM v$parameter
WHERE name = 'db_block_size');


2、        创建表空间
SQL> create tablespace mgw_tablespace
  2  datafile '/oracle/app/oracle/oradata/ora10/mgw_datafile_001.dbf' size 10M
  3  extent management local；
3、        创建用户：
SQL> create user mgw identified by mgw
  2  default tablespace mgw_tablespace
  3  temporary tablespace group001
  4  quota unlimited on mgw_tablespace;
4、        授权：
SQL>
SQL> grant connect,resource to mgw;
Grant succeeded.

SQL> grant execute on dbms_flashback to mgw;
Grant succeeded.
5、        创建表
SQL> connect mgw/mgw
Connected.
SQL> create table mgw_table
  2  (
  3  name char(10),
  4  sex char(10),
  5  age number(10)
  6  );
Table created.
6、        插入数据
SQL>
SQL> insert into mgw_table
  2  values('Maguowen','Male',30);
1 row created.
SQL> /
1 row created.

SQL> insert into mgw_table
  2  values('Tang','Female',23);
1 row created.
SQL> /
1 row created.
7、        设置显示
SQL> set echo on
SQL> set time on
03:46:10 SQL> select * from mgw_table;

NAME       SEX               AGE
---------- ---------- ----------
Maguowen   Male               30
Maguowen   Male               30
Tang       Female             23
Tang       Female             23
03:47:04 SQL>
03:47:08 SQL> commit;
8、        删除数据
03:49:03 SQL> delete from mgw_table where sex='Male';
2 rows deleted.
3:49:25 SQL> commit;
Commit complete.
03:50:07 SQL>
03:50:39 SQL> select * from mgw_table;
NAME       SEX               AGE
---------- ---------- ----------
Tang       Female             23
Tang       Female             23
03:51:09 SQL>
9、        闪回查询
04:14:46 SQL> execute dbms_flashback.enable_at_time('15-JAN-05 3:49:00 am');
PL/SQL procedure successfully completed.
04:15:34 SQL> select * from mgw_table;
NAME       SEX               AGE
---------- ---------- ----------
Maguowen   Male               30
Maguowen   Male               30
Tang       Female             23
Tang       Female             23
04:15:41 SQL>
04:15:46 SQL> execute dbms_flashback.disable;
PL/SQL procedure successfully completed.
04:17:47 SQL> select * from mgw_table;
NAME       SEX               AGE
---------- ---------- ----------
Tang       Female             23
Tang       Female             23
04:17:53 SQL>

10、        恢复
------查看当前的SCN号
VARIABLE SCN_SAVE NUMBER;
EXECUTE :SCN_SAVE := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
PRINT SCN_SAVE
------创建SCN_RECORD表，用来保存SCN号
CREATE TABLE SCN_RECOR
(
SCN_NO NUMBER,
TIME TIMESTAMP
);

------创建触发器，在对表进行任何添加、更改和删除前保存SCN号和日戳
CREATE OR REPLACE TRIGGER mgw_table_trigger
BEFORE INSERT OR UPDATE OR DELETE ON mgw_table
FOR EACH ROW
DECLARE SCN_SAVE NUMBER;
BEGIN
SCN_SAVE := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
Insert into scn_record values(SCN_SAVE,sysdate);
END;

------查看日戳和SCN号
SELECT * FROM SCN_RECORD;

------恢复SCN号之前的数据
DECLARE
CURSOR FLASH_RECOVER IS
select * from mgw_table;
recover_rec mgw_table%ROWTYPE;
begin
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(SCN号);
open FLASH_RECOVER;
DBMS_FLASHBACK.DISABLE;
loop
FETCH FLASH_RECOVER INTO recover_rec;
EXIT WHEN FLASH_RECOVER%NOTFOUND;
insert into mgw_table
values
(recover_rec.name,
recover_rec.sex,
recover_rec.age
);
end loop;
CLOSE FLASH_RECOVER;
commit;
end;