Oracle procedure,package,function，triger 的Flashback Query

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

Oracle Flashback技术总结

http://blog.youkuaiyun.com/xujinyang/article/details/6830438

在这篇文章里面，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_SOURCEdescribes the text source of all stored objects in the database.

USER_SOURCEdescribes 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

NameNull?Type

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

OWNERVARCHAR2(30)

NAMEVARCHAR2(30)

TYPEVARCHAR2(12)

LINENUMBER

TEXTVARCHAR2(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

as

v_date date;

begin

selectsysdate 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

as

v_date date;

begin

selectsysdate 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

as

v_date date;

begin

selectsysdate into v_date from dual;

return v_date;

end;

7 rows selected.

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

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