[Sql] 지운데이터 복구

10분전 삭제한 데이터 복구

SELECT * FROM TB AS OF TIMESTAMP (SYSTIMESTAMP-INTERVAL ‘10’MINUTE) WHERE NAME =’홍길동’

 

================================================================================

누가.. 세 테이블의 데이터를 지웠단다..
그리고, 복구가 시급하단다. ㅋ

select *
   from  user_tables
where table_name in ('EXPERTREVIEW' , 'EXPERTREVIEWMAP', 'EXPERTREVIEWPAGE');

엇.. 테이블은 있다....

이 테이블을 드랍한건지 delete한건지 몰라
후다닥 이 쿼리를 날렸다.

select *
   from  recyclebin
where original_name in ('EXPERTREVIEW' , 'EXPERTREVIEWMAP', 'EXPERTREVIEWPAGE');

어... 결과값이 없다. 흠 delete나 truncate 한것이겠군..
담당자에게 물으니 18시10분쯤 지운거 같다고 했다.

18시 데이터 기준으로 테이블을 찍어냈다.

create table expertreview_1801
nologging
as
select * from EXPERTREVIEW as of timestamp to_timestamp('20090616180100','yyyymmddhh24miss');

이 테이블 확인해보라고 했더니.. 이걸로 복구하면 된단다.

rename expertreview to expertreview_bak;

rename expertreview_1801 to expertreview;

alter table expertreview_bak drop constraints EXPERTREVIEW_PK

drop index EXPERTREVIEW_PK;

alter table expertreview add constraint expertreview_pk primary key (expertid)
using index tablespace expertidx1;

스크립트 후다닥 만들어서 대충 돌렸다.
백업본이 있으니 변경사항은 _bak 테이블을 활용하면 좋겠다.

 

 

==================================================================

Using Flashback Query (SELECT ... AS OF)

You perform a Flashback Query by using a SELECT statement with an AS OF clause. You use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time by means of a timestamp or SCN. It returns committed data that was current at that point in time.

Potential uses of Flashback Query include:

• Recovering lost data or undoing incorrect, committed changes. For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.

• Comparing current data with the corresponding data at some time in the past. For example, you might run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.

• Checking the state of transactional data at a particular time. For example, you could verify the account balance of a certain day.

• Simplifying application design, by removing the need to store some kinds of temporal data. By using a Flashback Query, you can retrieve past data directly from the database.

• Applying packaged applications such as report generation tools to past data.

• Providing self-service error correction for an application, thereby enabling users to undo and correct their errors.

  See Also:

  Oracle Database SQL Reference for details on the syntax of the SELECT... AS OF statement

Examining Past Data: Example

This example uses a Flashback Query to examine the state of a table at a previous time. Suppose that a DBA discovers at 12:30 PM that the row for employee Chung had been deleted from the employees table. The DBA also knows that at 9:30AM the data for Chung was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30 to find out what data had been lost. If appropriate, the DBA can then re-insert the lost data.

Example 10-1 retrieves the state of the record for Chung at 9:30AM, April 4, 2004:

Example 10-1 Retrieving a Row with Flashback Query

SELECT * FROM employees AS OF TIMESTAMP 
   TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE last_name = 'Chung';

The update in Example 10-2 restores Chung's information to the employees table:

Example 10-2 Reinserting a Row After a Flashback Query

INSERT INTO employees 
    (SELECT * FROM employees AS OF TIMESTAMP 
     TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
     WHERE last_name = 'Chung');

Tips for Using Flashback Query

Keep the following in mind when using a Flashback Query (SELECT ... AS OF):

• You can specify or omit the AS OF clause for each table and specify different times for different tables. Use an AS OF clause in a query to perform DDL operations (such as creating and truncating tables) or DML operations (such as inserting and deleting) in the same session as the query.

• To use the results of a Flashback Query in a DDL or DML statement that affects the current state of the database, use an AS OF clause inside an INSERT or CREATE TABLE AS SELECT statement.

• When choosing whether to use a timestamp or an SCN in Flashback Query, remember that Oracle Database uses SCNs internally and maps these to timestamps at a granularity of 3 seconds. If a possible 3-second error (maximum) is important to a Flashback Query in your application, then use an SCN instead of a timestamp. Refer to "Flashback Tips – General".

• You can create a view that refers to past data by using the AS OF clause in the SELECT statement that defines the view. If you specify a relative time by subtracting from the current time on the database host, then the past time is recalculated for each query. For example:

  CREATE VIEW hour_ago AS
    SELECT * FROM employees AS OF
      TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
  -- SYSTIMESTAMP refers to the time zone of the database host environment
  
  

• You can use the AS OF clause in self-joins, or in set operations such as INTERSECT and MINUS, to extract or compare data from two different times. You can store the results by preceding a Flashback Query with a CREATE TABLE AS SELECT or INSERT INTO TABLE SELECT statement. For example, the following query reinserts into table employees the rows that existed an hour ago:

  INSERT INTO employees 
    (SELECT * FROM employees AS OF 
       TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE))
  -- SYSTIMESTAMP refers to the time zone of the database host environment
    MINUS SELECT * FROM employees);
  

[출처 : http://blog.naver.com/whitefre?Redirect=Log&logNo=140088464047 ]