Oracle procedure,package,function，triger 的Flashback Query-优快云博客

之前对Flashback 进行了一个总结，参考：

Oracle Flashback 技术总结

http://blog.youkuaiyun.com/tianlesoftware/archive/2009/10/15/4677378.aspx

在这篇文章里面，Flashback Query 示例中只提到了对Table的Flashback Query。

如果是其他的对象，比如function，procedure，trigger等。这时候，就需要使用到ALL_SOURCE 表。

先看联机文档对该表的说明：

ALL_SOURCE describes the text source of the stored objects accessible to the current user.

Related Views

DBA_SOURCE describes the text source of all stored objects in the database.

USER_SOURCE describes the text source of the stored objects owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the object
NAME	VARCHAR2(30)	NOT NULL	Name of the object
TYPE	VARCHAR2(12)		Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY
LINE	NUMBER	NOT NULL	Line number of this line of source
TEXT	VARCHAR2(4000)		Text source of the stored object

如果我们误删除了某些对象，如procedure，就可以使用all_source 表进行恢复。

SQL> desc dba_source

Name Null? Type

----------------------------------------- -------- ----------------------------

OWNER VARCHAR2(30)

NAME VARCHAR2(30)

TYPE VARCHAR2(12)

LINE NUMBER

TEXT VARCHAR2(4000)

查看dba_source 的所有type

SQL> select type from dba_source group by type;

TYPE

------------

PROCEDURE

PACKAGE

PACKAGE BODY

TYPE BODY

TRIGGER

FUNCTION

TYPE

7 rows selected.

基于timestamp恢复的语句

SQL>SELECT text

FROM dba_source

AS OF TIMESTAMP TO_TIMESTAMP ('XXXXX', 'YYYY-MM-DD HH24:MI:SS')

WHERE owner = 'XXXX' AND name = '你删除的对象名'

ORDER BY line;

示例：

创建函数：

SQL> CREATE OR REPLACE function getdate return date

v_date date;

begin

select sysdate into v_date from dual;

return v_date;

end;

Function created.

查询函数：

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select getdate() from dual;

GETDATE()

-------------------

2011-04-07 21:02:09

查询dba_source 表：

SQL> select text from dba_source where name='GETDATE' order by line;

TEXT

--------------------------------------------------------------------------------

function getdate return date

v_date date;

begin

select sysdate into v_date from dual;

return v_date;

end;

7 rows selected.

drop 函数，在查询，记录不存在

SQL> drop function getdate;

Function dropped.

SQL> select text from dba_source where name='GETDATE' order by line;

no rows selected

使用我们的Flashback Query 查询：

SQL> select text from dba_source as of timestamp to_timestamp('2011-04-07 21:02:09','yyyy-mm-dd hh24:mi:ss') where name='GETDATE' order by line;

TEXT

--------------------------------------------------------------------------------

function getdate return date

v_date date;

begin

select sysdate into v_date from dual;

return v_date;

end;

7 rows selected.

这时候，又查看到了函数的代码，只需要把这些代码重新执行一下就ok了。其他对象和这个类似。这里就不演示了。

-------------------------------------------------------------------------------------------------------

Blog： http://blog.youkuaiyun.com/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)

DBA 超级群：63306533(满); DBA4 群： 83829929 DBA5群： 142216823

聊天群：40132017 聊天2群：69087192