Oracle procedure,package,function，triger 的Flashback Query

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

            Oracle Flashback 技术 总结

            http://blog.csdn.net/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_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

as

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

as

  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

as

  v_date date;

begin

   select  sysdate into v_date from dual;

   return v_date;

end;

 

7 rows selected.

 

 

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

 

 

 

 

 

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