oracle with the delete command to delete data recovery

2011-07-05  来源:本站原创  分类:Database  人气:100 

Just do not accidentally delete table data, better to find this.

http://hi.baidu.com/kinghmx/blog/item/41ea3551dd6cec521038c2a0.html

SCN (system change number), its English spelling is: System Change Number, it is very important in a database data structure.
SCN provides a mechanism for Oracle's internal clock can be seen as a logical clock, which is essential for recovery operations Notes: Oracle based only on SCN for recovery.
It defines the exact moment in a database commit. Submission of things, it is given a unique label things SCN. Some people think that SCN is, System Commit Number, usually submitted when changes in the SCN, so many cases,
These two terms are often used interchangeably.
What is the word which is actually not the most important for us, it is important that we know within the SCN clock mechanism of Oracle, Oracle database through the SCN to maintain consistency through the SCN is crucial for implementing the Oracle recovery mechanism.
Specific implementation process we can figure from the following example to appreciate;
1 of the original table record $ sqlplus eygle / eygle
SQL * Plus: Release 10.1.0.2.0 - Production on Wed Mar 30 08:52:04 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select count (*) from t1;
COUNT (*)
----------
9318
(2) accidentally deleted all records and submit changes.
SQL> delete from t1;
9318 rows deleted.
SQL> commit;
Commit complete.
SQL> select count (*) from t1;
COUNT (*)
----------
0
3 to obtain the current SCN
If you can know exactly what to delete before the SCN is best, if you do not know, you can try to flash back to the query.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
10,671,006
SQL> select count (*) from t1 as of scn 10671000;
COUNT (*)
----------
0
SQL> select count (*) from t1 as of scn 10670000;
COUNT (*)
----------
9318
We see in the SCN = 10670000, the data are.
4. To restore the data.
SQL> insert into t1 select * from t1 as of scn 10670000;
9318 rows created.
SQL> commit;
Commit complete.
SQL> select count (*) from t1;
COUNT (*)
----------
9318
Article 2
Restore accidentally deleted data select timestamp_to_scn (to_timestamp ('2009-03-13 09:00:00 ',' YYYY-MM-DD HH: MI: SS ')) from dual;
Results: 13,526,973
Convert scn time will be deleted
select * from reportinfo
AS OF SCN 13526973
Will reportinfo table scn data point based on this data can then be removed to restore operations create table reporttest as select * from reportinfo where 1 = 0;
insert into reporttest select * from reportinfo AS OF SCN 13526973;
- The above two should be able to synthesis a - create table reporttest as select * from reportinfo AS OF SCN 13526973;
This is reporttest table is scn point reportinfo data processing can be

相关文章
  • oracle with the delete command to delete data recovery 2011-07-05

    Just do not accidentally delete table data, better to find this. http://hi.baidu.com/kinghmx/blog/item/41ea3551dd6cec521038c2a0.html SCN (system change number), its English spelling is: System Change Number, it is very important in a database data st

  • oracle database, a simple way to delete duplicate data 2011-04-27

    Delete duplicate records: delete from tbl where rowid not in (select min (rowid) from tbl t group by t.col1, t.col2); When the data is more time, it is recommended for col1, col2 index, as follows: ALTER IGNORE TABLE tbl ADD UNIQUE INDEX (col1, col2)

  • The Oracle data recovery delete or drop table 2011-09-28

    In oralce, we sometimes mistakenly deleted some data or delete some of the table; then we need to restore the data. First, delete data recovery delete some data, you can use the following statement to find, this is mainly using Oracle's snapshot feat

  • oralce delete table data recovery operations 2011-07-11

    Normally we operate the database when the database may be some wrong operation is not careful to do common data in the table to be deleted or directly to the table to drop the. In fact, these operations, we are for data recovery, here is what I have

  • Add a foreign key when Oracle on delete cascade on delete set null function and 2010-05-05

    ------------------------------ ------------------ Extract ----------------------- Create a foreign key constraint if the default is created using the Oracle, in the light of the data was deleted, it will not be deleted, this point in the Oracle9i giv

  • oracle and to use on delete cascade on delete set null to create a foreign key 2011-04-06

    We introduced the surface creating a foreign key constraint by default if you use the oracle was created, delete the referenced data will not be deleted, this has given us in oracle9i more flexible options, but we use on delete cascade on delete set

  • Oracle, the difference between truncate and delete 2011-07-07

    Delete data in the table methods are delete, truncate, They are deleted from the table of data, table structure can not be deleted, delete the data you can delete the entire table can also delete a table or a bar to meet the conditions of data N, and

  • When ubuntu's boot space command to delete the old version 2011-03-04

    When ubuntu's boot space command to delete the old version sudo aptitude purge ~ ilinux-image-.* \ (\! `uname-r` \)

  • Detailed Windows delete command DEL 2011-06-28

    Original: http://seosin.blog.163.com/blog/static/116707084200942041153747/ Delete one or more files. DEL [/ P] [/ F] [/ S] [/ Q] [/ A [[:] attributes]] names ERASE [/ P] [/ F] [/ S] [/ Q] [/ A [[:] attributes]] names names designate one or more files

  • Construction of subqueries which the oracle table, merge operations, create, modify, delete constraints, create the trigger of the review exercise (a) 2011-04-08

    /** Construction of subqueries which the oracle table ,merge Operations, create , Modify, delete constraints , Use triggers to create the review exercise **/ /** Familiar example is the role of the oracle which trigger to use : There are two tables p

  • After reinstalling the system, oracle database, fast data recovery 2010-04-25

    1. Preparation of the old ORACLE COPY all the files backed up, delete the old directory, then reinstall ORACLE, Directory and the old directory the same (if not the same, to be corrected or more). Only to install ORACLE, do not create the database. T

  • oracle accidentally deleted data recovery Xiangjie table 2010-06-29

    1, undo_retention parameter query, modify: With the show parameter undo; command to see the time to set database parameters undo_retention. Displays the following: undo_management string AUTO undo_retention integer 10800 undo_suppress_errors boolean

  • oracle database link. database data recovery and Import / Export 2011-05-05

    1, database link create public database link remote_02 connect to scott identified by "tiger" using 'localhost: 1521/orcl'; create or replace public synonym syn_emp1 for emp @ remote_02; select * from syn_emp1; 2, data recovery copy a table to t

  • Oracle SQL syntax and a description of data objects 2010-08-19

    The following article introduces the Oracle SQL syntax and a description of data objects, including data control statements (DML) part, and data definition (DDL) part, and other related content specific description, the following detailed description

  • oracle truncate table in the data recovery after 2010-08-29

    A few days ago at work accidentally truncate a table, and the data in the table is very important data. And the table data and no backup, there is no backup is the latest of a sudden I do not know what to do. Internet is a lot of information, but do

  • Oracle in the spool command Comparison of two methods to achieve 2011-02-13

    Oracle in the spool command Comparison of two methods to achieve http://database.51cto.com 2009-04-21 11:23 Anonymous 51CTO Forum I Comments (0) Abstract: The Central Standing Committee met in the production need to be relatively large number of tabl

  • oracle data recovery 2010-04-26

    Today, accidentally changed the database table records. So use the following statement insert into TableName select * from TableName as of timestamp (SYSTIMESTAMP - INTERVAL '1 'hour); Incidentally, the method also to write up online [Method 1]: Flas

  • oracle high version of the database, the data lead to lower version database 2010-08-17

    oracle high version of the database, the data lead to lower version database When the data is usually in the lead, if need be oracle10g export dmp file into oracle9i, the Council reported that version does not support error Solution: Low version of t

  • How to delete how to delete posts delete posts Baidu how to delete posts Baidu posts QQ405447867 2011-10-09

    [How to delete how to delete posts Baidu posts on how to delete posts delete Baidu posts QQ405447867] Delete delete posts Professional Real Estate Company QQ405447867 post; delete Real Estate blog; remove Mop post; 19th floor to delete posts; delete

  • After the online log file corrupted data recovery 2010-11-09

    After the online log file corrupted data recovery 1, non-current online log file after the restore damaged or missing SQL> select * from v $ log; GROUP # THREAD # SEQUENCE # BYTES MEMBERS ARC STATUS FIRST_CHANGE # FIRST_TIME ---------- ---------- ---