Oracle Recovery Table Delete Data recovery package (using flashback)

    好久没写东西了，今天写一篇凑个数吧，来公司一年多了，感觉自己到了一个小瓶颈期了。 以前每天很多新东西，都是忙着学，感觉没时间写博客总结一下，大部分都是写笔记，现在又是没东西可以写，每天干着95%都是重复的工作，大部分时间在运维，但我内心是把自己当做dba的，毕竟当初老大把我从java开发拉倒系统组^_^    上次一个技术把表中的数据删除，这次是另一个技术把正确的包给覆盖了，我给恢复了，哈哈哈---有用的话看一下

Reference Blog: 6761458
虽然包恢复原理一样，但是操作差异一点，因为我的数据是12.2c的

Body 1: Table Data Deletion recovery

--1、查询删除数据时间点之前的数据selectfromasoftimestampto_timestamp(‘2016-08-11 16:12:11‘,‘yyyy-mm-dd hh24:mi:ss‘--（若没有数据 ，将时间继续提前）--2、启用行移动功能altertableenablerowmovement;--3、恢复数据（激动人心的时刻）flashbacktabletotimestampto_timestamp(‘2016-08-11 16:12:11‘,‘yyyy-mm-dd hh24:mi:ss‘);大功告成，数据恢复成功；

Body 2: Package is overwritten wrong, want to flash back

注意这里需要使用dba权限，否则查看不到

--Error package: xxx_pkg correct time: 12 O'Clock noonSELECTobject_id fromAll_objectsWHEREobject_name =' xxx_pkg ';--If the package is deleted directly, not overwritten, it can only be flashed back to the Query object table SELECTobject_id fromAll_objects as  of TIMESTAMP To_timestamp(' 2018-06-26 12:00:00 ',' Yyyy-mm-dd HH24:MI:SS ')WHEREobject_name =' xxx_pkg ';--Find 2 object IDs, one header, one package body9316394602--Query header and package contents according to object IDSELECT Source   fromsource$ as  of TIMESTAMP To_timestamp(' 2018-06-26 12:00:00 ',' Yyyy-mm-dd HH24:MI:SS ')whereobj# =93163;SELECT Source   fromsource$ as  of TIMESTAMP To_timestamp(' 2018-06-26 12:00:00 ',' Yyyy-mm-dd HH24:MI:SS ')whereobj# =94602;--then make sure it's OK, and overwrite it with the SQL content you've found.

注意：一般package body的内容比较多，怎么复制下来呢？spool  /home/oracle/xxx_pkg.sqlSELECT source  FROM source$ AS OF TIMESTAMP TO_TIMESTAMP(‘2018-06-26 12:00:00‘, ‘YYYY-MM-DD HH24:MI:SS‘) where obj# = 93163;spool offexitll /home/oracle/

Knowledge points

To summarize, here is the use of Oracle's flashback query function, very simple, it is understood at a glance, but there are many types of Oracle flashback, including the deletion of tables and databases, flashback transactions and so on.
The most important thing is to understand
Sql> Show parameter undo_retention;

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
Undo_retention Integer 900
This is the default of 900, where undo retains 900s, the back of the undo disk is not enough to be recycled, so the flashback may fail, of course, can also be forced to retain longer, more detailed explanation of Baidu, the following is my flash back for the notes of a little bit simple record:/http 8840743b.wiz03.com/share/s/28g7gx2-0a_u21n5531o6xtr1je9nv1sfqc52nk8dx1zamut

Thank you for reading, interested in the exchange of messages

Oracle Recovery Table Delete Data recovery package (using flashback)