1. as of timestamp로 테이블 데이터 복구하기
- > select * from 테이블명 as of timestamp to_timestamp (날짜,날짜형식) 또는 sysdate-5/1440
2. as of timestamp로 프로시져 복구하기
- > select * from dba_source as of timestamp to_timestamp (날짜,날짜형식) 또는 sysdate-5/1440 where name = '프로시져명'
Sample query)
SQL> select obj#,name from obj$ as of timestamp sysdate-5/1440
where name =’SP_TEST’;
SQL> select obj#,source from source$ as of timestamp sysdate-6/1440
where obj#=53258;
3. Recyclebin의 테이블 복구하기
Flashback Drop - Procedure
In Oracle 10g, when you drop a table, the table is not really dropped. Rather, the table is renamed. The data in the table is still available. You can use the Flashback Drop technologies to recover an accidentally dropped table. The procedure is as follows,
Step 1: Check whether the dropped table is still available in the recycle bin, command.
User level
Scott@prod> show recyclebin;
Database level
dba@prod> Select * from dba_recyclebin;
The original_name column shows the dropped table’s original name. It also has the recycle bin’s name and the dropped time.
Step 2: Use the Flashback Drop command to recover the table
Scott@prod> Flashback Table Employees to before drop [Rename to New_Employees];
Or you can use the system generated name as follows
Scott@prod> Flashback Table “BIN$YkyfmFKl8MLgQwppGAzwwg==$0" to before drop [Rename to New_Employees];
Step 3: To permanently drop the table.
Scott@prod> Drop Table Employees purge;
Scott@prod> purge dba_recyclebin;
Related Posts : Flashback Recovery Timestamp and SCN Conversion
Flashback Table - Privileges
Flashback Table - Procedure
Flashback Query
Important Points - Flashback Table